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