Some Random Thoughts

Review: OfficeWriter v8

2012-02-21T07:23:00.001-05:00

As a BI consultant specializing in SSRS, I have had lots of frustrations and hard times because of Excel. Every now and then, I have some or other business user coming up to me and asking for some feature which is there in Excel but not in SSRS. If you have been following my blog, you would already know that I am more of a work-around man, trying to find some alternative for features which are not supported out of the box. But when it comes to Excel related features, most of my attempts end in disappointment. So naturally, my ears perked up when I was asked to review a plugin which claimed to build SSRS reports using excel and word.

So I downloaded OfficeWriter v8 and spent close to a week playing around with it. Even though I encountered some minor quirks (v8.0 doesn’t run on the 64 bit version of Office 2010 yet – luckily I had a home pc with a 32 bit version of Office; got some minor issues when editing and deploying an existing SSRS report with shared data sources – got around it by setting the data sources once again from the report manager), overall I have been very pleased and of course, excited at the different prospects that this plugin opens up. You can quickly have a look at one of the reports created with OfficeWriter by clicking on the image below.

Let me note down quickly what I thought of it:-

Advantages
1) XLSX support – Right now, xlsx support is available only for SQL 2012, but with OfficeWriter, you can take advantage of all the features including overcoming the 65,326 row limit (listed here) in your current version of SSRS.
2) Full excel features support including charts and pivot tables – Since excel is fully supported, you can make use of all the charts, macros and other features which are there in Excel but not in SSRS.
3) Ability to have 2 views for a report - If we are editing a report with OfficeWriter from an existing report made through BIDS, then we will have 2 views for it – one which is displayed in report manager and other would be when exported to Excel. It can be helpful in scenarios where you view a fixed report and want to drill down and play around with the data further, in which case you can export to excel and then use a pivot table.
4) Charts are not rendered as images when exported to excel. The export to Excel feature in SSRS will return images rendered as a static image. However, with OfficeWriter, you get the chart exactly as you designed in Excel with all the the interactivity and even tooltips.
5) Can enable subscriptions, security – The tight integration with SSRS ensures that we can use subscriptions and other security features available in SSRS for the OfficeWriter reports.
6) Workbook Protection – You can password protect your workbook or lock your cells for edit, which is an often requested feature by SSRS power users for subscription.

Limitations
1) Can’t build MDX queries in OfficeWriter, will have to make in BIDS and then use them.
2) Will not work on the 64 bit version of Office, as of now.
3) This is not a limitation actually and is by design, but I couldn't help wishing if only the charts and data designed through OfficeWriter in excel would display properly in SSRS also.

Even though OfficeWriter has more features including integration with Word and SharePoint, I have reviewed only the integration with SSRS for Excel part. For folks who are further interested in this, I will show you how to create a sample report using OfficeWriter and post some useful links below it.

Creating / Editing a sample report

For the demo purpose, I am using OfficeWriter v8.0, the 32 bit version of Excel 2010 and SQL Server 2008 R2. Once the installation of OfficeWriter is done, you can find a new toolbar like shown below in the Add-Ins tab of Excel.

Now, there are two ways in which you can proceed from here -
I) Create a new report in Excel with OfficeWriter
II) Edit an existing report created with BIDS (Business Intelligence Development Studio) and deployed to the server

The steps to create a new report is shown below:-

1) Click on Add Query in the OfficeWriter toolbar and enter a name for your query.

2) Add a new Database connection if there is none existing, enter the server information and add the required tables for the SQL query. Once that is done, select the required columns. In this example, I have connected to the AdventureWorks database and used the English Product Name in DimProduct table & Order quantity measure in the FactInternetSales.

3) Even though you can group using the OfficeWriter features, I edited the SQL query directly to group by the English Product Name, as shown below and click on OK. Close the query editor after that.

4) Click on Insert Field and select the English Product Name in cell A1 and Order Quantity in B1.

5) Click on View. This will prompt you to enter the name of your report and save it in your local drive. Click on Save and a new dialog box opens which will prompt you to enter the name of your report server. Click on OK to publish your report to the report server.

6) Once that is done, the report preview would be shown in Excel. You can click on Close Report View to go back to the designer.

7) You can also preview the report from the report manager, which will display a message that the report was made in OfficeWriter and would display correctly only when exported to Excel with OfficeWriter.

Editing an existing report is also simple but you would not be able to edit the queries made through BIDS. Follow the steps below to edit an existing report.

1) Click on Open Report and then click on Retrieve as shown in the image below.

2) Enter the report server url and click on Refresh. The list of existing reports would be displayed below and you can select from them.

3) Insert the fields after selecting the datasets (notice that Add Query and Edit Query is disabled). You can also include charts as shown below.

4) Click on View to save and publish your reports. You can then preview the report in excel.

Useful Links
1) OfficeWriter Home Page
2) OfficeWriter tutorials and Documentation
3) OfficeWriter FAQ
4) OfficeWriter System Requirements
5) OfficeWriter Download Evaluation version

P.S.
This is a paid review. However, I have tried to maintain a totally neutral review, and if there is any point that you disagree with me, I would be pleased to hear about in the comments section.

Set the Slice on your SSAS Cube Partitions now!

2012-01-31T15:02:00.001-05:00

I am pretty sure that most of you would have heard or read about this at least once in your SSAS career, but how many of you actually do set slices for your partitions? Well, I am also guilty of the charge and seldom did set slices till a recent issue.

Situation : Duh, that’s strange!
It’s a sunny day in Charlotte and I was just playing around with SQL Server Profiler for learning some performance tuning stuff. I executed this simple MDX query on the sample Adventure Works DB for some purpose which is beyond the scope of this post -

select {[Measures].[Internet Sales Amount]} on 0,
{[Ship Date].[Calendar].[Month].&[2008]&[4]} on 1
from [Adventure Works]

Now while I was going through the Profiler trace, I noticed something strange – three partitions (2005, 2006 and 2008) were being read while I expected just one partition (for 2008) to be read as my query just had April 2008 on the rows.

Now, for people who are not familiar with Adventure Works cube, there are four partitions for the Internet Sales measure group, one for each year from 2005 to 2008 as shown below -

I decided to take a detour from my actual purpose to investigate this issue.

Debugging : Need to dig up that grave!
I was aware that SSAS partitions generate automatic data slicers for each partition and these information are present in the info.*.xml file of the partition data file (you can find them usually at C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data folder followed by the cube, measure group and partition name if you haven’t changed the default settings. Note that I am using SQL Server 2008 R2). I decided to have a look at these three partition data files.

As seen above, there was clearly a difference between the partitions of 2005 / 2006 and 2008 (which are having the names Internet_Sales_2001.0.prt / Internet_Sales_2002.0.prt and Internet_Sales_2004.0.prt. These names are the partition IDs and can be seen in the ID property of the partitions)

It seemed like the auto-slices were not getting created for the 2005 and 2006 partition while it was being done for the 2008 partition. This was further confirmed on opening and inspecting the info.*.xml file with notepad.

Knee-jerk Solution : Or is it?
I went into research mode on Google and soon came up with a property in the msmdsrv.ini file which limits the creation of auto-slicers based on the number of rows in the partition – the IndexBuildThreshold property.

By default, the value is 4096 rows and both the partitions for 2005 and 2006 had less than 4000 rows. This explained why the auto-slicers were not getting created and I modified it to 1000. Now on reprocessing the measure group, I was able to see that the auto-slicers were generated in the partition data files and only the correct partition was being read in the profiler trace.

Actual Solution : The real deal!
I decided to gloat over my findings to my friend and colleague, Javier Guillen (blog | twitter) who was sitting in the next room. When I described the problem statement to him, the very first question he asked me was whether I had set the partition slices, and I was like – sheesh, I missed on that cardinal rule, as usual. I was under the impression that as long as your partition is for a single data member (as in this case – a single year), it should work. But later I found out that partitions for which autoslices are not generated will be scanned anyway, unless the partition slices are explicitly defined. I went back and set the slices for each partition correctly to their respective years and also reverted the IndexBuildThreshold property to the original value of 4096.

Once this was done, I processed my measure group and found out that only the correct partitions were being read from the trace files.

Reading Materials
I took some time to read and learn more about partition slices, thought you would be interested in it too if you made it this far.

1) Get most out of partition slices - Mosha Pasumansky
2) SSAS Partition Slicing - Eric Jacobsen
3) Partition Slice Impact on Query Performance - Jesse Orosz

Using TraceEvent for MDX Debugging

2012-01-22T22:52:00.001-05:00

