Pages

Wednesday, July 25, 2012

And I am a MCSE in BI!

There has been a lot of things happening in my life recently. For example, yesterday was my first wedding anniversary, I was speaking at SQL Saturday 122 (Louisville, KY) in the weekend, last Monday I finally took my road test and got my driving license in the USA, and tons of other stuff which you would be least interested in. And the week before last, I managed to pass my 70-462 exam, which makes me a Microsoft Certified Solutions Expert (MCSE) in the BI track now; I thought at least some of you who are thinking of going through the certifications might be interested to know about my experience.

MCSE BI SQL 2012

You must have already heard about the new certification structure from Microsoft and there are tons of blogs out there which talk about this change. For the SQL Server 2012 certification, you have the MCSA (similar to MCTS for SQL 2008) and MCSE (similar to MCITP for SQL 2008) now. The MCSE has two tracks – SQL Server and BI and I would be talking only about the BI track here. The MCSA is a pre-requisite for the MCSE, and the way to obtain MCSA is given below

image

After you have got your MCSA, you can follow the path below to get your MCSE in BI

image

As you can see from the above images, you just have to write three exams for the upgrade path to your MCSE BI compared to the five if you are new to certification. Even though I was eligible for it, I decided to go through the longer path as I had got the codes to write the beta exams. I decided to write 4 of the 5 exams required for my MCSE in BI. 3 of them (463, 466, 467) were completely BI related while the other one (461) was more testing the SQL coding skills. As I had already starting working with SQL Server 2012, I was not that worried about the three BI exams. I consider my SQL skills also to be good (even though my TSQL skills are not that great) and since all the exams were free (because they were still in beta), I decided to try it anyway (else it would cost me $150 per exam). The only exam I didn’t write was 462 and the reason is something that will strike with most if not all of the BI guys – I had no clue on how how to administer the SQL Server side. I am more of the guy who would keep the phone numbers of a couple of DBAs in my hotkeys, and call them the moment I hear something on migration strategies, replication, mirroring, security, etc. Anyways, I scheduled the 4 beta exams in 2 weeks (as seats & dates were limited), and managed to pass all four of them. Even though I can’t disclose the exam content, I would give a couple of advices:-

1) Make sure that you read this book – Introducing Microsoft SQL Server 2012. This book is free and is the easiest way to make sure that you at least get acquainted with all the new features of SQL 2012.

2) As long as you have experience and are familiar with the SSIS, SSAS (including multi-dimensional, tabular, MDX, DAX) and SSRS, you just need to do some general reading which is what I did. Else you might need to pick up a few books and start preparing for those topics. I know that the tabular side is something that is completely new and is a challenge for most BI professionals that I know. I would recommend Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model as a great guide to get you to that intermediate to expert level.

3) Make sure that you go to the Skills Measured tab of the exam page and at least read something on each point that is there. The internet has a lot of articles and you can find almost everything if you just search for them.

4) For me, the easiest way to remember things is by doing. If it is the same for you, make sure that you have installed the entire BI suite and do a couple of exercises. If that is not possible for you, try to hit the Virtual Labs.

Once I got to know that I passed for all four, I started preparing for my 70-462. I was really lax initially as I was more of the mentality that I don’t require to know all these in my daily job as a BI consultant. Once I got over that mind block, I started to appreciate the fact that at least now, I will have some basic understanding of what all these terms mean. My advice for this exam is

1) Get the official guide for the exam - Training Kit (Exam 70-462): Administering Microsoft SQL Server 2012 Databases. After taking the exam, I feel that this is a great guide for the exam.

2) Make sure that you read and understand all that is written before you go forward. For example, there are different migration strategies and what you need to know is when to use which one. At the end, I was overwhelmed with all the information that I read and started to forget which one to use when.  It might be a good idea to make small cards for each section, and put the different techniques in a tabular format.

3) Take all the practice tests in the CD accompanying the book. It is a great way to test your readiness for the exam.

4) Incase you are not buying the book, make sure that you go to the Skills Measured tab of the exam page and read as much as you can on each point that is there from the internet.

Hopefully, this information would come in handy for those who are preparing for the exams. Meanwhile, let me start flaunting my new logo and certificate Smile

Jason Thomas - MCSE in BI SQL 2012

Wednesday, July 18, 2012

Creating Maps in Excel 2013 using Power View

