Power View – Using MDX, SSAS Tabular and Power View to display potential issues based on past trends in data
What I am going to explain below is using the above MDX, SSAS Tabular and Power View to display potential issues based on past trends within our data.
For our example we are going to using the example of SCOM data. Within our example we are looking for systems where the available memory has dropped more than 50% of what it was previously sitting at.
· So for example if a server when from 1024 MB of Available Memory and dropped to 400MB we would want to see this.
· And if our SCOM Alerts only looked for servers with less than 200MB of Available Memory it would not have alerted yet.
This is because it could highlight something going on a server that is not currently an issue but COULD become an issue.
Whilst the final Power View Report might look very easy to understand and simple, it is really valuable and enables the end users to quickly identify issues.
NOTE: With the above information below it has not breached the SCOM threshold, so no alerts have been created. But by the time the SCOM Alter comes through it might be too little too late.
NOTE: You can download, install and configure the SCOM data warehouse and cube from the following location, which I have previously blogged about:
http://gqbi.wordpress.com/2013/11/14/scom-systems-center-operations-manager-cube-and-data-warehouse/
MDX Query – SCOM Cube
The first part is to create and write the MDX Query which we will later use in our Tabular Model in order to get the data we require.
1. As with our example we created our MDX query to get the output we required.
2. The first query that we have below is where we are looking for Servers with a memory issue as explained above
Withmember [Measures].[PreviousWeekAverage] as
Avg((strtomember(“[Date].[full Date].&[“+format(now()-7,“yyyy-MM-dd”)+“]”):strtomember(“[Date].[full Date].&[“+format(now()-1,“yyyy-MM-dd”)+“]”)),[Measures].[SampleValue])
member [Measures].[CurrentDayAverage] as
(strtomember(“[Date].[full Date].&[“+format(now(),“yyyy-MM-dd”)+“]”),[Measures].[SampleValue])
member [Measures].[Diff] as
((([Measures].[CurrentDayAverage]-[Measures].[PreviousWeekAverage])/[Measures].[PreviousWeekAverage])*100)
Selectnonempty {[Measures].[CurrentDayAverage],[Measures].[PreviousWeekAverage],[Measures].[Diff] } onColumns,
nonempty {
Filter(
Filter(
Filter(
([Counter].[Counter Hierarchy].[Counter Name].&[Memory]&[Available MBytes]
,[Managed Entity].[Display Name].children,strtomember(“[Date].[full Date].&[“+format(now()-1,“yyyy-MM-dd”)+“]”))
,[Measures].[CurrentDayAverage]>0)
,[Measures].[Diff] > -100000)
,[Measures].[Diff] < -50)
} onRows
From [SCOM Daily]
a. As you can see from the above query we have create the following:
i. Our Measures to compare the current days average to the past weeks average.
ii. We then have put in 3 filters so that it will only bring back servers where the Available Memory has dropped more than 50%, as well as ensure that we do not get any random rows where the SCOM data is corrupt or out of whack.
b. NOTE: This query will return just the specific Display Names or Server Names.
3. In our next query we are basing it on the query above, but this time we are bringing down all the detailed data, so that this can be put into Power View in order to see the history.
Withmember [Measures].[PreviousWeekAverage] as
Avg((strtomember(“[Date].[full Date].&[“+format(now()-7,“yyyy-MM-dd”)+“]”):strtomember(“[Date].[full Date].&[“+format(now()-1,“yyyy-MM-dd”)+“]”)),[Measures].[SampleValue])
member [Measures].[CurrentDayAverage] as
(strtomember(“[Date].[full Date].&[“+format(now(),“yyyy-MM-dd”)+“]”),[Measures].[SampleValue])
member [Measures].[Diff] as
((([Measures].[CurrentDayAverage]-[Measures].[PreviousWeekAverage])/[Measures].[PreviousWeekAverage])*100)
Selectnonempty { [Measures].[SampleValue]} onColumns,
nonempty {
Filter(
Filter(
Filter(
([Counter].[Counter Hierarchy].[Counter Name].&[Memory]&[Available MBytes]
,[Managed Entity].[Display Name].children
,strtomember(“[Date].[full Date].&[“+format(now()-15,“yyyy-MM-dd”)+“]”):strtomember(“[Date].[full Date].&[“+format(now()-1,“yyyy-MM-dd”)+“]”))
,[Measures].[CurrentDayAverage]>0)
,[Measures].[Diff] > -100000)
,[Measures].[Diff] < -50)
} onRows
From [SCOM Daily]
a. Even though the queries look very similar there are differences.
i. In the actual Select statement we are now only putting in the actual Measure of the Sample Value.
ii. We then have also put in our date range, so that we can get this history for the past 15 days.
4. We will then put these queries into SSAS Tabular in the next steps.
SSAS Tabular
Next we are going to create our Tabular model in SSAS so that we can then use this in order to create our Power View Report.
1. Open SQL Server Data Tools (SSDT) and create a new Analysis Services Tabular Project
a. 
b. We have it the name:
i. SCOM Potential Server Issues
2. Create a new Import Data Source and select the following:
a. 
b. Then configure this to connect to your SSAS Instance.
i. With our example we connected to our SSAS Instance where our SCOM Daily cube is.
c. Next we configured which account for the Impersonation Information.
d. Next is where we will put our first MDX Query from Step 2 above.
i. As you can see below with our SCOM example:
3. We then clicked Finish to import the data.
4. Now in the model designer we renamed our columns so that they would be more descriptive. As Shown with our example below:
a. 
b. We also created a measure called:
i. Percentage Available Mbytes
ii. NOTE: The reason that we use this is so that we can use it in our Power View Report later
c. And we also formatted the Full Date column with the Short Date.
5. Next we are going to use our second query from step 3 above for the detailed information.
a. As with our example this is the actual information from SCOM.
b. We once again renamed the columns and formatted them as per our requirements.
c. 
d. We also created a measure called:
i. Available Mbytes
ii. NOTE: The reason that we use this is so that we can use it in our Power View Report later
e. And we also formatted the Full Date column with the Short Date
6. Now the crucial step here is where we created the relationship between the two queries.
a. NOTE: The reason that we do this is so that later when creating the Power View Report you can then select the server name and it will show you the history on the same chart.
b. In SSDT click on the Diagram View
i. 
c. As with our example we had to create a relationship within our SCOM Data.
d. Then create the relationship between the Display Names in the two queries as shown below
i. 
e. NOTE: This will work because in our Sheet called “SCOM_Available_Memory_Overview” there is only unique Display Names.
7. Now you would deploy your SSAS Tabular model to your Server.
Power View Report
Now what we are going to do is to create our Power View Report based on our SSAS Tabular Project.
1. All the steps below are based on our SCOM Example, which you could incorporate into your solution.
2. Log into your SharePoint Server and create your connection file to your SSAS Tabular database.
3. Then click on your connection file to create your Power View Report.
4. First we configured it from the SCOM_Available_Memory_Overview
a. 
b. Next we configured the overview for the Available Mbytes with the following in the created as a bar chart.
c. 
d. Once the bar chart has been configured we then change the sorting to be the following below:
i. 
e. NOTE: The reason for this is because you want to see the systems that have had the largest drop at the top.
f. Once completed it looked like the following:
5. Next we create the detailed view below using the SCOM_Available_Memory_Detailed
a. 
b. Next we configured this to use the Line Chart
i. 
c. This is what it will look like once completed.
6. Finally the trick that makes this work so well is that if you click on the bar chart at the top it will then only show the relevant line chart with the details below.
a. So for example if you clicked on the FileServer.Domain.com you can see that it has the largest Memory drop as shown below.
b. The thing to noteis that SQLServer.Domain.COM on the first chart looks like there was not a large drop in memory. But if you click on the bar chart for SQLServer.Domain.COM you will see that the detailed view now shows a much more meaningful picture.
i. NOTE: Look at the Orange line in step 5 above.
7. Now Save your Power View Chart.
8. And finally if required create an SSIS Package to refresh your tabular data as required.
[…] RSS ← Power View – Using MDX, SSAS Tabular and Power View to display potential issues based o… […]