Do you know how it feels like to hear an old song and have a whole lot of memories come flooding back to you? Well, something sort of similar happened to me today. I was doing my weekly reading and going through a brilliant post by Microsoft MVP - Greg Galloway (blog). That was when I noticed that he had released a new functionality to the Analysis Services Stored Procedures (ASSP) Project on Codeplex. I decided to test this new functionality and was thinking of a scenario where I could use it, when I remembered about an old post in the Analysis Services forum that I intended to blog about but never did.

Now the new addition to the ASSP toolkit is the TraceEvent class. Quoting from the ASSP project page -

“The function in the TraceEvent class is FireTraceEventAndReturnValue. It can be used to detect when Analysis Services is evaluating an expression since the function merely fires a trace event that appears in Profiler as a User Defined event, then returns the integer value you pass in. This function can be used in a scenario such as determining when the cell security expression is evaluated.

Here is an example MDX query displaying the usage of this function:

with member [Measures].[test] as ASSP.FireTraceEventAndReturnValue(99)
select [Measures].[test] on 0
from [Adventure Works]

The signature of the function is:
FireTraceEventAndReturnValue(int)”

Having a base in programming, it was natural for me to expect a sort of “watch window” when I started learning MDX. I wanted to view the values and the change in them while the query was being debugged, but sadly there was none. The TraceEvent class will help alleviate that pain to some extent and I will be showing it in the next few minutes. For the purpose of demonstration, I would be taking the two queries from the AS forum post I mentioned before -

I) PROBLEM

Query 1

withmember [Product].[Product Categories].[Agg] as Aggregate(TopCount([Product].[Product Categories].[Product].members, 3, [Measures].[Internet Sales Amount]))select {[Measures].[Internet Sales Amount]} on 0,[Date].[Calendar].[Month].members on 1from [Adventure Works]where ([Product].[Product Categories].[Agg])

Query 2

select {[Measures].[Internet Sales Amount]} on 0,[Date].[Calendar].[Month].members on 1from [Adventure Works]where (TopCount([Product].[Product Categories].[Product].members, 3, [Measures].[Internet Sales Amount]))

Results

For a MDX beginner, you would expect the results to be same for both queries, as you are just moving the formula of the calculated member in Query 1 to the WHERE clause in Query 2. The reason for the difference is because calculations overwrite current coordinates while axes don't overwrite the current context. So when the first query is being executed, the current coordinates are overwritten and executed in the context of the query axis which means it will check the top 3 products for each of the month and display the result. However, in the second case, you don't have any calculations defined and hence the current coordinates would be the one in the WHERE clause, which is the Top count of products across all months. (You would get a better idea by reading the original post in the forum)

Enough of theory and let us see whether we can debug and understand the working of the mdx queries using the TraceEvent class. For the purpose of seeing the results, we will modify the queries as shown below and see the results in the Profiler.

II) DEBUGGING THE PROBLEM

Query 1 for July 2005

withmember [Product].[Product Categories].[Agg] as Aggregate(TopCount([Product].[Product Categories].[Product].members, 3, ASSP.FireTraceEventAndReturnValue([Measures].[Internet Sales Amount])))select {[Measures].[Internet Sales Amount]} on 0,[Date].[Calendar].[Month].&[2005]&[7] on 1from [Adventure Works]where ([Product].[Product Categories].[Agg])

Profiler Results for Query 1

Even without knowing the theory part, you can find out that the measure gets evaluated at 4, which is after the axes get evaluated (1, 2 and 3 in the image above) and hence, the top 3 products would be calculated for the current context of July 2005. I have not pasted the entire trace here but if you run it, you can see that the measure values for each product for the month of July 2005 is returned in the Integer Data column. I have marked the top 3 values in red and you can see that this corresponds to $261,231 which is what we saw in the results for the first query.

Query 2 for July 2005

select {[Measures].[Internet Sales Amount]} on 0,[Date].[Calendar].[Month].&[2005]&[7] on 1from [Adventure Works]where (TopCount([Product].[Product Categories].[Product].members, 3, ASSP.FireTraceEventAndReturnValue([Measures].[Internet Sales Amount])))

Profiler Results for Query 2

Here, you can see that the measure gets evaluated at 1, which is before the axes get evaluated (3 and 4 in the image above). I have also marked the top three values for the products, and on close examination, it can be found to be the top 3 values for products across all months.

So once the top products are found, the axes are evaluated and then the results are serialized in 5, which gives us the value of $221,852 which is what we saw in the results for the second query.

III) SUMMARY

Even without knowing the theory, we were able to debug the MDX queries and find out why there was a difference with the help of the TraceEvent function. This technique can be elaborated and used across variable scenarios for query debugging. In short, the TraceEvent functionality is a great function to have when you don’t know why you are getting some particular results in your queries. And yeah, don’t forget to thank Greg for this!

Reducing SSRS RDL size by uploading Shapefiles to ReportServer

2012-01-15T11:45:00.000-05:00

I don’t know about you guys, but I am really petrified of public speaking. So I always try to read about it and it really gives me some comfort to know that I am not alone. In fact, if your fear of public speaking is between mild anxiety and complete terror, you are said to be well within the normal range. I would not be exaggerating if I said that some studies show that there are people who rate their fear of public speaking as more severe than fear of death. Last year was when I finally decided to embrace my fear and I managed to speak at a local user group event in London and also at SQLBits (trust me, it wasn’t that hard as I thought it would be). And since now is the time everyone makes resolutions, I was trying to take some hint from 50 New Year’s Resolutions for Public Speakers and watching my presentation video from the last SQLBits. Seeing it, I realized I had missed mentioning an important point regarding map reports and hence decided to blog about it before I get too lazy.

People who are familiar with map report development would already be aware of the size issues when dealing with shapefiles (If you are a complete newbie on map reports, I would strongly recommend you to go through my presentation video). Most of the shapefiles are in MBs and since this data is embedded in the report RDLs, the size of the RDL also goes into MBs. The problem as well as the solution are demonstrated below:-

1) Make a simple report from a shapefile by selecting the Basic Map option in the Map Wizard. I haven’t linked the shapefile to a dataset for simplicity purpose.

2) Now you can verify that the shapefile data has been embedded in the report by right clicking on the report in the solution explorer, and selecting the view code option.

You can see that the territory names are there in the rdl code.

3) Now deploy the report to the report server and then, download the rdl to check the size.

You can see that the rdl size is 3.11 MB. The shapefile that I had used for this report was 2.4 MB.

4) Now to solve this issue, we will have to upload the shapefile (.shp and .dbf files) to the report server. For this, navigate to the folder in Report Manager and click on upload. Then browse to the shp and dbf files and click ok.

5) Once this is done, go back to the report in BIDS and click on the map twice to bring the map layers panel on the right. Right click on the polygon layer and select Layer data option.

6) Now change the option from Data Embedded in Report to Link to ESRI Shapefile. Now give the location of the files in your report server (In this case, \Blog Reports\COL_adm1.shp). Note that the location has to start with a slash followed by the folder names within the report manager and then the file name of the shp file.

7) Once this is done, you will not be able to view the results in BIDS. You can view the report code to confirm that the map data is not embedded in the rdl now. Just deploy the report after that and preview it in report manager to ensure that it is working.

8) Now, download the rdl from the report server and you can see that the size has been considerably reduced, from over 3 MB to 12.2 KB.

It would actually be a good practice to store your shapefiles in the report server at the end of your development. This way, the shapefiles can be reused by multiple reports if needed. Also, you would not need to open BIDS in case an updated version of the shapefile becomes available later. This technique can also be used for report images though I see little benefit from it as usually the images are only a few KBs. As for performance, I quickly checked a couple of times and the rendering time was a little less using this technique. Maybe I will do a detailed performance test and post a blog on it later. Till then, adieus amigos!

A New Year and a New Job

2012-01-08T12:37:00.001-05:00

For people who are connected with me, this would not come as much of a shock but yes, with this post comes a big change for me. By the time this post gets published, I would have already moved on from MindTree Ltd and started work at Mariner, my new work place. This would also mean that I have changed my base from London, UK to Charlotte, USA.

I have lots of fond memories about MindTree and the decision to part was indeed very difficult. I will definitely miss my colleagues there and wish them all the very best for the future. At the same time, I am also very excited to join Mariner and look forward to working and learning from some of the most talented people I have known. Hopefully, I will also be able to contribute something.

The new year has got me a new job and I am looking forward to a lot of things. I would like to end this post with a New Year wish that I had read sometime back, for all of my readers-

