Random Thoughts on Business Intelligence and Emerging Technologies

COGNOS Report with Dynamic Columns

noreply@blogger.com (Gaurav Mittal) — Sun, 06 Jan 2013 07:00:00 +0000

This is the first blog post I am writing in year 2013. I want to be little more active with respect to blog writing this year and my goal is to write at least 20 posts in 2013. Primarily my target is to write articles related to COGNOS, Informatica, Microsoft Business Intelligence, SharePoint 2013 and Data Analytics.

The first one I am going to start with COGNOS where I am going to showcase the use of macros and conditional statements in COGNOS Report Studio. The requirement is to develop a report which should display the columns dynamically based on the options selected. For example a report has three options to run the report.

Option A – Show Quantity Detail

Option B – Show Revenue Detail

Option C – Show Sales Detail

The report should show the below mentioned columns based on selected option

Option A – Year, Order Method (dynamic column) and Quantity (dynamic column)

Option B – Year, Product Line (dynamic column) and Revenue (dynamic column)

Option C – Year, Product Description (dynamic column) and Total Sales Amount (dynamic column)

The simplest but the dirty way to achieve this would be having three list objects with in the same report and hide two of them based on prompt selection.

This could be done dynamically without having the need of three separate list objects…we just need to write a macro and Query calculation columns where we can write the conditional statements to make the columns dynamic.

Here are the steps to make this working

a) Drag a List object in the report page

b) Place the “Year” data item in the first column on the List object. I am using sample “Sales Query” package.

c) Now create a Query Calculation column as a next column in the report. This column will be used to display the second column (Order Method, Product Line or Product Description) based on the selected prompt. The macro expression should look something like below

#prompt('Report Type','token','[Sales (query)].[Order method].[Order method type]')#

Please note that we are creating a new parameter called “Report Type” which we will use in the Value Prompt as a parameter

d) Create another Query Calculation column for the measures (third column). We need to write the Case expression and assign the column accordingly. The expression should be something like below

case

when(?Report Type?='[Sales (query)].[Order method].[Order method type]') then ([Sales (query)].[Sales fact].[Quantity])

when(?Report Type?='[Sales (query)].[Products].[Product line]') then ([Sales (query)].[Sales fact].[Planned revenue])

else

([Sales (query)].[Sales fact].[Product cost])

End

e) Now we need to create a prompt page…go to prompt page and drag and drop the “Value Prompt “in the prompt page. Change the “Select UI” property of value prompt to the “Radio Button Group”

f) Assign the values Option A, Option B and Option C in the “Static Choices” option in the Value Prompt.

Option A - [Sales (query)].[Order method].[Order method type]

Option B - [Sales (query)].[Products].[Product line]

Option C - [Sales (query)].[Products].[Product Description]

That’s all, run the report and you should be good to go. If we select “Option B”, we should see the Year, Product Line and Revenue columns in the report.

What is new in COGNOS 10.2?

noreply@blogger.com (Gaurav Mittal) — Fri, 05 Oct 2012 15:37:00 +0000

The COGNOS 10.2 was released on 10^th Sep 2012. I had opportunity in last few weeks to explore the COGNOS 10 and this has been very interesting journey so far. It’s amazing to see on how IBM COGNOS 10 has taken this tool forward and very well aligned with future needs of Mobile BI and Self Service Business Intelligence. Not enough, now COGNOS 10.2 has connectivity for the Big Data as well which I feel is going to play a very important role in near future.

In this blog post I am going to be focused on the new enhancements on the IBM COGNOS 10.2 version. Please note that I am not talking about the COGNOS 10 features and it’s going to be specific to the COGNOS 10.2 which was released last month in Sep-12.

Some of the features are listed below

Dynamic Cubes

I think this is the most significant feature in the COGNOS 10.2 release. The most important thing is there is no additional licensing involved for this feature and customers need not buy anything separately.

The dynamic cube comes with the concept of the In -Memory analysis very similar to what other major BI vendors like SAP, Microsoft, SAS and Oracle have.

Ø The dynamic cube model is build using the Dynamic Query Mode (DQM) functionality. Also these cubes use the extensive caching capabilities to efficiently store data within the memory.

