Adatis

Adatis BI Blogs

KPI’s in Power View and DAX Query for KPI’s

I was recently approached by a client where we have assisted in implementing a Microsoft Analysis Services tabular solution and a supporting management pack of SSRS reports.  They were asking about utilising some of the KPI’s defined in their tabular cube in the SSRS reports using DAX queries.  I was at a bit of a loss and most web help seemed to suggest it couldn’t be done: Blog comment from 2012 However, with a bit of time to investigate it appears Power View does now support Tabular KPI’s and as such a DAX query should be able to pull them out.  My KPI base measure is named “Highest Ranking” and I am able to view its value, status or goal in Power View.  A little work with every SQL developer’s best friend Profiler and I had what I was looking for.  It appears that when you create a KPI SSAS is defining some measures in the background which do some of the work of the MDX KPI functions. Therefore the following DAX query against my cycling Demo cube contains the expected results and could therefore be used in SSRS reporting. EVALUATE ADDCOLUMNS( VALUES('DimRider'[Name]) , "Highest_Ranking", 'FactRanking'[Highest Ranking], "Highest_Ranking_Goal", 'FactRanking'[_Highest Ranking Goal], "Highest_Ranking_Status", 'FactRanking'[_Highest Ranking Status] ) ORDER BY 'DimRider'[Name]   I also tested for the existence of other KPI functions such as Trend and Weight but these do not appear to be present.  It is also interesting that the use of a KPI over a measure does not change the measure name but just group it in the field list and as such there is no need for a value function. For more info on KPI’s in Power View there is additional documentation here.  I am currently unsure of the required versions for this functionality so if it is missing from your environment I would love to hear from you.

Power BI preview– Mobile Integration

I have had the invite through to view the Power BI preview today (Get yours here).  The first thing I wanted to do was get some reports up and start displaying them through my Surface and its Power BI App. The app comes pre-installed with samples but to link it up with your own site you need to hit browse from the app menu.  From there you can add a location.  What caught me out was where do i navigate to to get my Power BI preview reports? Well it is hidden away in the documentation here that you don’t try and navigate directly to your Power BI site as I did but you directly give the SharePoint site and it will pick up the Power BI app from there.  From there you can navigate to the reports you have uploaded to your preview site and favourite the ones you want to appear on the home screen.  The couple I have on there can be seen here: You can control which sheets get displayed by changing the browser view options of the workbook, as described here More to come on Power BI shortly..

Power View in SQL Server 2012 RTM

Power View has been given a couple of small changes from RC0 to the RTM release: Firstly, you can now choose the colour of the various visualisations via the Themes section, which you’ll find on the new Styles ribbon tab: This will allow you to change the colour for a slicer, table or card, whereas for a chart it will set the palette that gets used for the chart series. Surprisingly it seems to set the theme globally, rather than allowing you to set the colour/palette for a single object: The other change that I’ve noticed is that the Card visualisation now has a Style option on the ribbon: This allows the cards to be displayed without normal coloured background, with a much larger font. So potentially useful for highlighting key numeric facts: I do really like Power View, in particular the clean look & feel, fast performance, export to PowerPoint and animations to name a few. However, even though RC0 added a lot of new features, I think it’s still missing some of the functionality offered by other reporting tools. I know that it’s marketed as a data visualisation tool rather than a reporting tool, but those lines will become a bit blurred for users. This is where Connect comes in… One connect suggestion that I’ve created is to allow drill down in Power View charts. If you’ve used Power View to perhaps spot a trend or spike in the data, then it makes sense for you to manipulate the view to find the root cause, which I think drill down would help with. Other suggestions that I think would be useful are heat maps and allowing Power View to access multidimensional models. Please vote on Connect if you agree!

Power View Default Field Set

Here's another quick tip when creating data models for use with Power View - Default Field Sets can be created in both Tabular SSAS and PowerPivot that give the user a shortcut for automatically adding multiple fields to a report. As an example, here's a screenshot of the Default Field Set in PowerPivot - this will tell Power view that the following fields should be automatically selected when the table is added to a Power View report: I'd seen this in the documentation a while back, but hadn't actually used it, nor could I see a way to select the table. In fact, although there's no checkbox, it's just a simple single click on the actual table name, as I’ve highlighted below: When you click on the table name Product above, you will now get the following fields automatically added to a table visualisation: So not exactly rocket science, but worth doing! Here's how to set up the Default Field set for PowerPivot and Tabular Analysis Services.

PowerPivot Settings for Power View