There has been a lot of buzz in the BI community since yesterday, and the reason is none other than the public preview of Office 2013 (Excel 2013 in particular). There’s been a lot of articles written on the new features available and the integration of PowerPivot and Power View has really got everyone talking about. In case you still haven’t looked at the new features, here is the link for it. Meanwhile I can’t wait to blog about the spatial capabilities available in Power View, so I am directly jumping to the topic.

Creating Maps in Excel 2013 using Power View

Power View in Excel 2013 has the ability to create maps from your data and uses Bing maps for the same. Unlike the maps in SSRS, here you do have the ability to zoom and pan as needed. Also, Bing maps automatically detects the location and hence you don’t need to provide a shapefile or even the latitude/longitude information. In this blog, I will take you through the steps to create a map report in Power View.

I) Creating a basic map report

1) Open up a new workbook Excel 2013 and then enter the following data in the cells

Country

City

Sales

USA

Charlotte, North Carolina

100

USA

Madison, Wisconsin

50

USA

Jacksonville, Florida

140

USA

Rochester, New York

40

USA

Philadelphia, Pennsylvania

120

2) Select the entire data, go to the Insert tab and click on Power View icon.

select data in excel

You should get a loading screen while it takes a couple of seconds to open Power View

loading power view

3) Now click on the Country field, and then you should be able to see the Map icon appear on the menu above. Click on the Map icon as shown in the image below

Map in Power View

You should get a warning to enable content as the data needs to be sent to Bing to get geocoded. Click on enable content to proceed. Note that you would need an internet connection for implementing this.

enable content in Power View

4) Now you can pretty much rearrange your fields by dragging them into the areas below. I have dragged Sales into the Size, City into Locations as well as Color. So I get a map report below which shows the cities as bubbles with corresponding colors and size as the amount of sales.

Basic map report in Power View

5) You can also play around with other properties like Title, Legend, Data Labels and Map Background. They are present when you click on the Layout tab.

map layout properties

It is interesting to note that the data used for creating the Power View report gets imported into PowerPivot by default. This is because Power View can only communicate through DAX currently, and hence needs a tabular model behind it. The PowerPivot model can be viewed by clicking on the PowerPivot tab and then selecting the manage tab.

powerpivot

Since there is no way to add additional data into this model without deleting and recreating the table, it would be a good practice to create the PowerPivot model first from linked tables, and then using the PowerPivot fields to create the Power View report. This way, we will be able to keep on adding data as long as there is a link between the table and PowerPivot. I will be showing you how to do this in the next part.

II) Creating a drill down in map report

1) Select the same set of data in excel, go to the PowerPivot tab and select the Add to Data Model icon as shown below.

Add to data model

Note that you can still create Power View reports directly, but we will be using this technique for the reasons mentioned above earlier.

2) Select the home tab, then click on Pivot table option and select Power View to create Power View report.

PivotTable-->Power View

It is recommended to set the reporting properties of the Country and City field, so that Power View can recognize them as geographical entities, as shown in the image below.

setting reporting properties

3) Now you should be able to see Power View (in case you don’t, you can click on a blank cell, go to the Insert tab and click on Power View icon). Notice that the Country and City fields have a map icon

map icon besides fields

4) Now click on the Country field, and then you should be able to see the Map icon appear on the menu above. Click on the Map and then drag both the country and city fields within locations. You can also put the Sales measure in the Size area. Now you should have a map report which drills down from the Country level to the city.

Adding drilldown fields to location

If you double click on the blue dot in the center (which is USA), you will get the drill down report by cities. Notice that the title has automatically changed from Sales by Country to Sales by City. You can also click on the up arrow (highlighted in the image below) to return to the parent report.

return to parent report

You can also create hierarchies in your PowerPivot model, and that can be directly added dragged and dropped to the Locations area. This will ensure that you don’t have to drag and drop each field individually and the drill down would be present across the entire hierarchy.

You can also see the benefit of adding the linked table to the PowerPivot model instead of using a range. Now if I have to add data, I can just go to the excel sheet and append the rows that I want as shown below

Adding more data in linked table

Now, all I need to do is to go to my Power View sheet and refresh the report. You can see that the new data is already included in the report

power view report showing new data

III) Creating Pie charts in map report

1) Use the previous report, and then add the city to the Color area. Now you can see that there is a pie chart by cities at a country level.

Add city to color

2) You can hover the mouse on the pie charts, and the pie chart will expand and show the tooltips as shown below

Pie charts in Power View

You can also use the horizontal / vertical multiples location areas to split it by the selected field as shown below

multiples in Power View

Similarly, you can use the Tile By option also (however, this feature is not available if you use the multiples option)