Ø The dynamic cube supports the relational Star or Snow flake schema and provides an OLAP experience without sacrificing on high performance. Some of the key highlights are mentioned below

Ø 100 % In Memory Engine with high performance and low data latency.

Ø Aggregate recommendation to allow query routing to in memory or physical aggregate table in data warehouse.

Ø New OLAP modeling tool called Cube Designer provides better OLAP experience. The Cube Designer tool is integrated with the COGNOS Framework Manager.

Ø Multiple cubes can share the conformed dimensions.

Ø Dynamic cubes can connect to the relational databases including aggregated tables to provide the OLAP style analytics.

Ø Once the dynamic cubes are created and published as package, they can be accessed similar to any other OLAP packages.

Now the question comes on what is going to happen to existing Transformer and PowerCubes? I believe they still are going to be there and Dynamic Cubes will be a kind of complement in the existing environment. The decision to go with the Dynamic Cubes or PowerCube will be purely dependent on the Business need on each and every scenario.

COGNOS Workspace (AKA Business Insight)

IBM COGNOS 10.2 has done the renaming of the Business Insight and Business Insight Advance to Cognos Workspace and Cognos Workspace Advance. I think this new naming convention is clearer and reduces the confusion around and should be a welcoming change. There are some enhancements done in the Cognos 10.2 Workspace as listed below

Ø Muti Tab Support

I believe this is the coolest enhancement in the Workspace.

Now we do not need to worry about shuffling around horizontal and vertical scrolling. We can easily expand our visual footprints in various tabs without the need to scroll.

Ø Now we can freeze and unfreeze the crosstab columns and rows.

Ø Support for the Google Chrome and Safari browsers.

COGNOS Mobile

Cognos Mobile was one of the major enhancements in the 10.1 release. In the10.2 release there have been some enhancements with respect to mobile

Ø Cognos Mobile now supports the push notification. The users will be notified when a new of version of the report is available.

Ø The page streaming feature has been added which helps in reducing the load time before viewing the reports.

Ø Administrators now can secure the mobile access which can help organizations to implement the BYOD (Bring your own device) policy within the organization.

Report Studio

Report Studio has fewer but higher impact enhancements.

Ø Prompt API

A fully supported API is available in the 10.2 release. This will help to extend the prompting capabilities. This java script based prompt API can be used for reading, deleting and validating the prompt value. This means now we can add the validations in the various prompt before users run the reports. The Prompt API is supported only in

ü Report Studio for authoring

ü Cognos Viewer for consumption

ü Cognos Workspace/ Advance

Ø Excel Output Changes

Excel 2010 is still not an output format but the Excel 2007 output capabilities have been expanded. Now the Cognos reports can export the data with increased limit of maximum of 16,384 columns and about 1 million rows.

Architecture Enhancements

Ø Support for new Data Sources

ü SQL Server and Analysis Services 2012

ü Salesforce.Com

ü SAP ECC

ü Siebel

ü Big Data Support : Hadoop and Hive

As you might see above, the support for Big Data is very significant. I believe this is going to be one of the key features in coming next few years as the Big data is coming in reality.

Also apart from support for some other ERP vendors like SAP and Oracle you might notice that now 10.2 can host the content store on SQL Server 2012 and also have ability to connect to the SQL Server 2012 cubes.

Ø 64 Bit – Now the entire remaining 32 bit component gets an update to 64 bit. This means now Cognos 10.2 is fully 64 bit.

Ø Multi Tenancy – Cognos 10.2 now supports native multi- tenancy.

Column Store Database Technology

noreply@blogger.com (Gaurav Mittal) — Sun, 02 Sep 2012 06:44:00 +0000

Column Store database technology is something creating a buzz these days in the world of Business Intelligence as the time of Self Service Business Intelligence is coming soon and performance is going to play major role in deciding which Self Service BI tool to adopt. The Column Store Technology is something not new and it’s been there in the market since long time. I believe Sybase IQ, Teradata and now SQL Server 2012 are the major players in the field of the column store technology.

