Pages

Sunday, December 30, 2012

Risk Matrix Chart in SSRS

Of recent, I have been getting mails from my readers whether I have forsaken Reporting Services for Analysis Services in my blog. I want to reassure all of you that this is not the case and that I will be equally focussing on both the technologies. Just that there is a lot of development happening in the AS side and it is very important for all of us to keep on expanding our skillsets and building our expertise. That said, I am writing this post on how to create a Risk Matrix chart in SSRS for all of you guys as a new year gift from my side.

Risk Matrix chart in SSRS

A risk matrix chart, as the name suggests, is used for performing risk analysis. Typically, we combine the Likelihood and Impact ratings of an event to arrive upon a risk score, which aids in deciding on what action to take in view of the overall risk. For the purpose of this post, I have just got 3 levels for the Impact and Likelihood (and this can be increased/decreased as per your requirement). For both Likelihood and Impact, a rating of 1 means Low, 2 means Medium and 3 means High. Now I have categorized the risk score into Low, Medium, High & Critical and would like to plot my events as a scatter chart against this. For that, follow the steps below:-

1) Create an image with the required risk scores in excel / PowerPoint / Paint. I came up with the following image.

background image for SSRS Chart

This will server as the background image of our scatter chart. Add this image to the report.

Add image to report

2) Create a report with the required data sources and dataset. In this case, I have just made a sample dataset

SELECT     'Project A' AS Project, 0.5 AS Likelihood, 0.8 AS Impact
UNION ALL
SELECT     'Project B' AS Project, 0.8 AS Likelihood, 1.3 AS Impact
UNION ALL
SELECT     'Project C' AS Project, 0.9 AS Likelihood, 2.5 AS Impact
UNION ALL
SELECT     'Project D' AS Project, 1.4 AS Likelihood, 0.9 AS Impact
UNION ALL
SELECT     'Project E' AS Project, 1.5 AS Likelihood, 1.5 AS Impact
UNION ALL
SELECT     'Project F' AS Project, 1.2 AS Likelihood, 2.9 AS Impact
UNION ALL
SELECT     'Project G' AS Project, 2.1 AS Likelihood, 0.2 AS Impact
UNION ALL
SELECT     'Project H' AS Project, 2.4 AS Likelihood, 1.4 AS Impact
UNION ALL
SELECT     'Project I' AS Project, 2.7 AS Likelihood, 2.9 AS Impact
UNION ALL
SELECT     'Project J' AS Project, 1.6 AS Likelihood, 1.2 AS Impact
UNION ALL
SELECT     'Project K' AS Project, 2.2 AS Likelihood, 2.2 AS Impact
UNION ALL
SELECT     'Project L' AS Project, 1.1 AS Likelihood, 0.7 AS Impact

3) Go to the toolbox and drag and drop a chart item into the report body. Select the chart type as Scatter chart and click on OK.

scatter chart in ssrs 

4) Drag and drop Likelihood from the dataset fields list into the Values and then select Impact as the X Value in the chart. Also drop Project field into the Category Group of the chart.

Add x and y values as well as category of scatter chart 

5) Change the Axis titles to Likelihood and Impact for the Y and X axis respectively. Then go to both of the axis properties, and set the minimum as 0, maximum as 3 and Interval as 1.

SNAGHTML108f3df0

6) Now click on the chart area and add the RiskMatrix image as the background image.

Add background image

You might also want to hide the major gridlines for the horizontal and vertical axis.

remove major gridlines

7) Now with a bit of cosmetic changes to your marker colours, we arrive upon the end result. It is a good practice to enable the tooltips on the points, so that we can just hover our mouse to find out the project names.

Risk matrix chart

The beauty of this approach is that it can be extended to a lot of other scenarios. For eg, I remember using this approach to answer a scatter chart with four quadrants question in the MSDN forum.

scatter chart with 4 quadrants

Ok, this is all from me for this year. As for my new year wish, I would like to quote Ann Landers-