Another best practice is to concatenate your city names with state/country info also (as I have done in all the examples) as there might be duplicate city names and doing this will help Bing in geocoding it better. You can also do this operation as a calculated column in PowerPivot, if you don’t want the city names to be displayed with their country/region info appended.

IV) Creating a map report with latitude and longitude

1) Let’s say we already have fields in the database with longitude and latitude, and we want to use them instead of Bing geocoding for us. For the purpose of this demo, let’s use the data below and paste them into excel

Country

City

latitude

longitude

Australia

Melbourne, Australia

-37.8136

144.9631

Australia

Sydney, Australia

-33.8737

151.2069

Australia

Brisbane, Australia

-27.4709

153.0235

Australia

Perth, Australia

-31.9529

115.8573

Australia

Adelaide, Australia

-34.9287

138.5999

I used the site http://www.findlatitudeandlongitude.com/batch-geocode/ to get the latitude and longitude of the address.

2) Now select the data and use that to insert a Power View report. Click on the City field and then select the Map option. Now the latitude and longitude would be mapped to the corresponding columns if the names are the same (else you can always drag and drop them) and your map report would be ready.

Power View report by latitude and longitude

Hopefully this post has given you a good start to start playing with your own map reports in Power View. There are again a lot of best practices embedded within the post and sorry for not doing a great job of consolidating them, but I am hoping that this will serve as an incentive for you to read this long post completely. And well, if you have not yet downloaded the office preview, do it now from this link.

Thursday, July 5, 2012

Simulating Slicers in SSRS Reports

One of my readers noticed that I had used something resembling excel slicers in my previous post and asked me more on how to implement it in SSRS. For people following the blogosphere, they must already be familiar with this idea as it was introduced almost an year back by Simon Sabin (blog | twitter) in his post as well as some sessions. He used custom code to simulate the slicer features and I thought it would be a good exercise for me to replicate the features (with some slight variations) using just report expressions and actions. Also, this scenario touches on some important SSRS concepts and will serve as a good example on how to play around with multivalued report parameters.

image

Before we go forward, let us look at some functionalities of an excel slicer using the below image as a reference.

Initial State of excel slicer

a) When we click on any of the product category, only the selected product category is shown (and the excel sheet also refreshes for the selected product category) as shown below

Selecting Bikes and then selecting Clothing in slicer

b) When we hold the Ctrl key and click any unselected product category, it is added to the selected list instead of being the only selection (as was the case in above) and the result is shown below

Hold CTRL and then selecting Clothing and then Accessories

c) When we hold the Ctrl key and click any selected product category, it is removed from the selected list and the result is shown below

Hold CTRL and then selecting Clothing

d) When we click on filter icon on the top, it resets to the initial state with all product categories and the filter icon gets greyed out.

Return to initial state when filter icon is clicked

We will not be able to replicate the features in SSRS exactly as there is no way to know whether the CTRL key has been pressed or not. However, we will be implementing a workaround to have the same feature. For this post, I would be using the AdventureWorks cube for building my queries. Follow the steps below to replicate the solution:-

1) Create a report named Slicers and then create a new Analysis Services datasource connection to the AdventureWorks cube.

2) Save the four images (All, BSS, BSU, Reset) below in your local system and then add them to the report.

AllBSSBSUReset

3) Then create two new multivalued report parameter called GeographyCountry & AllGC and use the same dataset named GeographyCountry created with the query below for setting the parameters’ available and default values.

WITH MEMBER [Measures].[ParameterCaption] AS
[Geography].[Country].CurrentMember .MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS
[Geography].[Country].CurrentMember.UniqueName
MEMBER [Measures].[ParameterLevel] AS
[Geography].[Country].CurrentMember .Level .Ordinal
SELECT
{ [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel] } ON COLUMNS,
[Geography].[Country].Children ON ROWS
FROM [Adventure Works]

The purpose of the parameters will be explained later when we are implementing the features.

4) Now create a dataset called DataSet1 with the query below

WITH MEMBER [measures].[rsa] AS
IIf (
    instr ( @GeographyCountry, [Geography].[Country].CurrentMember.UniqueName ) > 0,
    1,
NULL
)
MEMBER [Measures].[ParameterValue] AS
[Geography].[Country].CurrentMember.UniqueName
SELECT NON EMPTY
{ [Measures].[Reseller Sales Amount], [measures].[rsa], [Measures].[ParameterValue] } ON COLUMNS,
NON EMPTY
{
  ( [Geography].[Country].[Country].AllMembers * [Date].[Calendar].[Calendar Year].AllMembers )
} ON ROWS
FROM [Adventure Works]