I hope you will have a wonderful year, that you'll dream dangerously and outrageously, that you'll make something that didn't exist before you made it, that you will be loved and that you will be liked, and that you will have people to love and to like in return. And, most importantly (because I think there should be more kindness and more wisdom in the world right now), that you will, when you need to be, be wise, and that you will always be kind. I hope that in this year to come, you make mistakes.Because if you are making mistakes, then you are making new things, trying new things, learning, living, pushing yourself, changing yourself, changing your world. You're doing things you've never done before, and more importantly, you're Doing Something. Whatever it is you're scared of doing, Do it. And I hope, somewhere in the next year, you surprise yourself!

Using SSAS formatting in SSRS

2011-12-30T15:40:00.000-05:00

Christmas time is one of my most favourite times of the year – the time when you can just lay back, relax, enjoy some quality time with your family and catch up with your old friends. While this Christmas time has been a little hectic for me considering that I changed my base from London, UK to Charlotte, USA (I promise to say more on that in a later post), I still found time to catch up with family and friends. So I was talking with an old colleague of mine, and as is with most techies, we ended up discussing work after some time (now I know what the idiom means – All roads leads to Rome). Well, I should warn you that my friend is not a great fan of SSRS, and he was cribbing about SSRS and the difficulties he faces when dealing with it. Being a SSRS enthusiast, I couldn’t resist giving solutions or workarounds to most of the problems he said. One of his major concerns was about the formatting in SSRS. He was totally annoyed that the decimal/currency formatting he did in SSAS did not carry over to SSRS and that he had to modify potentially many reports to replicate the formatting change. That is when I jumped in and said that you could achieve the same in SSRS if you want to.

To demonstrate the solution, I am using the AdventureWorks database. Follow the steps below to reproduce the solution:-

1) Create a new SSRS report and use the query below to create a dataset
SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Gross Profit Margin], [Measures].[Internet Order Quantity] } ON COLUMNS,
NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) } ON ROWS
FROM [Adventure Works]

Note that [Measures].[Internet Sales Amount] is a measure expression, [Measures].[Internet Gross Profit Margin] is a calculated measure and [Measures].[Internet Order Quantity] is a base measure.

2) Create a simple matrix in SSRS using the fields above and preview it.

You will notice that the measures are not formatted while the same measures in SSAS are formattted, as clear from the cube browser preview.

3) Now, to get the same SSAS formatting in SSRS, you will have to modify the MDX query to include the cell property – format_string as shown below.
SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Gross Profit Margin], [Measures].[Internet Order Quantity] } ON COLUMNS,
NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) } ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE, FORMAT_STRING

4) Once the query is modified, click on each of the 3 textboxes that need to be formatted, and enter the following code in the format property
=Fields!<name>("FORMAT_STRING")
E.g., for the Internet Order Quantity, the code and image is given below
=Fields!Internet_Order_Quantity("FORMAT_STRING")

5) Now preview the report.

We can see that the format for Internet Order quantity is correct, but the other two has been overwritten by Currency and Percent. This is because the format strings in SSRS and SSAS is not the same for all formats. To correct this, I have used custom code in the next step.

6) Click on Report menu and then select Report Properties. Copy and paste the following code into the custom code tab

Dim public RSFormat as String
Public Function FindFormat(ByVal ASFormat AS String) AS String
If (ASFormat="Currency") then
     RSFormat="c"
ElseIf (ASFormat="Percent") then
     RSFormat="p"
ElseIf (ASFormat="Standard") then
     RSFormat="0"
Else
     RSFormat=ASFormat
End If
return RSFormat
End Function

P.S. : I have taken care of the frequently used format strings, but if there is any format that I have missed, that can be easily added to the code with a ElseIf statement.

7) Now click on each of the 3 textboxes that need to be formatted, and then replace the code used in step 4 with the following code
=Code.FindFormat(Fields!<name>("FORMAT_STRING"))
E.g., for the Internet Order Quantity, the code and image is given below
=Code.FindFormat(Fields!Internet_Order_Quantity("FORMAT_STRING"))

8) Preview the report and now you should be getting the same format that was in SSAS also.

The advantage of using this technique is that you would not need to touch your reports if you change the formatting in SSAS. This would ensure that the formatting used in your reports is standardized and consistent with what has been defined in your cube. Also, if you have multiple reports, you could compile the custom code and deploy the assembly to a server. This way, you would not need to duplicate the custom code in each of the reports.

References

1) Retrieving Cell Properties
2) How to Get Extended Properties with SSAS OLE DB Provider

Where did my RDL.DATA files go?

2011-11-30T09:56:00.001-05:00

Today morning, I was reading an old post on Clearing SSRS Query cache by Alex Whittles (blog | twitter). That is when I decided to try out something and opened up my most recent project to find some rdl.data files. Surprisingly, I did not find a single rdl.data file although I had more than 10 reports which had been run multiple times. Even a full search of my laptop failed to yield results for those elusive files. That is when I decided to investigate further into the matter.

The rdl.data files are usually present for each report and are used only in the development environment. When we preview the SQL Server Reporting Services (SSRS) reports in Business Intelligence Development Studio (BIDS – the development environment), the query results are cached and stored as the rdl.data files in the report folder. This cache will be used the next time the reports are previewed and is the reason why the report seems to display faster. This will help in developing reports faster as we don’t have to wait a long time for the report to be displayed. However, this could also mean that the data would be stale when we are previewing the report in BIDS.

Now, coming back to the issue, I quickly noted my version of SQL Server (which is 2008 R2) and checked for updates. I realized that I had not applied the Service Pack 1 for SQL Server 2008 R2 and quickly searched the internet for a bug, which I found here. So all I had to do was to apply the Service Pack 1 which I downloaded from this link. Once the Service Pack was applied, things got back to normal and I was able to generate the rdl.data files when the reports were previewed.

P.S. : Kindly note the version to which this fix applies – Microsoft SQL Server 2008 R2 Reporting Services (without any patches/updates/service packs)

Adding a Target line to a Horizontal Bar Chart in SSRS

2011-11-24T07:38:00.001-05:00

This is usually the time of the year when I get to my laziest best. The cold weather and the approaching holiday season brings out the sloth in me and I have a huge backlog of articles I need to read as well as tend to. But somehow I have pulled up my socks and managed to come up with this article on how to add a target line to a horizontal bar chart in SSRS.

For the benefit of people who are not so familiar with the horizontal bar charts in SSRS, there is no out-of-the-box functionality for achieving a target line (unlike the column charts, where we can implement a line chart as detailed here). So when this question was raised in the MSDN forums, I put on my thinking cap and suggested a solution based on having the image of a line as a marker, something like shown below

The steps are mentioned in the forum discussion here. But a disadvantage of this approach was that it wouldn’t accommodate an increase or decrease in the number of members in the Y axis. For e.g., if the line image was designed for 5 members, then an increase in the number of members would mean that the image would spill over the X axis. Similarly, a decrease in the number of members would mean that the lines fail to reach the X axis. So I came up with an alternate solution using striplines and the steps are given below:-

1) Make a simple bar chart with a category and measure. In this example, I have used Year as the category field and Cnt as the measure value.

2) Click on the X axis to select it, then right click and unselect the option ‘Show Major Gridlines’. This will remove the gridlines on the chart.

3) Click on the X axis to select it, and then select the collections button in the StripLines property

4) Click on the Add button in the ChartStripLine Collection Editor. The properties should appear as shown below now.

5) Now change the BorderStyle property as Solid and enter the field or the number at which you want the target line to come in the IntervalOffset property as shown below

6) Select OK and click on the preview button. You should be able to see the target line in the bar chart as shown below.

7) You can increase the width of the target line by changing the BackgroundColor property to the BorderColor (in this case Black) and by increasing the StripWidth property in the ChartStripLine Collection Editor shown in step 5.

A disadvantage of this method is that the target line appears behind the bars. Apart from this, it works pretty well and you have a clever workaround the next time someone insists on having a target line on their bar charts.

Tracking Data over Time Visually in Power View aka Project Crescent

2011-10-18T17:22:00.001-04:00

All right, I know most of you Microsoft BI fans would be gushing over the new announcements made in PASS Summit 2011 last week. Now onwards, Project Crescent will be officially known as Power View, SQL Server Denali as SQL Server 2012, Juneau as SQL Server Data Tools and finally, we have a mobile BI strategy! (you can read the official press release from here and some more details on Power View from here). I decided to celebrate the occasion by blogging about one of the new features I particularly like in Power View in CTP3 version – tracking data over time visually.

For this demonstration, I have used the computer in SQL Server Virtual Labs. This is a great place to kick-start your SQL Server 2012 journey. Apart from the great tutorials, I also access it when I have to test out some new feature. Follow the steps below to create a Power View report with the ‘track data over time’ functionality:-

1) Go to the SharePoint site and click on Data Sources. Then click on the small dropdown icon on the right of the data source name and select the Create ‘Crescent’ Report option as shown in the image below.

