UnPivoting data in SQL Server

There are some situations when we need to do the data comparison row level. One of the solution of this kind of situation is to do the "Unpivoting" of data which will apparently move the records under a specific column and then we can do the filter of the data by using the "Where" clause.

Here is an example.
Lets take the below sample code

declare @Test as table(id int, col1 int, col2 int, col3 int, col4 int)
insert into @Test values(1, 100, 500, 300, 200)
insert into @Test values(2, 600, 300, 500, 400)
insert into @Test values(3, 400, 100, 800, 300)

select * from @Test

The expected output from the above table is to get the each rows max value. This means the output should be
ID MaxVal
1 500
2 600
3 800

Since in this case we need to do the comparison at row, the first thing which should be clicked is unpivoting of data and then do the normal filtering. To get this output we will use the SQL Server Unpivot command as below.

SELECT id,Max(colVal) as MaxVal
FROM
(SELECT * FROM @Test) T
UNPIVOT
(
ColVal FOR columnnames in(col1,col2,col3,col4)
) as UnPvtTable
Group by ID

Implementing a Microsoft SQL Server Parallel Data Warehouse Using the Kimball Approach

Here is the link available for you to download the white paper on Implementing the parallel Data warehouse using Kimball approach.
Download White Paper here

SharePoint 2010 PerformancePoint Services and PowerPivot Hands on Labs

This would be injustice if I do not share the detail on a nice training material available on Business intelligence 2010. I wanted to create and publish some KPI’s, Scorecards, Filters, Reports and Dashboards using PPS 2010 along with PowerPivot after creating the Business Intelligence site in SharePoint 2010 environment and I came across channel9’s excellent hands on lab which covers all these.

http://channel9.msdn.com/Learn/Courses/SQL2008R2TrainingKit/SQL10R2UPD03

The first three hands on labs cover all the above mentioned things. The hands on labs are using Adventure Works cube as a data source. Each lab has a word document which contains complete walkthrough of the exercises and it is very easy to follow.
Thanks to Channel9 team for their efforts on preparing such a wonderful hands on lab.
There is one more thing which I would to mention about a SharePoint 2010 VHD available which contains some PPS 2010 sample dashboards. I have not tried this but just wanted to share with you.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=751fa0d1-356c-4002-9c60-d539896c66ce&displaylang=en

For more information about PerfromancePoint Dashboards in this VHD, you can look into the below blog.

http://sptechland.wordpress.com/2010/06/09/performancepoint-dashboards-in-sharepoint-2010-vhd/

I would recommend trying the hands on labs if you want to learn creating KPI’s, scorecards, reports, and filters, dashboards using PerformancePoint 2010 along with PowerPivot.

I hope this helps

Thanks,
Gaurav Mittal

SharePoint 2010 Business Intelligence

I was working on a client demo from last few days for SharePoint 2010 Business Intelligence. There are some good references available in the web on SharePoint 2010 Business Intelligence and I thought of sharing this with everyone.
Here are the important links which I came across on web.

1. Getting Started with Business Intelligence in SharePoint 2010

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=fc97d587-ffa4-4b43-b77d-958f3f8a87b9&displaylang=en

2. BI VPC
http://www.powerpivotblog.nl/bi-vpc-version-71