“Let this coming year be better than all the others. Vow to do some of the things you've always wanted to do but couldn't find the time. Call up a forgotten friend. Drop an old grudge, and replace it with some pleasant memories. Vow not to make a promise you don't think you can keep. Walk tall, and smile more. You'll look ten years younger. Don't be afraid to say, 'I love you'. Say it again. They are the sweetest words in the world.”

Wishing all of you a very prosperous new year! Smile

Monday, December 24, 2012

Introduction to Excel GeoFlow (Beta 1)

I have been playing around for a couple of weeks with the beta release of Excel GeoFlow and I  have got very mixed feelings about the product. No, I am not trying to be the Grinch in this Christmas season and I must say that I sure do like the direction in which Microsoft and this product is going – just that it is not even half as ready as I would have expected (but to be fair to them, it is just the beta 1 release). That said, there are a couple of features that I am really excited about and I thought of jotting down a quick introduction to GeoFlow for those who are interested.

image

1) How to get the beta release of GeoFlow
If you would like to try GeoFlow, send a mail to
geoflowbetarequest@microsoft.com and wait for the team to respond. You must have Excel 2013 for GeoFlow to work.

2) Current Limitations
- Related tables are not supported in this release.All the data should be in one table.
- Backend data changes are not reflected in the visualization in this release and this will mean deleting and recreating the visualizations.
- No Undo/Redo is supported in this release.
- No way to slice and dice data within the visualization
- No drill down functionalities from a higher level (say, states) to a lower level (say, counties) which is there in tools like Power View.

3) Create a basic GeoFlow report
GeoFlow requires your data to have some geographical fields in it. It can be in a variety of formats including latitude and longitude, street address, city, zipcode, state, country, etc. Be aware that you will need internet connection for the geocoding to take place. For the purpose of this demo, I have got the population and personal income data by year of all the counties in the United States. Follow the steps below to create a basic GeoFlow report:-

a) After installing GeoFlow, you should now be able to see the 3D Map button in the Insert tab. Click on any of the cell within the data table and then select the 3D Map button within the Insert tab. Click on the Explore in 3D option as shown in the image below.

1 Insert 3D Map button

b) Choose the geographical field based on which you need the map. In my case, it is the StateName field. Click on Map It button to proceed.

2 Choose geo field

c) Click on the Population field to see the states visualized by this measure.

3 Basic map of population by states

By default, you have got the Column chart visualization (which can be clustered or stacked if a category is present). This can be changed to a bubble map or a heat map visualization also.

4 Bubble map

4) Navigating the 3D Map
Navigating the 3D map can be tricky so it is useful to know all the navigation features. To use a mouse to navigate in the GeoFlow 3D environment, do any of the following:
·         Double click rapidly on any portion in the globe to zoom closer to it.
·         Use the scroll wheel on your mouse to zoom in and zoom out.
·         Click and drag the globe in any direction to pan without changing the pitch.
·         Hold the Alt key, and then click and drag to change the pitch.
·         If you lose your point of focus, zoom all the way out with the scroll wheel to reset the globe and view.

To use the Keyboard to navigate in the GeoFlow 3-D environment, do any of the following:
·         Tap the arrow keys up, down, left and right to pan and spin the globe.
·         Hold the Alt key and then tap the left and right arrow keys to orbit the current target, or the up and down arrow keys to increase and decrease the camera pitch.
·         Tap the plus (+) and minus (-) keys to zoom in and out.

5) Interesting Features
There are quite some interesting features in GeoFlow and these are some of the main ones in my opinion:-

a) Handles multiple geographical levels:- We can add multiple geographical levels to GeoFlow. To add the counties (AreaName field) to our basic report, click on the Edit button in the Layer Manager as shown below.

SNAGHTML5b930dd

Then select the AreaName field and map it to the County in Geography section. Then click on Map It button.

SNAGHTML5bba4d4

Now you should see the map visualized by counties. It might take some time for all the data to be processed on the map and it depends on the number of rows that you have. When the processing is done, you should see the message as Finished in the green bar below the map.

SNAGHTML5bdb42f