2) If this is the first time you are opening the Crescent report, there is a chance that you might get the following error -
An error occurred while loading the model for the item or data source. Verify that the connection information is correct and that you have permissions to access the data source.

This is a bug within the current CTP and this should go off if you refresh the address bar of the browser.

3) Select Category under Product and Quantity under Sales. A table will be displayed in the layout with the values of Category and Quantity.

4) Click on the Scatter chart option as shown in the image below.

5) Resize the chart to occupy the full space. A scatter chart requires 3 measures – for the X axis, Y axis and the size of the bubble. Since we have already selected the Quantity measure, we just need to select 2 more measures for the Y axis and size. Select Revenue and # Products.

6) Once that is done, select Year under Date and this will automatically be taken as the Play Axis of the scatter chart. You should be able to get the graph as shown below.

7) Give a title and then click on the save button on top left corner. Enter the destination path as well as the file name and then click ok to publish the report in SharePoint.

8) Go to the destination path specified in the step above and click on the report name to preview it. Click on the play button to see visually how the Category changes over the year.

You can also click on a particular category and track the changes for that particular category over the year.

Isn’t that sweet and simple? Coming from a manufacturing domain, I can already think of lots of scenarios where this can be used (e.g.: tracking price changes, profitability, revenue, stock, sales, etc over time).

Of late, I have come to realize that some people face difficulties in following blogs by just reading the steps or by seeing a few images. So, I have taken a conscious decision to video-blog the steps whenever I can. You can find the video for this blog below

Tracking Data over Time in Power View

Let me know your feedback about this initiative and whether it is helping in conveying my message better. Cheers!

Generating QR codes in SSRS

2011-10-10T07:58:00.000-04:00

Over the short span of my career, I have seen many people get burnt out and change their careers from technology to some other field. It is easy to get disillusioned with a job that requires you to sit on a chair the entire day and "code" the same stuff. At the same time, I have also met people who are so passionate and enthusiastic about what they do and are still coding away to glory after 15-20 years. What makes those people different, and why don't they get bored of the "same, old mundane coding stuff"? The answer is simple if you do get to observe them - their creative spirit. To quote Edward De Bono, "Creativity involves breaking out of established patterns in order to look at things in a different way." For these people, every day teaches them something new and they like to challenge their limits. They make out new things from the same stuff by looking at it from a different angle. I always make it a point to imbibe this quality in the people I mentor, and try to put across some puzzles where they have to reuse their previous learning in a new way. One of my recent questions was on how to display QR codes in SSRS.

On asking this question, most of the answers involved in getting some custom code written (but no-one knew how to write the code) or to get some plug-in from third party vendors (which involved licence costs). But the team had to put on their thinking caps when I said that the only thing they would require is access to the internet.

For demonstrating the purpose, I have made a report which will generate business cards for all the employees in my fictional company with QR codes. Follow the steps below to generate the report:-

1) Create a dataset with the following query

SELECT        'Jason Thomas' AS name, 'www.road-blogs.blogspot.com' AS URL, '00447574713732' AS mob
UNION ALL
SELECT        'Jay Thomas' AS name, 'www.beyondrelational.com' AS URL, '67891' AS mob
UNION ALL
SELECT        'Jean Elizabeth' AS name, 'http://twitter.com/de_unparagoned' AS URL, '12345' AS mob

Instead of this, you can also use information from your database tables or SSAS cubes. For the sake of simplicity, I am directly hardcoding my dataset.

2) Drag and drop a list into the layout. Then insert the name, URL and mob details into the list. Also add a company logo to make it look jazzy

3) Drag and drop an image to the list and select the image properties.

4) Go to the General tab and select the Image Source as External. Then click on the expression and enter the following expression

="http://qrcode.kaywa.com/img.php?s=8&d=" + Fields!name.Value + Constants.vbcrlf + Fields!URL.Value + Constants.vbcrlf + Fields!mob.Value

This is shown in the image below.

(You can append any string after the URL though I am using the Name, URL and mobile here)

5) Click on preview and you should be able to get the desired results.

The secret of getting this done is by using the Kaywa site which will accept parameters and output the QR code for that as an image. Now there are plenty of sites online which will generate QR codes as well as barcodes in this format, and you can use any one of them for doing the same. And since you have already learned before that it is possible to display External images in SSRS, generating QR codes or barcodes is just an application of that knowledge.

Disclaimer
This report was developed just for having some fun with SSRS and for educational purposes. For people who are going to be using this for commercial purposes, I would ask them to go through the conditions of the site which generates QR code (for eg, Kaywa states it’s use should be only for non-commercial reasons).

uCertify 70-448 PrepKit Review

2011-10-08T07:13:00.000-04:00

Ok, let me put it up very bluntly - I am not a big fan of all those test and preparation sites for certifications that keeps floating around. The main reason for my dislike is that the questions are usually the braindumps of the exams which have been leaked out and many people can cheat their way to a certification just by studying the dumps (I still don't get it why the questions are never changed or shuffled on a regular basis for the Microsoft certifications). This eventually affects the reputation of the certification as well as the credibility of the people who have been certified. And for people who have done it the hard way, it could mean that their efforts have gone in vain. This was the first thought that came to my mind when uCertify asked me to review their test preparation software for 70-448 recently and I was certain that the product would face the ire of my blog if this was going to be another dumps based test site. Luckily it didn’t.

The very first thing that came to my notice after installing the software was the elegant and user friendly UI.

The UI has got separate sections for tests, study materials and progress tracking. The tests can be taken in two modes - learn mode (where you can review your answers immediately and read the notes also for it) and test mode (where you would be able to see your answers and score only at the end of the test). uCertify has neatly classified the tests so that there is a diagnostic test in the beginning to assess your level, practice tests to improve your skill levels and the final test (which I think is a bit harder than the real one).

The study material section is also pretty handy as it contains all the key concepts in one place.

The test readiness section tracks your history and gives you an indication of how ready you are to take the certification.

Another great feature is the Study Helper which explains the key concepts in each of the six chapters required for MCTS 70-448.

Now, coming to the pros and cons of the 70-448 PrepKit

Pros
1) Extremely strong and user friendly UI which I believe is shared for all the uCertify PrepKits.
2) uCertify’s strong policy against Braindumps
3) Comprehensive set of questions with study notes (300+) which are neatly classified
4) Test readiness feature gives an indication of when you should appear for your exams
5) Money back policy if you don’t pass within your first attempt
6) Good simulation of the test environment.

Cons
1) Even though the overall quality of the questions are good, there are some areas of improvement. For eg, questions with pictures of the actual development environment are less.
2) The How To section (visuals explaining how to do something) and Articles section are pretty much empty for 70-448.

Overall
My experience suggests that the 70-448 exam is a piece of cake for people with at least 2 years of experience in SSIS, SSAS and SSRS and they would pass the exam by buying and studying the official book - MCTS Self-Paced Training Kit (Exam 70-448). But for people who would want to get a feel of the tests before the actual exam or for those who don’t have an all-round work experience in the three sections (SSIS, SSAS, SSRS), I would recommend uCertify 70-448 PrepKit. It’s a good investment considering that you will get your money back if you don’t pass within your first attempt. And also, you can be proud of the fact that you tried to pass the exam honestly and not by depending on the illegal dumps.

P.S.
I am not being paid by uCertify to write this review nor am I getting any benefits out of the sales made through this review. However, my readers can avail a discount of 10% (in addition to any existing sale) on any test preparation software from uCertify by putting the discount code 'UCPREP' in the shopping cart.

SQLBits 9 - Hangover

2011-10-06T05:36:00.001-04:00