As the adoption of the self service Business Intelligence tool will grow day by day which I am sure, it is highly important for these tools to store the large amount of data effectively and have ability to play around with the data at run time efficiently. As we know unlike the OLAP where we do have the pre aggregated stored in the within the cells, in the self-service BI tools it is done on real time data based on the request.

SAP HANA a self-service Business Intelligence tool leverages the Sybase column store technology for data storage in memory, similarly Microsoft based self service BI tool Power Pivot uses the xVelocity in memory analytic engine and same technology is used for the column store index in the SQL Server 2012.

What is Column Store and how column store drastically improves the speed of the data warehouse queries?

In a traditional table the data is stored in the row- based fashion. This storage is basically the row based storage. Column Store is something like turning the row based storage to 90 degree. In the column store all the values of a single column are stored in compressed form. The column store stores each column in separate set of disk pages rather than storing multiple rows per page.

Let us take an example of the Employee Table to evaluate the different ways of storing the data.

Table Containing the Employee Data –

Employee Data Stored in the Row Format

Row Store

1 14417807 adventure-works\guy1 Production Technician - WC60 M M

2 253022876 adventure-works\kevin0 Marketing Assistant S M

3 509647174 adventure-works\roberto0 Engineering Manager M M

4 112457891 adventure-works\rob0 Senior Tool Designer S M

5 480168528 adventure-works\thierry0 Tool Designer M M

Column Store

1 2 3 4 5

14417807 253022876 509647174 112457891 480168528

adventure-works\guy1 adventure-works\kevin0 adventure-works\roberto0 adventure-works\rob0 adventure-works\thierry0

Production Technician - WC60 Marketing Assistant Engineering Manager Senior Tool Designer Tool Designer

M S M S M

M M M M M

As you might see the that column store groups and stores the data for each column together while row store groups the data for each row.

Now since we understand on how the data is stored in row and column based storage techniques, let us try to understand on how the column store significantly improves the speed of the queries.

The first and the biggest reason for significant improvement in the query speed is because the column store shares many more similar characteristics that the data stored at row level. Because of this data can be highly compressed in the column store database.

When the data is compressed, queries requires less IO because the amount of data that needs to be transferred from the disk is significantly reduced.

The compression permits the columnar operations like MIN, MAX, AVG, COUNT etc..to perform quickly.

In the case of SQL Server 2012 column store uses the xVelocity compression algorithm which compresses the data far superior than the row and page based compression techniques.

The column store technology is used to improve the Data Warehousing queries and we might see a lot of enhancements and improvements coming in this area in near future.

Get your own SAP HANA DB Server using Amazon Web Services

noreply@blogger.com (Gaurav Mittal) — Sun, 12 Aug 2012 12:19:00 +0000

Someone who is interested to explore SAP HANA and do not have access to HANA DB server can use the Amazon Web Services by creating their own instance on the cloud. Please keep in mind that this is not a free service and after completing the free trial limit you will be charged by Amazon based on the usage and volume of data.

The steps are very simple in order to create our own HANA instance using Amazon cloud services. We need to follow the below mentioned steps in order get the access to the SAP Hana instance running on the cloud.

1. Account creation in Amazon Web Services. We need to have an account on the Amazon Web Service as a first step, please note while creating the account, you will be asked to enter the credit card information which is mandatory in order to complete the registration.

http://aws.amazon.com/

2. Now once we have the account setp up, we need to generate the “Key Pair”. In order to create the Key pair we need to go to the “Amazon Management Console” àEC2 à Left hand side we should see the option called “Key Pairs”, give a Key pair name. You will be asked to save the Key Pair file which we should keep in a safe place as this will be used further.

3. Once we have the Key Pair ready, we are ready to go to the SAP site to register for the Hana. You need to Login and need to accept the License agreement. In case if you go not have the account in the SAP SCN then we need to create first in order to move further.

4. After accepting the licensing agreement, you will be asked to enter the Amazon account number which you can find from the Amazon Web Services.

5. Enter the Amazon account number and in the next page you will be asked to enter the Key Pair name which you already created in the Amazon Management console. Enter the Key pair name and click Continue. Regarding the HanaInstaceSize, use the default option “m2.xlarge” until you have need of some extra memory.

6. In the final step, you will be asked to enter the Stack name and new stack will be created in few minutes.