You can easily toggle back to the states by selecting the StateName radio button in the Map by section. However, as I mentioned before, drill downs are not possible (as far as I know in this release). Vote to up this request in Connect by clicking here.

b) Visualizing Data over time:- We can visualize the data over time if we have a date  or time field. In my dataset, I have the year but this is not enough as GeoFlow requires a date or time field. So I had to make a new filed called DateYr which converts the year to a date field by adding 1/1/ before every year. Vote here to up this request to have the time play axis use any level of the time dimension. Now in GeoFlow, drag and drop the DateYr field to the Time section. Now you will get an additional section for time settings which has values like Time Accumulation, Instant and Persist the last.
SNAGHTML5cafdfb

You can also see the time play axis on the bottom. Click on play and you can see the data changing over time.

c) Adding Annotations and Textboxes:- This is definitely one of my favourite features. We can now add an annotation to a particular data-point by right clicking on it and then selecting the Add annotation option.

image

Similarly, you can add a textbox also. The only difference between them is that an annotation is bound to a data point while the textbox is more like a sticky note on the screen. The textbox stays at the same position even when the map is panned or zoomed.

d) Find a Location:- The find a location feature is pretty handy in case you have a lot of data points and want to directly zoom to a particular location.

SNAGHTML5ddebe2

e) Creating and Saving Tours:- I have obviously saved the most exciting feature for the last. Now you can create tours by clicking on the New Tour button and then including the scenes in the tour by selecting the Capture Scene button.

SNAGHTML600fded

Scenes will transition automatically and effects are automatically applied. A scene is auto-saved when you capture a new scene. If you want to change a scene, for example if you want the scene to show a different angle or a different time subset, select the scene, make the changes and then click on Save Scene, Once you have created a tour, you can play it by selecting the Play Tour button. I have posted a video of a sample tour that I created.

Excel GeoFlow

After creating a tour in GeoFlow, the tour is automatically saved back to your workbook. You can create multiple tours by clicking on New Tour. Saving your Excel workbook will save each of the GeoFlow tours you created with the worksheet it was created from. Now you will be able to see a new option to Manage Tours also.

SNAGHTML6063add

To summarize, there are some exciting features but clearly, a lot of work needs to be done before it can be used by data professionals for analysis. With this, we come to the end of today’s post. Wishing all of you a very Merry Christmas from my side and be good!

Sunday, December 2, 2012

Querying Attributes and Measures in DAX Multidimensional

The past week has been pretty exciting for Analysis Services geeks - Microsoft SQL Server 2012 With Power View For Multidimensional Models Customer Technology Preview (CTP) has been made available for download (and if you haven’t tried it out yet, download it from this link). Now the obvious part is that Power View can consume data from SSAS Multidimensional objects now and that was a long time request from the community. This will enable the business to use their existing investments in SSAS Multidimensional to utilize the latest end user tools like Power View. The best part is that all this is achieved through native support for DAX in Multidimensional and there is no translation of DAX into MDX. Now this opens up a whole new door of possibilities. For eg, we should be able to design SSRS reports using DAX queries on top of SSAS Multidimensional objects (and who knows, we might be able to replace some of the slower MDX calculations with faster DAX equivalents). Welcome to the world of DAX Multidimensional (or DAXMD)!

Querying in DAXMD

Now the purpose of this post is to introduce you on how to query Multidimensional objects in DAX, and you should be able to get a good overview on how the multidimensional objects are mapped in tabular from here. But there is something extra in this blog that the official documentation has not mentioned when it comes to querying attributes which don't have the same name and key values (for SSAS beginners, an attribute in SSAS MD can have different values for it’s key and name, while in SSAS Tabular, you have to specify the key and name as different columns). For the purpose of this post, I am using the Adventure Works DW 2008R2 database and querying using SQL Server Data Tools (SSDT).

Before we start, lets have a look at the summary

Object mapping summary - MD to Tabular

Armed with this knowledge, let us start querying in DAXMD

1) Querying Attributes with same Key and Name

Let us take the example of Calendar Quarter of Year in the Date dimension