At the time of writing this post, I can see yellow leaves falling to the pavements and feel the cold breeze kissing my cheeks. No matter how hard I try to resist it, reality is dawning on me that the summer has gone by and that the winter is finally settling in (yeah, I know some of you might argue that we never had a summer. After all, it's been the coldest summer in 18 years). Can't believe that it was only a week before many of us were sweating in the big halls of the Adelphi Hotel in Liverpool. Oh sheesh, I did it again! I tried to avoid it but just like the winter, here comes the familiar feeling of hangover.

This was my third SQLBits and like always, I had been counting down the days from the time SQLBits was announced. Well, this time I had an additional reason - I was presenting a session at SQLBits. More than that, I had also got a free entry on Friday because I was a speaker (now that calls for a big HURRAY!). Even a loss of pay on Friday couldn't deter me from attending the conference on Friday (I had already exhausted my annual leaves for my wedding in July) and I reached there right in time for the keynote.

Even though the agenda suggested that I would be seeing cloned versions of Steve Tramack in both the Aintree and Lancaster halls using the latest Microsoft & HP technologies (check the agenda here), all I got to see was a bigger room with the wall removed and one Steve Tramack. Or wait, I thought I saw two people on the stage and it definitely wasn't two Steves. Being a victim of the hardships involved in installing and configuring SharePoint myself, I totally appreciated the idea and thought behind the HP Fast Track Solutions for Microsoft SQL Server. The best part is that you can configure a system within a couple of hours what would have definitely taken some weeks. Overall, it was a brilliant start to SQLBits.

After that, I went to Matt Masson's (twitter | blog) session on 'What's New in SQL Server Denali for SSIS'. The Facebook social life manager demo was really cool and unique, and also had a peek at many of the new features of SSIS.

Next on my list was ‘Basic BISM’ by Chris Webb (blog). This was an introductory session on Business Intelligence Semantic Model and was extremely helpful to people like me who were more or less on the start of the learning curve. Chris also touched upon the advantages and disadvantages of the tabular mode in this session.

Then I went for the ‘Myths and Monster of Flash’ session by Fusion-IO where Steve Wharton (twitter | blog) presented some of the characteristics and mechanics of the NAND flash.

Later I spent some time walking around the stalls and talking with the exhibitors. It was so interesting and informative at the same time that I actually spent a lot more time than I intended to and missed the next session as a result.

The next session was one of my most awaited ones – ‘iPad and Powerpivot-Mobile Business Intelligence in action’ by Jennifer Stirrup (twitter | blog). I had been planning to catch hold of Jen’s sessions for a long time and finally made it this time. Not to say, I had a great time learning about mobile BI and some of the best practices in visualizations.

One of the other things I was really looking forward to was meeting Hrvoje (twitter | blog) and Tomislav Piasevoli (twitter | blog) who were coming down to SQLBIts all the way from Croatia. So after the sessions, I met up with the Piasevoli brothers and we headed to the Cavern Pub for washing down our throats. Had an extremely great time with them as is evident from all the photographs.

We were keeping a tab on the time as we did not want to be late for the much anticipated event of the night – James Bond Casino Night. It really was a great experience walking in to the hall which had now transformed into a casino and we started gambling with the fake money provided to us. Most of the people were appropriately dressed in their James Bond attires also, which made the experience even better. I am sure all of us would have left the place declaring this as the best post event party in SQLBits till date.

The next day, I had to wake up at an unearthly hour as I had to rush for my 8.10 am session on ‘Fast Track to Spatial Reporting’. The demo material for my session are available for download here. Got a few pics of my session too, thanks to Tomislav.

The next three sessions I attended was definitely the highlight for many in this version of SQLBits. The first of the series was ‘Building Great Models for Crescent’ by Kasper Jonge (twitter | blog). His session was jam packed with demos and being a reporting person myself, it was really nice to watch all the new features in Crescent.

The next of the series was ‘Vertipaq vs. OLAP: Change your Data Modelling Approach’ by Marco Russo (twitter | blog). Most of the perceptions that I had built about data-modelling was challenged and I was forced to see them in a new light when I am modelling in Vertipaq.

Alberto Ferrari (twitter | blog) was next on the stage with ‘Many-to-Many Relationships in DAX’ and I was seriously amazed at how he presented such a complex topic in such an easily understandable format. This was one of the moments where I wished if only I had such people as my professors when I was studying in my University.

I checked in to the ‘Lightning Talks’ soon after, something which I wanted to do in last time’s SQLBits itself but couldn’t because it was clashing with another session. It was great to see the likes of Kasper Jonge, Martin Newman, Andrew Fryer, Richard Douglas, Ed Vassie, Neil Hambly and Tobiasz Koprowski share the same stage.

The last session of the day was kept aside for Duncan Sutcliffe’s (twitter | blog) session on ‘Kerberos-All You Need to Know in One Hour’. As usual, Duncan just blew everyone apart with his unique presentation style and all in all, it was one of the most comprehensive sessions on Kerberos that I have seen.

Soon we had the customary post event party and lots of prizes were announced from Kindles to Xboxes. A big thanks to all the sponsors without whom SQLBits would not have been possible.

Also a moment thanking all the guys behind this - Simon Sabin, Christian Bolton, James Rowland-Jones, Martin Bell, Darren Green, Chris Webb, Allan Mitchell, Tim Kent, Chris Testa-O’Neill and all the helpers. A big, BIG, BIG thanks to all of you guys.

Once the party was over, all that was left for us to do was to reminisce the last two three days and wait eagerly for the next version of SQLBits. Well, myself, Hrvoje, Tomislav and Kasper also found something else to do while reminiscing , see below for a hint.

As for the winter, I would like to borrow a few lines from Percy Shelley – If Winter comes, can Spring be far behind?

Fast Track to Spatial Reporting - SQLBits session material for download

2011-10-02T14:31:00.000-04:00

So one more objective struck off from my annual list. Even though I had nightmares where my laptop crashed just before my session, nothing untoward happened and I managed to complete my first session at SQLBits without any problems.

The session was scheduled on Oct 1 at 8.10 am in the morning (at a time when I would normally be snoring in my bed). There was a brief moment of confusion when I discovered around 7.30 am that my laptop did not have the VGA port for plugging in the projector (the new DELL systems just have the HDMI and mini USB ports). Luckily, James Boother (twitter | blog) and Neil Hambly (twitter | blog) helped me get a converter and salvaged the situation. I was able to complete all the demos within the stipulated 1 hour time though I thought I had to race at some parts. Anyways, I will be blogging about my overall SQLBits 9 experience in detail soon. Till then, feel free to download the materials that I used in my session below:-

Session slides – contains the PowerPoint presentation (2 MB)
Report RDLs – contains all the report files used in the demo (12 MB)
Database backup – contains the spatial database used for the reports (41 MB)
Demo notes – short notes describing the demo (5 KB)

Book Review : MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

2011-09-22T15:58:00.001-04:00

What do you do in your evenings when you are sent off from your base location on a month long consulting assignment? Well, I had an option to soak up the old Beatles numbers in the Cavern Pub (the birthplace of Beatles) every evening, now that I am in Liverpool. But a particular book got me hooked this time and every weekday was just a restless wait for the evening to come. My readers, you might be misled by my words in thinking that I am reading the latest Dan Brown thriller but trust me, I am not exaggerating when I say that I couldn’t keep this book down till I completed it.

Neither the book nor the author needs introduction. The book has already been lauded by most of the industry experts like Chris Webb, Marco Russo, Vincent Rainardi, etc. Tomislav Piasevoli (MVP | blog | twitter) has been one of my favourite bloggers and I have been following him for quite some years now. So it wasn’t quite surprising that I have been following up on the book updates ever since I saw the RAW e-book version (something like a beta release, just has a few chapters) on my PackT online subscription during the month of March. And luckily for me, the launch of the book coincided with my consulting assignment. I have spent quality time with the book most of these evenings (in fact, so much that I think it made my wife jealous ) and I am thoroughly impressed with the format of the book.

Walking back the memory lane, I always yearned for a SSAS book that had examples of common design and implementation issues and was overjoyed to find the same in Expert Cube Development with Microsoft SQL Server 2008 Analysis Services. Once I went through this book completely, I tried to find a similar one for MDX too but in vain (found one for SSRS in the process - Microsoft SQL Server Reporting Services Recipes for Designing Expert Reports). Well, I am glad to say that the search has finally ended with the advent of the MDX cookbook by Tomislav. The book follows a simple and easy to understand structure throughout the 9 chapters :

- Getting ready :- getting the pre-requisite queries ready for the task
- How to do it :- how to do the task
- How it works :- how the task works
- There’s more :- alternate ways and more information on the task
- See also :- links to related information and tasks

Once I got familiar with the format, I was just reading the ‘Getting ready’ part and trying to solve the task on my own. After that, I would compare my answers with the next 2 sections and it enriched my reading experience. Though the initial chapters were relatively easy, I was scratching my head as the chapters neared the end and I have learned a lot of new things through this book. This is not really intended to be your first MDX book but is a MUST HAVE once you have tasted a bit of MDX. All you MDX developers, you are definitely missing out on a great book if you haven’t read this. Now if you are not sure where to buy it from, here is the link. The Kindle version is also available in Amazon and you can download a sample from here to get a better feel of how the cookbook and its recipes are structured. As for me, I have just added it to the favourites on my blog’s Amazon widget.

Export to Excel upgrades in SSRS Denali CTP3

2011-08-11T10:21:00.001-04:00

I don’t know whether it is the same with you guys but for me, it is a bit hard to get on track and start work after a long vacation. Usually, the initial few days are spent idle before the computer and if I don’t make an effort to control it, my moods become crankier than the schoolboy who doesn’t want to go to school. However this time has been a bit easier for two reasons: (a) My wife who is a constant source of encouragement (benefits of being newly wed! ) and (b) release of SQL Server Denali CTP3. There has been lots of interesting and new features to test out and this has become the proverbial carrot for me to get out of my sluggishness. This article is about one of those new features in SSRS Denali CTP3 – the feature upgrades in exporting a SSRS report to Excel.

Before I start listing down the new features, I would like to show you guys a screenshot of the new developer environment.

Yes, the old BIDS has given way to a new mean Visual Studio 2010 environment and I totally dig the blue background. Isn’t it cool?

Now before I get distracted with the other features, it is high time I say the upgrade is – SSRS renders a report to the native format of Microsoft Excel 2007-2010. The format is Office Open XML. The content type of files generated by this renderer is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet and the file extension of files is .xlsx. The benefits of this over the previous .xls format (Excel 2003) are listed below:-

1) Max columns per worksheet increased from 256 to 16,384