I’ve been using both PowerPivot and Power View quite a bit recently and, in addition to the post I did a while back, have made a few further observations on getting the two to play nicely together. Building an Example PowerPivot Model For this post I’ve created a very very simple PowerPivot model, based on the Adventure Works Internet Sales Fact table and its related tables. What this means is that after the model is built, I can build a variety of Excel reports that summarise the measures in the FactInternetSales Adventure Works fact table, such as Sales Amount, Tax Amount or Order Quantity. In the example below I’ve built a very basic report showing Order Quantity by year: On to Power View Having already published the workbook to SharePoint, my task now is to create a Power View report, which can be done via the PowerPivot Gallery. If wanted to try and create a visual version of the above report in Power View (E.g. a graph), my steps would be to do first of all drag Calendar Year into the report, then Order Quantity. However, when doing this recently, the result wasn’t quite what I expected: Order Quantity has not been summed at all, which is the opposite behaviour to that of PowerPivot. Consequently I can’t create a graph visualisation, because Power View thinks I don’t have any measures in my report: Yet if we contrast this to measures such as Internet Sales Amount or Tax Amount, they all work fine. Upon looking at the field list for Internet Sales, we will see that Order Quantity is missing the sum symbol, as Power View has not interpreted it as a measure: We can get around this in Power View with a bit of dragging and dropping, but we may as well look why it’s happened in the first place. The reason for this is the way in which the PowerPivot import wizard interprets SQL data types. The OrderQuantity column in the FactInternetSales SQL table has a data type of Smallint, which will not be summarised by default in Power View. Decimal and Money types are fine, hence why Tax Amount and Sales Amount above look ok. Rather than changing the data types, we can force Power View to interpret the column as a measure by going to the PowerPivot advanced tab and clicking on Summarize By. If the Summarize By value is changed from Default to Sum, then Power View will interpret the column as a measure: If we republish the model and then go back to Power View, we will see quite a difference with the same actions as before. First of all the field list is looking correct: Secondly, only dragging Calendar Year and Order Quantity will now give the following correct results: Now that we have a measure, it means that we can now change the table into a visualisation of our choice: Power View is the kind of product that power users are hopefully going to find intuitive, but we can make things even easier for users by making small changes like this.

Working with Images in Power View

Power View includes several ways to create visualisations with images, ranging from a simple table, to the scrollable tile visualisation. In this post I’m going to explain a bit about the tile visualisation, showing how it can be used in Power View, before showing a couple of tips for working with images in both PowerPivot and Analysis Services Tabular models. Power View Tiles The Power View Tiles visualisation allows you to display a scrollable series of images, which, when clicked on, will display data of your choosing. The report below is an example of this: What’s nice about the View above is that the names of the products are automatically displayed underneath the images. This is a sensible place to display the product names, as it means the area below can focus on whatever product level data you want, in this case Revenue by Month for the selected product. This works fine in the sample models (in my case the Tailspin Toys model that I think came with CTP3) but it wont work in vanilla PowerPivot / Tabular models unless you configure a few model settings. Trying to reproduce the report above without these settings will give the following tile visualisation, minus the product names: PowerPivot Advanced Settings To address this in PowerPivot, you first of all need to switch to Advanced Mode, which you can do by selecting this option from the File menu in PowerPivot: This gives you the following advanced tab: Clicking on the highlighted Table Behaviour button in the ribbon gives you the following Table Behaviour window: Here you can “change the default behaviour for different visualisation types and default grouping behaviour in client tools for this table”. The following properties should be set in order to get the tiles working correctly: Row Identifier – Specifies a column that only contains unique values, which allows the column to be used as an internal grouping key in client tools. Default Label – Specifies which column gives a row level display name, e.g. the product name in a Product table. This is key, as when a row level image is used, this property specifies which value to display alongside the image in tiles and other image-based visualisation. Default Image – Specifies which column contains images representing the row level data, e.g. pictures of products. The full details for all these properties can be found here. Tabular Models Tabular Models contain the same properties, which can be edited when you open a tabular project in the new SQL Server Data Tools. The image below shows how the properties have been configured for the column in my model called Image: Back to Power View If we now build a tile visualisation using the image column, we will see that we get the names of the products returned along with the image. It’s also worth noting that the Image and Product Name columns have now have an icon next to them, indicating that the field exhibits “row level” behaviour: Finally, for some examples of what’s new in Power View SQL 2012 RC0 take a look at this post on the Reporting Services Team Blog.