3. PerformancePoint Services (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/ee661741.aspx

4. Business Intelligence Dashboards
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=da2e7bfc-c005-42e4-8b39-e1809bd65e0b&displaylang=en

5. PowerPivot vs Native SSAS
http://www.networkworld.com/community/blog/powerpivot-vs-native-ssas

6. PowerPivot vs Excel with SSAS cube
http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/388d247d-c9cb-4549-ba34-cb0c1e0c728e/

7. PowerPivot vs SSAS quiz by Chris Web
http://cwebbbi.wordpress.com/2010/11/19/powerpivot-vs-ssas-quiz/

8. SQL Server 2008 R2 Reporting Services
http://msdn.microsoft.com/en-us/sql10r2byfbi-trainingcourse_sql10r2byfbi03-rec-06

In addition to the above articles I have bunch of presentations available on SharePoint 2010 Business Intelligence. If you are interested Please email me at gauravmittal.cs@gmail.com and I can send that to you.

Hope this helps.

Thanks,
Gaurav Mittal

Repeat Tablix Row Header on each page (SSRS 2008)

Getting SSRS 2008 to repeat the Tablix header is not a straight forward setting and requires some additional steps to do so.
This used to be very simple thing till SSRS 2005, there basically we just used to right click on the Table, select the check box “Repeat header rows on each page” and we are done.







We need to take some additional steps to make this working in SSRS 2008 and the primary reason is the Table structure has been changed in SSRS 2008. Now we have “Tablix” in place of “Table” which is the combination of Table and Matrix.
Here are the steps needed to make sure Tablix Row header is shown is each page.
1. Even though the Tablix has Row/Column header properties in the property pane but this does not work especially when the row grouping is used in Tablix.
















2. In order to make sure that the Tablix row header is repeated in each page, we need to go the Advance Mode settings of Tablix.
In the bottom of Report Designer-->Under Groupng Pane-->Column Group-->Click on Advance Mode




3. In the Row Groups pane, click the static member of the row you need to repeat.
4. When we select the Static Member, the corresponding cell in the Tablix will be selected. If this confirms the cell which needs to be repeated, go to the properties of this Static Member.
5. Set the KeepWithGroup property
a) For a Static row that is above a group, select “After”
b) For a Static row that is below the group, select “Before” if the Static Row is below the Group.


6. The last thing would be to set the RepeatOnNewPage property “True”





















For more details please see the below msdn article.
http://msdn.microsoft.com/en-us/library/cc627566.aspx

I hope this helps.

Thanks,
Gaurav

Parameter could not be resolved because it was referenced in an inner subexpression

One day while working on a SSRS report I got an error message “Parameter could not be resolved because it was referenced in an inner subexpression” when I was using parameterized MDX.
Here is the sample MDX where I got this error message

with member [Measures].[Jan SalesAmt] as '([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Jan " + @Year2Digit + "]"))'
member [Measures].[Feb SalesAmt] as '([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Feb " + @Year2Digit + "]"))'
select {

[Measures].[Jan SalesAmt],

[Measures].[Feb SalesAmt]

} on Columns,

non empty

[Customer].[Customer].[All Customers] on Rows

From [Adventure Works]

This MDX was used in one of the data set where I already declared the parameter “Year2Digit” in my dataset. After spending some time I was able to fix this problem and here is the solution – Remove single quotes from the member expression and this would fix the problem.

Here is the modified MDX...