If you are exporting a report with more than 256 columns in a worksheet to the normal .xls format in Denali or in SSRS versions prior to Denali, you will get the following error message

You will no longer see this error message if you are exporting to the .xlsx format.

2) Max rows per worksheet increased from 65,536 to 1,048,576

Similarly, if you are trying to export a report with more than 65,536 rows in a worksheet to the normal .xls format, you would be greeted with the following error message.

However, this will no longer be an issue when you export to the new .xlsx format.

3) No of colours allowed in a worksheet increased from 56 to approx. 16 million

I am sure this is a very welcome move for many. Prior to Denali, you couldn’t get more than 56 colours in a worksheet but with the new .xlsx format, this is very much possible. For eg, consider the following colour matrix that I made in BIDS.

Now when I export it to .xls (Excel 2003) format, I get the following output in excel

We can see that the entire last row is blacked out due to the colour limit. However, if we export it to the .xlsx format, we get the proper output.

BUG ALERT

Even though the excel export worked as expected in the case of pre-defined background colours for each cell, it did not show up the correct results when I used custom code to show the background colours for the members in a group.

For eg, the following output came up properly in .xls format

But when the same report was exported to .xlsx format, it started showing the last colour (which is Green) for every cell.

This has been raised as a bug in Connect. Vote for it if you want it to be rectified fast.

https://connect.microsoft.com/SQLServer/feedback/details/683691/ssrs-denali-export-to-excel-colors-not-being-displayed-correctly

4) ZIP compression

The .xlsx format has ZIP compression, hence the size of the files generated would be lesser. For eg, I generated a report with 250 rows and 250 columns in both .xlsx and .xls formats. The .xlsx format was just 25KB while the .xls format was 35KB in size. This difference may become more evident and important in the case of large reports.

That’s all from me this time folks. Hopefully, I will be back with some more of the new features.

And here I announce the good news…

2011-08-03T09:48:00.001-04:00

To all my loving and caring readers who had been wondering where the hell did I get lost the last month, I have two good news for you.

1) I got hitched!

Yes, I had been on a vacation last month and meanwhile managed to upgrade the status of Ms Neethu Sebastian from long-time girlfriend to wifey . The wedding was a lot of fun but hectic to the core (roughly translates to – enjoyed it but never doing it again).

2) My session got selected for SQLBits 9!

Yes, I will be speaking at SQLBits 9 – Query across the Mersey. Just got the confirmation from the SQLBits team that my session – Fast Track to Spatial Reporting has been selected. The session would be held on Oct 1, Saturday from 8.10 to 9.10 am. Please click on the agenda below for more details.

P.S. : If you are attending SQLBits 9 in Liverpool, make sure that you drop in and say a hi to me

SSAS Best Practices–Session slides for download

2011-07-04T13:11:00.001-04:00

Looks like I am on a session taking spree, this was my third one in 3 weeks. This time, I decided to collate some of the best practices in SQL Server Analysis Services and present it for the BI minds in my company, MindTree Ltd. This is one subject that just doesn’t finish in 1 hour, and even though I tried to cut down a lot of points, I ended up spilling by almost 15 minutes. A big thanks to Emil Xavier for organizing the session and for arranging the logistics, everything was prim and proper. Anyways, without boring you guys further, here goes the session slides. Feel free to pass it around!

Let’s Go Spatial – Session slides for download

2011-06-22T11:06:00.001-04:00

So that’s one objective struck off from my list for this year. Yes, I made a presentation on the mapping features in SSRS 2008 R2 for the London BI User Group on 21 June, 2010. The UG meeting was held in the Hitachi office near London Bridge and was attended by around 30 people. A big thanks to Hitachi Consulting for sponsoring the beer / pizza and of course, for allowing us to use their office for the meeting. Another round of applause to Chris Webb (blog) for organizing this UG meeting and personally, my gratitude for giving me the opportunity to speak. Though my presentation was more focussed on demos, I am attaching the slides that I used for my session – ‘Let’s go Spatial’ along with this post. I will try to upload the rdls of the reports also but that might take some time. So keep checking or ping me if you have got some urgent need for the sample reports used in my presentation.

The demos included

Creating map reports from ESRI shapefiles
- How to deal with deployment errors with large shapefiles
Creating map reports from Spatial Data
- How to import shapefiles into your database
Visualizing your map reports using measures
How to drill up and down your map reports
How to do custom aggregation in your map reports
Creating bubble maps in your map reports

This was followed by an amazing session on Change Data Capture by the charismatic Duncan Sutcliffe (blog | twitter). Overall a nice day and looking forward for more BI user group meetings.

When and How to Snowflake Dimension Sources : SSAS Design Part 3

2011-06-09T06:48:00.001-04:00

In my last post, I had quickly hinted why I prefer using a star schema and also explained the second scenario in which I would snowflake the dimension sources for the DSV.

The three scenarios as well as the example product dimension are repeated below for quick reference

Multiple lower levels for the dimension (Scenario 1)

Multiple facts coming at multiple granularity (Scenario 2)

Multiple levels joining to the same parent (Scenario 3)

Scenario 3 – Multiple Levels joining to same Parent

Consider the above example where there are multiple levels (Local SKU and Nielsen SKU) joining to the same parent. Invariably, a “diamond” or cyclic relationship would be involved the moment you say that there are multiple levels joining to the same parent in SSAS dimensions (You could avoid this by splitting the dimensions and making referenced dimensions in SSAS but then you would lose the ability to make hierarchies between the levels in the two split dimensions). For Scenario 3 to be applied, an additional condition also needs to be satisfied – at least one of the intermediate attributes involved in the cyclic relationship needs to have fact coming in at that level and this attribute should not be the start point or end point of the cyclic relationship. Some examples of diamond relationship are given below

In our example, both Local SKU and Nielsen SKU have fact coming in at their levels and they are neither the start point (Dummy Level) nor the end point (Retail SKU) of the cyclic relationship, and hence Retail SKU qualifies under Scenario 3. In this scenario, the following views would be made

a) View for parent level – A view is made for the parent entity (Retail SKU in our example) which will contain all the attributes / entities till the next entity where one of the three scenarios’ happens. For our example, one view is made.

// View for Retail SKU and attributes

SELECT DISTINCT

// Scenario 2 at Brand Pack

Retail_SKU, Standard_SKU, BrandPack#FK

FROM

<table>

WHERE

// <levels inside “diamond” at which fact granularity comes>

<level=Local_SKU_ID or level=Nielsen_SKU_ID>

Some things to note here

As mentioned in the note section of my previous post, there is a basic difference in the WHERE clause of the views in Scenario 3 and the rest. For the rest of the scenarios, the clause would be for the lowest level of the view. So if the same principle was applied to the above view, then the WHERE clause would have been for <level=Retail_SKU>. Consider a case where there are Retail SKUs which don’t have any Local SKUs or Nielsen SKUs. There would not be any facts associated with them, and would be a pain for the business users if they start appearing in the dropdowns for the reports. Having the Scenario 3 WHERE clause would prevent this from happening as only the Retails SKUs which have facts associated with them would be brought in the cube.
The reason why Brand Family doesn’t qualify for Scenario 3 is because apart from the lowest level (Brand Pack), there are no attributes in the “diamond” relationship which has fact coming at that level. Hence, all these attributes have come under the view for Brand Pack in Scenario 2.
In both Scenario 1 and Scenario 3, there is a cyclic relationship or diamond relationship present in the model. These views will only ensure that the master data is correct. Care should be taken to handle the cyclic relationships within SSAS by defining multiple hierarchies or by making the relationship linear. Else you might see the blue squiggly line in the dimension attribute relationship manager in SSAS warning you of the occurrence of cyclic relationships.