This query will be used to generate a chart in our report. You might have noticed that the query is not filtered by the GeographyCountry parameter. The reason for it is that we would like to have a “brushing” effect on our chart where the unselected countries are displayed in a grey colour and the selected countries are highlighted (unlike the traditional reports where just the selected values are displayed). The calculated measure “rsa” will be used to determine if the countries are selected or not. Now your report data pane should look like shown below

Report Data Pane

5) Now create a simple chart with Reseller Sales Amount as the Value, Calendar Year as Category Group and Country as Series Group.

SSRS Chart

Click on the Reseller Sales Amount value in the chart data and press F4 to open up the properties. Then enter the following expression for the Color property

=iif(sum(Fields!rsa.Value)>0,"Automatic","WhiteSmoke")

This will give the brushing effect to the chart that we talked about before in step 4.

6) Now let us make a matrix and add the Country field from DataSet1 to the rows. Click on the textbox where the Country field was added and enter the expression below in the Value property under BackgroundImage

=iif(sum(Fields!rsa.Value)=0,"BSU","BSS")

Also make sure to set the Source property as Embedded, MIMEType as image/png and BackgroundRepeat property as Clip

Background Image properties

7) Now right click on the same textbox and select the Textbox Properties. Go to the action tab and select the Go to Report option. Specify the report name as Slicers (which is the same report) and pass the [ParameterValue] field to the GeographyCountry parameter as shown below

Report action for Country textbox

This will give the feature (a) of the excel slicer that we discussed above, where we can click on a particular field and see the report filtered for that selected value.

8) Since we cant implement the CTRL key feature to add and remove values from the selected list, we are going to add one column on the right and left of the Country textbox. Then we can implement actions such that we can add values when we click on the left column (so I will keep a + symbol in that textbox) and remove values when we click on the right column (so I will keep a symbol).

image

9) Right click the textbox having + symbol, select the textbox properties and then go to the action tab. Then select the Go to Report option. Specify the report name as Slicers (which is the same report) and pass the expression below to the GeographyCountry parameter as shown below

=split(join(Parameters!GeographyCountry.Value,",")+","+Fields!ParameterValue.Value,",")

You can see that the expression first uses a join function to make the parameter object as a comma separated string, then concatenates the current selected value (note that the unique name of the country is being passed which is the ParameterValue field) and finally uses the split function to convert the comma separated string to the parameter object. The image is shown below for reference

Adding to a selected list

So clicking on the + symbol will give the feature (b) of the excel slicer that we discussed above, where we can click on a particular field and see it being added to the list of selected values.

10) Right click the textbox having - symbol, select the textbox properties and then go to the action tab. Then select the Go to Report option. Specify the report name as Slicers (which is the same report) and pass the expression below to the GeographyCountry parameter as shown below

=split(
iif(Parameters!GeographyCountry.Value(0)=Fields!ParameterValue.Value,
replace(join(Parameters!GeographyCountry.Value,","),Fields!ParameterValue.Value+",",""),
replace(join(Parameters!GeographyCountry.Value,","),","+Fields!ParameterValue.Value,"")),
",")

Removing from the list is a bit more complex as we have to check first whether the value which needs to be unselected is the first member of the parameter. If yes, then we will have to replace the value and a comma with an empty string, else it has to be a comma followed by a value. The image is shown below for reference

Removing from a selected list

So clicking on the - symbol will give the feature (c) of the excel slicer that we discussed above, where we can click on a particular field and see it being removed from the list of selected values.

11) To get the final reset feature, we can add an image to the top right corner of the tablix, with the expression below

=iif(join(Parameters!GeographyCountry.Value,",")=join(Parameters!AllGC.Value,","),"All","Reset")

Conditionally displaying reset icon

Make sure that the Source property is Embedded and MIMEType is image/png. So clicking this image will give us the feature (d) of the excel slicer. Now we need to set the action for the image. For that, select the Go to Report option and set the expression below for the report

=iif(join(Parameters!GeographyCountry.Value,",")=join(Parameters!AllGC.Value,","),Nothing,"Slicers")

This will ensure that the action is enabled only if all the values are not selected and this is where we use the second parameter AllGC (for comparing whether the Geography Country is equal to AllGC). Also add the value [@AllGC] to be passed to the GeographyCountry parameter.

Setting action for Reset icon

12) Now preview the report and you can see the chart getting changed as per the slicers.

