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.