That said, this marks the end of my trilogy on When and How to Snowflake Dimension Sources. I have made an effort to make it as generic as possible, but there could be scenarios which I have missed. Feel free to throw bricks or debate on the above concepts, constructive criticism is always welcome!

When and How to Snowflake Dimension Sources : SSAS Design Part 2

2011-06-01T18:57:00.000-04:00

As a response to my previous post, one of my readers asked me this question – “ why are you hell-bent on making a star schema? You yourself have told that a snowflake schema would give the best performance during cube processing and once the cube is processed, it doesn’t matter which schema was used – the query performance would be the same. So make the designer’s life easier by making all the dimension attributes in 3NF, obviously nothing can go wrong there! “

Well, I have been extremely lucky to work along with a highly technical support & maintenance team at the client site, who challenges and makes me give appropriate reasons for every design decision I take. We as a vendor develop our applications and pass it on to the support team for the final code review and acceptance (this is in addition to the UAT or data testing part which would be done along with the business users). This made me think of them as two separate customers with two different priorities for the same application. If I have to sell my application to them, I need to appease both of them and the only way I can do is by doing a fine balancing act. The Business Users would want the best performing system (both in terms of cube processing as well as query performance) while the Support Team would want a system which is easier to support and maintain. If I designed all my attributes in 3NF, the support team would straightaway reject the application as they wouldn’t make sense what is happening in the DSV with the hundreds of attributes and relations. Since my cubes are usually less than 50GB, the processing time difference between the two schemas is less than 15 minutes which my business users would not even notice. In this case, it makes sense to use a star schema and appease my other customer, the Support Team. Your support team would love you for the additional work you do in your DSV’s diagram organizer when you neatly show them the star schemas for every fact.

In my last post, I had quickly summarized some of the best practices that I apply in the design of my dimension sources and also explained the first scenario in which I would snowflake the dimension sources for the DSV. The three scenarios as well as the example product dimension are repeated below for quick reference

Multiple lower levels for the dimension (Scenario 1)
Multiple facts coming at multiple granularity (Scenario 2)
Multiple levels joining to the same parent (Scenario 3)

Scenario 2 – Multiple facts at multiple granularity

Consider the example in the above figure where there are facts coming in at multiple levels of the same dimension (at Prod Group, Brand Pack, Local SKU and Nielsen SKU levels). In this scenario, all the members of the levels at which the fact comes are needed and hence, the following views are made

a) Individual views for each of the level at which fact granularity comes – For each level at which the fact comes, a view is made which will contain all the attributes / entities till the next entity where one of the three scenarios’ happen. Since there are 4 levels at which fact comes in this example, the following views are made

// View for Local SKU and attributes

SELECT

// Scenario 3 at Retail SKU

Local_SKU, RSKU#FK

FROM

<table>

// View for Nielsen SKU and attributes

SELECT

// Scenario 3 at Retail SKU

Nielsen_SKU, RSKU#FK

FROM

<table>

// View for Brand Pack attributes

SELECT

// Scenario 2 at Product Group

BrandPack, BrandSF, LocalBrandSF, BrandFamily, RPCT, RPCTGrp, PG#FK

FROM

<table>

// View for Product Group attributes

SELECT

ProductGroup, ReportingProductGroup

FROM

<table>

Some things to note here

If the entity for which the view is made is the lowest level of the dimension or if the entity’s immediate child is having a mandatory relationship with it, then there is no need to include the Unknown Member. Else, we would have to provide the Unknown Member for that entity. (A mandatory relationship indicates that for every occurrence of entity A there must exist an entity B, and vice versa)
There is an overlap of Scenario 1 and Scenario 2 for Local SKU and Nielsen SKU views but the resultant views are the same. These views are repeated in this post just for completeness of Scenario 2.
In the view for Brand Pack attributes, it might look like Scenario 3 applies at Brand Family level. But Scenario 3 needs an extra condition to be satisfied which is not done here. This will be explained in detail in the next post.
General rule for each individual view in Scenario 1 and Scenario 2 is to select all the attributes from the table which has children at the lowest level of that view. For eg, in the view for Brand Pack attributes, all Brand Sub Families would be present which have Brand Packs. If there is a Brand Sub Family which doesn’t have a Brand Pack, it will not be present in the view. But that particular value of Brand Sub Family can be safely omitted as there are no facts coming in at a Brand Sub Family level and hence it would not be needed for analysis (in most of the cases). This general rule would not be applicable to Scenario 3 and would be explained in the next post.

Meanwhile, as usual, let me know till what level you agree or disagree on these points.

SQL Server BI Quiz 2011 & The Analysis Services 2008 R2 Operations Guide

2011-05-31T18:51:00.000-04:00

June is the month which has the longest daylight hours of the year in the Northern Hemisphere. So all you guys in this half of the world, its time to make use of the extra daylight and try to win yourself an Ipad. Yes, BeyondRelational is back with their next edition of quiz – SQL Server BI Quiz 2011

As usual, BeyondRelational has coordinated with 30 Microsoft MVPs / experts and come up with this SQL Server BI feast for all you guys. You get to answer questions from some of the best SQL BI brains out there – every day of the month. And oh, in case you didn’t notice, I have also put together something for you on June 17. Hope to see a lot of you answering them. Now people in the southern half of the world, you don’t need to sulk, this is open for you guys too

Another useful way to spend your extra time would be to go through the Analysis Services 2008 R2 Operations Guide. A must read for every SSAS enthusiast out there, this has been written by Denny Lee, John Sirmon and Thomas Kejser. If this isn’t enough to make your mouth water, just check out the long list of contributors in the article.

When and How to Snowflake Dimension Sources : SSAS Design Part 1

2011-05-30T21:41:00.000-04:00

One of the best practices while designing a SSAS solution is to decouple your source database (which is in ideal cases, the data warehouse or the data mart but could also be an OLTP database) with the help of a semantic layer or view layer. Even though the Data Source View (DSV) of Analysis Services does provide a semantic layer, it is best to do all your intended operations/calculations within a SQL view and then use that view as the source of your dimensions and facts in the DSV. This will make sure that if the query for a dimension or fact needs to be changed at a later point of time, the SSAS solution need not be opened and make life easier for the DBA who is maintaining the system. It also has some performance benefits for the cube processing in the case of complex queries, which is beyond the scope of this post. This post tries to discuss a scenario in which you would want to snowflake your dimensions in the view layer (and not the scenarios in which you would want to snowflake your dimensions in the relational database or SSAS). Before all the Kimball enthusiasts come to bash me up, hear me out when I say that my preferred option would be to use the denormalized dimensions to create the popular star schemas. Even if the dimension is snowflaked in the relational database, I usually end up making a view on top of these normalized tables which will denormalize them into a single dimension giving a star schema look to the dimensions in the DSV. I am completely for the star schemas because of it’s simple, easy to understand and maintain structure. With this, I would also like to clear a popular misconception that the star schema is the best model for SSAS because of performance benefits due to lesser joins. Actually for large dimensions, the star schema would take longer time for cube processing than the snowflake schema and you can get a taste of this from a demo in MVP Ashwani Roy’s (blog | twitter) presentation on Analysis Services Best Practices.

Vincent Rainardi (blog) has written an excellent post on When to Snowflake your dimensions in the data warehouse side, which would be a good read prior to this post. If your data modeller generally follows a star schema approach and has already taken care of the concepts outlined in the above article by snowflaking appropriately, you would be lucky enough to just create the view layer as the exact copy of your data warehouse. Else have a look at the scenario below while making your view layer-

Multiple lower levels for the dimension (Scenario 1)
Multiple facts coming at multiple granularity (Scenario 2)
Multiple levels joining to the same parent (Scenario 3)

We will use the following Product dimension as an example for explaining the three scenarios.

Scenario 1 – Multiple Lower Levels

Consider the above figure where there are multiple lower levels (Nielsen SKU and Local SKU) in the dimension. Even though SSAS can handle multiple lower levels, it is always better to create a surrogate key which can be used as the dimension key (Read this post to learn how to create a dimension with multiple column key). For this scenario, the following views would be created in the view layer which would be then used as such in the DSV:-

a. View for dummy level – For the dummy level key, a surrogate id or sequence id can be used as it would be unique. The view would be of the following format

SELECT

// SID as dummy level key and the other two as foreign keys

SID, LocalSKU_ID#FK, NielsenSKU_ID#FK

FROM

<table>

Individual Views for each lowest level – For each of the lowest level, an individual view needs to be created which would contain all the attributes / entities till the next entity where one of the three scenarios’ happen. Since there are two lower levels, two views need to be created as shown below.

// View for Local SKU and attributes

SELECT