with member [Measures].[Jan SalesAmt] as ([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Jan " + @Year2Digit + "]"))
member [Measures].[Feb SalesAmt] as ([Measures].[Average Sales Amount],strtomember("[Date].[Month].[Feb " + @Year2Digit + "]"))
select {

[Measures].[Jan SalesAmt],

[Measures].[Feb SalesAmt]

} on Columns,

non empty

[Customer].[Customer].[All Customers] on Rows

From [Adventure Works]

I hope this helps.

Thanks,
Gaurav

How to fix number of rows per page in SSRS

Recently while working on a SSRS project where we had requirement to limit the number of records in report per page. This was something basically needed to improve the report visibility in the SharePoint page. If we fix the number of records per page in the report then we can control the height of the report and can avoid the vertical scroll bar in the report as well.
In this example I am going to show the 25 rows per page in the report…The steps are mentioned below to achieve this functionality.

1. Add a Row Group (Parent)

2. In the group enter the expression, =CEILING(RowNumber(Nothing)/25)

3. In the above expression 25 is the number of records displayed per page.
4. The CEILING function returns the Returns the smallest integer greater than, or equal to, the given numeric expression.
http://msdn.microsoft.com/en-us/library/aa258240(SQL.80).aspx
5. The RowNumber Returns a running count of the number of rows for the specified scope.
http://msdn.microsoft.com/en-us/library/ms159225.aspx
6. Since we are using “Nothing”, the RowNumber will consider the outermost data region, this can be replaced with the name of the specific data region.
7. Once the group is created, go to the Group properties-->Page Breaks and select the check box “Between each instance of a group”

8. Make sure you have removed the sorting from the Group Properties since in this case this will cause an error.
9. Now at last we can delete the first column which was created when the Group was added since we do not need to display anything here. Please make sure to select the “Delete Columns Only” option while deleting the column.

10. Run the report and it should return 25 records per page.

Hope this article helps…please leave the comment if this is not clear or if you have any questions on this.

Thanks,

Gaurav

Role playing dimension in SSAS

In this article I would like to talk about the "Role playing" dimension in SSAS which is commonly used and it is very important to know what the Role playing dimension is.
Basically if the same dimension is used more than once with different names in the cube then it is called the Role playing dimension.
For example suppose we are designing a cube which captures the purchasing data, we can have multiple dates in this scenario like Order Date, Ship Date and Order Received Date etc...in these kinds of situations we need to have different date keys stored in the fact tables (like OrderDateKey, ShipDateKey etc...) to get the different date information while browsing the cube. To handle this situation we do not need to create the “n” number of database dimensions for dates in the cube, what we can do is to just create a one Date database dimension while designing the cube and others can use the same with different name. For example we created a database dimension called as "DimOrderDate" and other date dimensions can be created by using the same database dimension with different names like "DimShipDate", "DimOrderReceivedDate" etc… and these remaining date dimensions should be the cube dimensions (Under the Cube Structure Tab-->In the Dimension Section-->Right Click on the Cube-->Add Cube Dimension-->Select existing “DimOrderDate”-->Give another name for example “DimShipDate”). The key thing here is to keep in mind we should have only one database date dimension and other date dimension should be created as a cube dimension. This means in the cube we will have many different date dimensions but behind the scene we are only using one database dimension. Creating one database and others as cube dimensions will also save some memory usage as the cube database date dimension will be processed once and other dimensions will use the same date dimension.
I hope this helps. If you have any questions/ comments please do leave the comment and I will get back to you as soon as possible.

Custom SSRS Templates

This is always recommended to use the custom SSRS templates specially when we are working at enterprise level and there are many SQL Reporting projects going on within an organization. Using custom Templates helps to make sure the basic look and feel of the all the reports are same, export to pdf, print report etc… is working fine across without spending additional time in each and every report to fix all these common issues.

Also we can deploy our custom Templates in Visual Studio so that any time when we add a new report file (RDL), we can choose our custom templates which we have already created. In order to deploy these custom templates we need to make sure our custom files ate available in the below location
“C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject”
Once this is done we will able to choose our custom template any time when add the new rdl file from Report project using Business Intelligence Development Studio.

I created two custom report templates
1. Landscape
2. Portrait
Here are the details on what all exist in above two report files.
1. The header and footer are already being added in both the reports.
2. The header section has a text box which would show all the selected parameters in the report. To do this we do not need to modify anything in the Header section and we just need to give parameter names in a hidden parameter.
3. The header section contains company logo in it.
4. The footer part will print the userid of the person who is running the report and page number.
5. The landscape report is used when we need to print the report in landscape format.
6. The portrait report is used when we need to print the report in the portrait format.
7. Both the reports would ensure that export to pdf and print works fine without making any changes.
We should not change the report layout or the size of the report but there always can be exception when we need to increase the width of the report if all the contents are not fitting in the template report.
At last once again I would recommend using the SSRS custom templates while working on SQL Report projects, this helps in saving some time and make sure all the reports are consistent across within an organization.
I hope this helps, please leave comments if you have any questions or thoughts on this.

BIDS Helper Tool

I just wanted to let you know if you are not aware of about a very nice tool available for BI. I except every BI developer to use this tool..."BIDS Helper" is available free for download in codeplex to install and it takes few minutes to install in dev machine.http://bidshelper.codeplex.com

There are many advantages in using this tool…
1. The first thing which I use this is for checking the dimension health, this is very helpful especially when you are dealing with the hierarchies...this helps in checking the data which is violating the hierarchy rule like data with many to many relationship etc...
2. There are many other useful options available like smartdiff (for finding the difference in code between versions), Deleting the Report Dataset Catch files (to make sure we see the refreshed data), Roles Report (gives the detail on the roles used in the cube) etc…
I would strongly recommend using this tool as this helps in improving and analysing many things while doing the BI development work.
Thanks, Guarav