7. Now we should again login in the Amazon Web Services and go to the Management Console, click on the EC2 and you should see the option called “Launch Instance”. After clicking on the “Launch Instance”, the SAP Hana instance will be initiated and ready to use. We will have to give the IP Address of this instance from the HANA client in order to connect my HANA DB hosted on Amazon Web Services.

8. In order to install the SAP Hana Client, please click on the link and download the Windows (32 and 64 bit) or Linux version of client.

9. As mentioned in starting this is not a free service and you should be careful about the usage. Also in order to reduce the usage cost you should start the instance only when we are going to use. The rest of the time we can easily stop the instance, it takes less than a minute to restart the instance. In order to Stop the instance, go to the Amazon Management Console, go to the EC2 and in the home page in the left hand side under instances we should the option called “Stop” used to stop the instance.

For more details on Amazon Web Services and SAP HANA DB Server, you can visit the SAP Community Network

SQL Server Analysis Services Server Memory Issue

noreply@blogger.com (Gaurav Mittal) — Sun, 05 Aug 2012 15:25:00 +0000

Sometimes we might have noticed that SSAS is consuming too much memory in the server and creating a deadlock kind of situation where the only solution is to restart the SSAS service or restarting the server.

This situation arises when bulky MDX queries are running and trying the fetch the large volume of data. Since in the SSAS the MDX queries are constructed with in the memory, the MDX query requesting for the large amount of data can be very much memory intensive. We can avoid these kinds of situations by changing some default memory settings in the server. Before we talk about the solution I think it is very important to understand some of the memory setting properties within the SSAS. I have captured their details below as per described in MSDN.

LowMemoryLimit

Specifies the amount of memory allocated by Analysis Services at start up. When this limit is reached, the instance will start to slowly clear memory out of caches by closing expired sessions and unloading unused calculations. The server will not release memory below this limit. The default value is 65; which indicates the low memory limit is 65% of physical memory or the virtual address space, whichever is less.

TotalMemoryLimit

Defines a threshold that when reached, causes the server to deallocate memory more aggressively. The default value 80% of physical memory or the virtual address space, whichever is less.

Note that TotalMemoryLimit must always be less than HardMemoryLimit

HardMemoryLimit

Specifies a memory threshold after which the instance aggressively terminates active user sessions to reduce memory usage. All terminated sessions will receive an error about being cancelled by memory pressure. The default value, zero (0), means the HardMemoryLimit will be set to a midway value between TotalMemoryLimit and the total physical memory of the system; if the physical memory of the system is larger than the virtual address space of the process, then virtual address space will be used instead to calculate HardMemoryLimit.

In order to fix the memory freezing problem, we should change the default Memory\HardMemoryLimit setting. We should keep this value between the Memory\TotalMemoryLimit and 100. Since the TotalMemoryLimit is 80 by default, then in this case we can set the HardMemoryLimit to 90. What will happen is anytime when the memory exceeds 90%, the SSAS will start cancelling the queries. The users might see the error message saying “The operation has been cancelled due to memory pressure”.

In order to set the Memory\HardMemoryLimit property to 90, please follow the steps below

1. Login in the SSAS using the SQL Management Studio.

2. Right Click on the Server Name and Go to Properties.

3. Look for the “Memory\HardMemoryLimit” property and set the value to 90. By default it is 0.

New Business Intelligence Features for Office 2013

noreply@blogger.com (Gaurav Mittal) — Sat, 28 Jul 2012 10:09:00 +0000

After exploring quite a bit the preview version of Office 2013 it is exciting to see the nice features coming which are closely integrated with the Business Intelligence.

The office 2013 excel looks great, apart from the look and feel which I liked. There are a lot of things to explore in terms of Business Intelligence.

Some of the key features I have described below

1. The xvelocity (aka Vertipaque) in memory database is now integrated with excel 2013. The Power Pivot comes bundled with the Excel 2013 but it is not enabled by default. In order to use the enable the PowerPivot the steps are mentioned here in detail.

Integrating xVelocity simply means adding more power in Excel and I am sure users end users will like it as now they will have the ability to pull the large amount of data, defining the relationships, creating their own data model and designing the interactive dashboards.