Calendar Quarter of Year

We can write a simple DAX query as shown below

evaluate
values ('Date'[Calendar Quarter of Year])

Querying Calendar Quarter of Year

Note that Date is a role playing dimension in the cube and you will need to use the name of the cube dimension as the table name (and not the database dimension name).

2) Querying Attributes with different Key and Name

Let us take the example of Calendar Year.

Querying Calendar Year

Now let us see what the query below will result in.

evaluate
values ('Date'[Calendar Year])

We will get an error - Column [Calendar Year] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

Composite Key Error in Dax

I spent hours trying to figure out what this error means and finally managed to figured out that it happens only when the key and names are different. It made sense also as DAXMD will only recognize the single attribute as two different columns and not as one. But I had no idea of what syntax too use. Enter Jeffrey Wang (blog) from the Anaysis Services Dev team -

This is a design decision for good performance. A single MDX attribute can produce several DAX columns, some visible, some not, but you can query them in SSMS regardless whether they are visible or not. In general, a MDX attribute, named A, can produce several columns like A.Key0, A.Key1, A. The design requires you to group by at least A.Key0 and A.Key1 if you want to group by any of the three columns. So to query attribute A in DAX, you can do either Summarize('Table', [A.Key0], [A.Key1]) or Summarize('Table', [A.Key0], [A.Key1], [A]). In simple cases where an attribute, named B, only generates a single DAX column, which will also be named B, you can simply query Values([B]) like regular tabular columns.
This constraint helps us achieve good performance since we can send all queries to the MDX engine which only groups by the entire attribute regardless how many DSV columns were used to create this attribute. Otherwise we would have to eliminate duplicate values on the DAX side after MDX query returns the resultset. Hope this makes sense.

What this means is that your query will have to follow the below format

evaluate
summarize('Date', 'Date'[Calendar Year.Key0], 'Date'[Calendar Year])

Querying Calendar Year the right way

Adding the .Key format to the column name is a new addition to the DAX language as far as I know. I am reasonably sure that this would be how Power View also issues the DAX when such attributes are used in the model, though I haven’t tested it so far. If anyone of you have seen the profiler traces of Power View, feel free to comment below and add.

3) Querying Attributes with Multiple Keys

Let us take the example of Calendar Quarter.

Calendar Quarter
As mentioned previously, we will have to group by all the keys at least and then the name, if needed.

evaluate
summarize('Date',
          'Date'[Calendar Quarter.Key0], 
          'Date'[Calendar Quarter.Key1],
          'Date'[Calendar Quarter])

Querying Calendar Quarter

4) Querying Measures in a Measure Group

Let us take the example of Internet Sales Amount in the Internet Sales measure group.

Internet Sales Amount

We can just write a simple DAX query to display the Internet Sales measure group by Calendar Quarter of Year as shown below

evaluate
summarize('Date',
                   'Date'[Calendar Quarter of Year],
                   "test", 'Internet Sales'[Internet Sales Amount])

Querying Internet Sales Amount

We can also refer to the measure without the table name in the query. Also note that we don’t need to provide any aggregation to the measure, else we might get the following error - Column 'Internet Sales Amount' in table 'Internet Sales' cannot be found or may not be used in this expression. This makes sense also as the engine should use the aggregation that is defined in the multidimensional cube.

5) Querying Measures without a Measure Group

This section refers to those calculated measures that are made in the multidimensional cube which are not associated with a measure group. Since all the calculated measures in Adventure Works cube are associated with some or the other measure group, I made a simple calculated measure called test in the calculated member script of the cube as shown below

Create Member CurrentCube.[Measures].[Test]    As  2;

Now I can refer to this Test measure in a DAX query as shown below

evaluate
summarize('Date',
                  'Date'[Calendar Quarter of Year],
                  "test", 'Measures'[Test])

Querying custom measure

Hopefully, this should get you started thinking in DAXMD now! Smile

Update 3/1/2013

On the same vein, Gerhard Brueckl has blogged on DAXMD and Default Members. Interesting read, check it out!