Report with slicers

13) Now, I would like to add a new feature also to the slicer which is the All But The Selected functionality. So when you click on the slicer button value for the first time, only that selected value is displayed. Currently, if you click on the same button again, the report refreshed but there is no change in the report as the same value is being selected. With this new feature that we implement, clicking a selected value will display all the value except the selected one, like shown below

'All But the Selected' feature explanation

For this functionality, copy & paste the expression below instead of [ParameterValue] field in step 7. This expression will check if the value which is clicked is the only value in the selected list. If yes, then it selects all the values except that and if no, then it just displays the report for that selected value.

=iif(join(Parameters!GeographyCountry.Value,",")=Fields!ParameterValue.Value,
split(iif(Parameters!AllGC.Value(0)=Fields!ParameterValue.Value,replace(join(Parameters!AllGC.Value,","),Fields!ParameterValue.Value+",",""),replace(join(Parameters!AllGC.Value,","),","+Fields!ParameterValue.Value,"")),",")
,Fields!ParameterValue.Value)

14) Preview the report and now you can also see this new feature added in your report.

SSRS Report with the new feature

Now there is an obvious disadvantage with slicers in SSRS that the report needs to be refreshed and so you will see the “Loading” screen when the slicers are changed. But this is just a small issue and with fast reports, this might be under a second. I know this is a long post and that there are a lot of report expressions in this post, but if read carefully, you can get to know a lot of important concepts which can be applied to other scenarios. If anyone needs the report file, feel free to click and download it from the link.

Download Slicers.rdl

As for me, I am already thinking on another type of visualization involving these slicers, hope to put it out as a blog soon!

Monday, July 2, 2012

SSRS Chart Issues in SharePoint Integrated Mode

Have you ever faced a problem that you had almost given hopes on, and then pushed yourself to that one last hopeless try which succeeded? Well, it happens a lot with me, I just seem to be very lucky in such scenarios. I have always felt that men try to over-romanticize the idea of discovering something by accident (remember the Nobel laureate Albert Szent-Györgyi saying – “A discovery is said to be an accident meeting a prepared mind”?). I don’t know how prepared I was but it sure was an accident that made this discovery. I had been dabbling with some SSRS reports in the SharePoint Integrated mode and was facing a lot of issues with the inline charts that I was using. This post will talk about the issues that I encountered as well as the solution which I discovered accidentally.

SSRS Chart Issues in SharePoint Integrated Mode

To give an idea of the environment which had the issues, I was using SQL Server 2012 Reporting Services in integrated mode with SharePoint 2010. To recreate the issues, follow the steps below:-

1) Create a simple report with some charts embedded within a table or a matrix. I have created a data bar, bar chart, bullet chart as well as a line chart as an example.

SSRS Report with inline charts

2) Implement some actions as well as tooltips on the charts. As an example, I have implemented an action on the first databar to go to http://www.bing.com and tooltip expressions on all of the 4 charts. You can also implement actions and tooltips on some of the regular tablix columns without the charts. Preview it and make sure the tooltips as well as the chart actions are working.

Preview the SSRS Report

3) Now deploy this report in Sharepoint and preview the report.

Issues with the SSRS Chart when previewed in SharePoint

Even though it is not that evident from the image, there were quite some issues that I faced with this report and I am listing them below:-
     a) The actions that were defined on the frist bar chart was not working for some bars.
     b) The tooltips defined for the second bar chart was not working.
     c) The tooltips defined for the bullet chart was not working.
     d) The tooltips defined for the line chart was not working. In addition to that, the lines appeared to be jaggy and seemed to have some pixels cut off from them. Click and expand the image above to see jagged effect on the line chart.
Also, the report seemed to take more time in displaying the charts in SharePoint than when I had previewed it in the SSDT environment. It is to be noted that the actions as well as the tooltips in the tablix columns without the charts worked fine.

4) Now to solve the issue, all you have to do is to enclose each chart in a rectangle before using it in the cell of the tablix.

How to enclose chart within rectangle in SSRS

5) Once you have done the above step for every chart, save and deploy the report to SharePoint. You can preview the report and see that now the tooltips as well as the actions are working for the charts. Also, the line charts seemed to be smooth and devoid of the jagged line effects seen earlier. Performance also seems to be much better than before.

SSRS Report when previewed in SP

Moral of the story – If you are using inline charts in SSRS SharePoint Integrated mode and facing similar issues, then try to use the charts within a rectangle. This might save you some headaches later.