There are some enhancements done in the Power Pivot with in excel 2013

a) TimeLine feature in addition to “Slicer” functionality which we already have.

b) We can now integrate the multiple pivot tables and can create a model out of that.

c) Ability to mash several chart types.

I believe adding these features will help excel coming out of from the tag of “ad hoc tool” for analysis and will be able to do a lot more than this.

2. Another important integration is done by providing the ability to create a sheet using “Power View”. We can go to the insert Tab and click on the Power View button to do so. The most important thing is now Power View supports the maps as well. We can easily import the spatial data and display the interactive maps in excel.

3. Office 2013 is now can connect to the Windows Azure Market place. This means we can now import the data hosted on Data Market. This seems to be very important feature with respect to cloud by providing ability to pull the data in excel hosted on cloud.

Microsoft Office 2013 Customer preview available to download now

noreply@blogger.com (Gaurav Mittal) — Thu, 26 Jul 2012 18:52:00 +0000

Microsoft has recently released the customer preview of the Microsoft Office 2013. This new version of Office 2013 is compatible for both the touch screens and desktops. However the new features of the Office 2013 are tailored to the Windows 8 but this is still going to support the Windows 7. The customer preview can be downloaded from here

Microsoft has also announced that Office 2013 will not be supporting the Windows XP and Vista and they will not be able to upgrade on top of these two versions.

UnPivoting data in SQL Server

noreply@blogger.com (Gaurav Mittal) — Sun, 18 Sep 2011 15:42:00 +0000

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

noreply@blogger.com (Gaurav Mittal) — Sun, 18 Sep 2011 15:35:00 +0000

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

noreply@blogger.com (Gaurav Mittal) — Sat, 09 Apr 2011 04:21:00 +0000

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

noreply@blogger.com (Gaurav Mittal) — Mon, 28 Mar 2011 20:50:00 +0000

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)

noreply@blogger.com (Gaurav Mittal) — Wed, 23 Mar 2011 18:14:00 +0000

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

noreply@blogger.com (Gaurav Mittal) — Fri, 18 Mar 2011 19:20:00 +0000

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

noreply@blogger.com (Gaurav Mittal) — Tue, 15 Mar 2011 04:13:00 +0000

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

noreply@blogger.com (Gaurav Mittal) — Sat, 05 Mar 2011 19:02:00 +0000

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

noreply@blogger.com (Gaurav Mittal) — Wed, 09 Feb 2011 19:04:00 +0000

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

noreply@blogger.com (Gaurav Mittal) — Thu, 03 Feb 2011 17:51:00 +0000

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

Finding missing Indexes in SQL Server

noreply@blogger.com (Gaurav Mittal) — Sun, 26 Sep 2010 18:16:00 +0000

As you might already know SQL Server DMV's (Dynamic Management Views) started from SQL Server 2005 are very helpful in analysing the SQL Server performance (finding locks, missing indexes, indexes which are not in use and causing overhead, long running t-sql queries, sp's etc...). I enjoy reading the Brent Ozar's blog in SQL Server performance and they have been very helpful personally to me in understanding the SQL Server performance tuning.
The SQL Server performance tuning used to be a task of DBA's to work on in the time of SQL Server 2000, but going forward starting from SQL Server 2005 with the help of DMV's a normal SQL developer also can contribute a lot in this area.
In this article I am going to talk about finding the missing indexes in SQL Server 2005/2008. Indexes are very critical if we talk about SQL Server performance and can impact a lot. If the transactional system is used for reporting purpose also the index can play a major role in improving performance of the reports. One thing we need to be very carefuly before adding new indexes is to make sure we analyse them thoroughly before adding. If any erp system/app is ruuning on top of SQL Server we need to make sure and understand adding a new index is not going to effect the performance of erp system/app since adding new index means more time in insertion and update activities.
I have copied the script below which is very helpful and gives top missing indexes with their impact. Adding these top index can definitely improve the SQL Server performance but once again we should very careful before adding these. We should be analysing these first in the staging/dev. environment before adding in production if required.

SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL
THEN ''
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ''
ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
ORDER BY 2 DESC , 3 DESC