Local_SKU, RSKU#FK

FROM

<table>

// View for Nielsen SKU and attributes

SELECT

Nielsen_SKU, RSKU#FK

FROM

<table>

It is to be noted that the attributes / entities are taken only till the level of Retail SKU because Scenario 3 (multiple levels joining to the same parent) applies at Retail SKU level.

Now, why do we have to snowflake in this scenario? Let us see how the denormalized table would be for the same three levels

SID	LSKU	NSKU	*RSKU*
1	L1	NULL	R1
2	L2	NULL	R2
3	NULL	N1	R1
4	NULL	N2	R2
5	NULL	N3	R2

Usually, I replace the NULLs by –1 in my design to avoid using the Unknown Member feature of SSAS. If this table is taken as the dimension source for SSAS and the appropriate relations are specified as SID—>LSKU—>RSKU and SID—>NSKU—>RSKU, there would be errors during dimension processing saying that there are multiple parents for the same child (for eg, –1 for NSKU would point to R1 and R2 values for Retail SKU). Snowflaking the source views as shown above would solve this problem. Please note that the SSAS Product dimension would still be a single one which would contain all the attributes from the snowflaked source views.

The other scenarios would be covered in the subsequent posts. Meanwhile it would be great to know how much you agree or disagree on these points.

Performance problems with Dynamic Named Sets

2011-04-19T19:17:00.001-04:00

The last couple of days, I have been fiddling around with an interesting mdx query passed on to me by Hrvoje Piasevoli (blog | twitter | MSDN). Though I wouldn’t be explaining about the query as Hrvoje himself would blog about it soon (once he gets off the SSAS forums and twitter, which could be never ), I was trying to recreate the scenario in the cube and was involving a lot of dynamic sets. That is when I hit upon the problem – queries which were running in split seconds started to drag. And I am not even speaking of the measures which involved those dynamic named sets. If you are a beginner to named sets, I would suggest having a quick glance through the post – Static Named Sets v/s Dynamic Named Sets before reading further.

The steps to demonstrate the issues are given below.

Problem

1) Before illustrating the problem, let us take the average times of a simple query in cold cache. For the demo, the query shown below is used

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE {[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2001]}

The average time in cold cache was around 63ms in my laptop.

2) Now a simple dynamic set is created in the cube calculated member script as shown below

CREATE DYNAMIC SET CURRENTCUBE.[ExSet]
AS (EXISTING [Date].[Calendar].[Calendar Year]);

The average time of the previous query is again checked in cold cache and is found out to be around 63ms still. All well and good so far.

3) Now create a measure which will refer the dynamic set, like shown below

CREATE MEMBER CURRENTCUBE.[Measures].SetString
AS SetToStr(ExSet),
VISIBLE = 1 ;

The average time of the previous query is again checked and suddenly, it has come up to 1.1s which is an increase of 75%. – Issue 1

4) Let us also note down the time taken to display the SetString measure which was created.

Even this takes around 1.1s on cold cache which is a lot for such a simple operation. - Issue 2

Reason

On closely checking the traces from Profiler, it is found that when the SetString measure referring the dynamic set is not created, only the above query (in step 1) gets executed (after the calculated member script is loaded into memory). However, after the SetString measure is created, in addition to the above events, a series of Query dimension events and Calculate Non Empty Current events are generated which indicates that the dynamic set is being evaluated, even though it is not being used in the query.

Workaround

A workaround for this issue is to trick the engine into thinking that the dynamic set is not being referred. This can be done by replacing the set with StrToSet. Eg – the dynamic set ExSet will be replaced by StrToSet(‘ExSet’). Hence, the calculated member definition in the script will be modified as shown below

CREATE MEMBER CURRENTCUBE.[Measures].SetString
AS SetToStr(StrToSet(‘ExSet’)), VISIBLE = 1 ;

This will solve the issue and bring the performance back to normal.

Note

This issue had been raised in Connect almost 20 months ago and Microsoft had responded saying that they will investigate this in the future release. As of now (SQL Server 2008 R2 – 10.50.1600.1), there has been no improvements and part of it could be because of the very low number of votes. Considering that the performance could get a lot more worse in the case of complex dynamic sets, it is a serious issue for me and needs to be fixed. If you also feel the same, please vote for this Connect issue – Calculated member with a reference to dynamic named set Kills the cube's performance.

SQLBits 8 - Hangover

2011-04-14T13:34:00.001-04:00

Yups, 10 days have passed since I attended the SQLBits by the Seaside, and I am yet to recover from the hangover. And nopes, I am not talking of the hangover from the beer I drank (though I must admit I did guzzle down a few too many, thanks to all the wonderful sponsors who were way too generous handing out free coupons).

I had become a big fan of the SQLBits conferences, ever since I attended the last one in York. But this time, I was even more excited because it was going to be held in Brighton. Sunny forecasts and the prospect of lunch at the beach just made me count down the days till April 3. In a sadistic sort of way, I was even more pleased with myself on registering pretty early because many people could not get it done on account of the registration being fully booked. In fact, I had to use some native Indian yoga tricks to restrain myself from flaunting it off in front of my poor colleagues who couldn’t get a reservation. Due to work reasons, I had only gone for the free community day sessions on Saturday.

I reached Brighton by 8.55am and straightaway jumped into Marco Russo’s (blog | twitter) session on Analysis Services Advanced Best Practices.

Even though it was a level 400 session, Marco explained the concepts in a very simple way so that everyone could understand and the session was pretty captivating. In the end, I took away some very nice design related tips for SSAS dimensions for scenarios like SCD type 2, parent-child, etc.

Next I went for John Stevens-Taylor’s (blog | twitter) session on Data Modelling for a Flexible Cube. The room was so jam packed with people that I had to stand at the back and even then, people were pouring in. Since I had sprained my ankle the other day, I couldn’t stand for long and I came out rueing on not coming early to the room. This feeling was compounded when I came to hear the amazing feedback on the session.

After that, I was double minded on whether to go for the SQL Product Team Panel session by the SQL CAT Team or for Vincent Rainardi’s (blog) session on Advanced Dimensional Modelling. I decided to go for Vincent’s session and had a very enriching time there learning about Fact Table Primary Key, Vertical Fact Tables, Aggregate Fact Tables, SCD Type 6, Snapshotting Transaction Fact Tables, Dealing with Currency Rates, When to Snowflake, etc.

Soon after the session, I was able to materialize my dream of having lunch at the Brighton beach side, soaking up the warm sun and watching the surfs getting washed up the shore (ok, I admit, and some hot chicks in bikinis )

The next one I attended was Jamie Thomson’s (blog | twitter) session on SSIS Dataflow Performance Tuning. Though I am more of a SSAS/SSRS person than SSIS, I just couldn’t stop myself from attending Jamie’s session, I had attended one of his sessions in a Local User Group meeting, and have remained a big fan of his presentations after that.

His session was heavily based on demos which made it very interesting and I had some great takeaways from his session on performance tuning and execution trees.

Next on my list was Alex Whittles’ (blog | twitter) session on Automating SSAS cube documentation using SSRS, DMV and Spatial Data. Now if you are one of those who follow the SQL world in twitter, you would be already aware of the ‘Spatial Art’ tweets being passed around by Alex, Alastair (blog | twitter) and Simon (blog | twitter). This got me hooked and I was waiting for the session to actually see it live.

Alex’s slides were so beautifully done and I was so much in awe of his presentation skills. The topic was like a breath of fresh air and to top it all, I won a book on Microsoft SQL Azure during his session.

Soon we had the customary post event party and lots of prizes were announced from 50” plasma TV to Xboxes. Everyone I spoke to had lots of fun and a big thanks to all the sponsors without whom it would not have been possible.

A special mention for Fusion-IO for successfully conducting the Crappy Code Games in Manchester, London and Brighton, was really an awesome event. Totally loved it! (Click here to read my London experience).

Now this post would not be complete without thanking the people behind it - Simon Sabin, Christian Bolton, James Rowland-Jones, Martin Bell, Darren Green, Chris Webb, Allan Mitchell, Tim Kent and Chris Testa-O’Neill. A big, BIG, BIG thanks to all of you guys.

Now for those who missed SQLBits 8 (and those who are still in the hangover like me), what better way to relive the experience than Jamie’s videos. Catch them right here - Reflections on SQLBits 8

MDX Basics

2011-04-05T10:49:00.000-04:00

One of my colleagues in MindTree Ltd, Bragdishwaran U, had recently taken a session on MDX Basics. I found the session well structured and specially asked his permission to publish the slides from his deck for the benefit of my readers who are completely new to MDX.

Feel free to comment and let me know if there are any additional areas you would like to see being included here.