MDX Learning Blog and SQL Server BI

MDX - means Multi Dimensional Expression, a key to all advanced functionality in Analysis Server

Books to read - to get more idea about OLAP DM(Dimensional Modelling)

1) Data warehouse Toolkit, A complete guide for DM - Ralf Kimbal, Willy Pubs

2) MDX - MDX Solutions for SQL2005 and HyperonSpace - George Spafford, Willy pubs

Slice - Members of One Dimension

Dice - Members of more than one dimension display

A cube can have up to 128 Dimensions

A MDX can have up to 64 axes

While fetching the level Name can't precede the member Name

KPI - KeyPerformanceIndicator - Why they are at back end, they are supposed to be at front face as they are similar to Dashboard.

Note: These exists from quite a long time in the industry, but are new from Microsoft's perspective and for the first time they have included

them in their product

Microsoft Dashboard Manager or KPI Manager

Scorecard - is the information that is WRT other information

drill through- Is another feature that every OLAP tool should support.

MOLAP - Is the +vesFastest storage ModeRetrival is Fast,-ve is Latency

ROLAP - will only create the Cube and the data is still there in RDBMS

HOLAP - Details data is still there in RDBMS, where as Aggregates are stored at Dimensional Model, in other words,in Cubes

Some of the SQL Server and BI collection of books:

SQL SERVER 2005 and BI Books Collections

1) MDX Solutions: with Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase, Second Edition by George Spofford, Sivakumar Harinath, Christopher Webb, Dylan Hai Huang, Francesco Civardi

2) Applied Microsoft Analysis Services 2005 : And Microsoft Business Intelligence Platform by Teo Lachev.

3) Professional SQL Server 2005 Integration Services by Brian Knight

4) The Microsoft Data Warehouse Toolkit : With SQL Server 2005 and the Microsoft Business Intelligence Toolset by Joy Mundy

5) Microsoft SQL Server 2005 Reporting Services 2005 by Brian Larson

6) The Rational Guide to Scripting SQL Server 2005 Integration Services Beta Preview (Rational Guides) by Donald Farmer (got this one a couple of weeks ago - great book!)

SSRS - Keeping the format of the Data when we export it to excel

Today I found this Blog link about the SSRS. He talked about how to keep the format of the data when we export the report to the excel. Here we go to his link.

http://blog.stevienova.com/2007/06/18/ssrs-exporting-report-to-excel-keep-formatting-on-export/

It’s about keeping the format of the data when we export it to excel.

The same guy posted this in another link where his previous solution is not working.

Using Wrap expression in CDec() function.

Another interesting post he posted among his blogs, which is about the weird behaviour of GOOGLE.

I really wonder, how they shrewd they are!

Read about the weird bug in GOOGLE

The blogger said that he accidentally typed the word with apostrophe in front of the word. In the resulting page he doesn’t find the top search bar was missing.

I also tested with number of words. Just click on the following link If you want to test or type a word like this – ‘IAMGOD

http://www.google.com/search?hl=en&q=%27NERD

Using SSIS Package as my Data Source in SSRS

I have a requirement from my customer. They are migrating to the SSRS from Excel Reports. Currently they have a SSIS Package which will get the columns needed from the database to the excel sheet and the business users are asked to look into that report. They asked to do the same report in SSRS and email it to the customers.

For the above problem I can have 2 type of solutions:

1) Accessing the data directly from the source of the SSIS.
If your source database is OLTP system and running the Data Source will affect the performance of the Database.
2) Using the existing SSIS package as the Data Source of the report.
When I was searching for this functionality, my friend told me about the possibility of doing it. He asked me to change a couple of config files and you can use the SSIS package as your Data Source of the report.

Steps to Follow:
Follow below set if you need to set SSIS packages as data source for your reports.
1) Open RSReportDesigner.config file through text editor. It is stored in the \Program Files\Microsoft VisualStudio8\Common7\IDE\PrivateAssemblies folder.
2) Remove comment marks that have been set for SSIS extensions under data and designer element. Save and close it.
3) Open the rsreportserver.config file in the \Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer folder.
4) Clear the comments marks for SSIS extension under data element.

I have already heard of using XML file as the Data Source to the Report. But you need to add a assembly in the report server level to do this.

I welcome your suggestions and views regarding the posts.

Auto Printing Functionality in SSRS

Currently my customer is using some other reporting tool. He wants to migrate the existing reports to SSRS. There was one requirement which is supported by the existing tool. I need to know whether such functionality can be achieved using Reporting Services. The Requirement is, he have some set of Reports which are auto printed based on the scheduled time. For e.g. They want the report to be printed automatically based on the scheduling (Lets Say every 15th of the month). Can we able to achieve it in SSRS or we need to code to achieve that functionality?

Your views and suggestions are welcome regarding this!

Expression Samples in SSRS

Today I come across these sites in the MSDN, which explains about the various functions and their uses in the SSRS Report expressions.

One of the important functions which we use extensively in our reports is:

=IIF (Condition1, Expr1 (Returned when Condition1 is true), Expr2 (Returned if the condition fails)

One another expression I don’t come across. I don’t know that we can use this function in the reporting services.

=Switch (
Condition1, Val1,
Condition2, Val2,
Condition3, Val3
)

1) Functions Reference in the SSRS or Expressions Examples
http://msdn2.microsoft.com/en-us/library/ms157328.aspx
2) Using Report functions in expressions – SSRS
http://msdn2.microsoft.com/en-us/library/ms159673.aspx

SSRS Reporting Criticism - Lack of Stylesheets and Fixed Column Width

Today I found this link in the net. This is a musing about the lackluster in the Reporting Services. Since I worked in some of the Reporting tools, I have come across the features that the blogger is criticizing is available in Cognos.

http://geekswithblogs.net/workdog/archive/2007/08/07/114478.aspx

The important features that SSRS lacks (According to the blogger) are:

1) Support for the CSS Style sheets. It includes both the HTML Viewer Style sheet and Print Style Sheet. One another important reason why he is criticizing is – Report headers will have Color shading, When the customer prints the report, they don’t want to waste the printer toner in printing the dark shades of the report.
2) Columns widths are fixed in SSRS when rendering the report in IE.

Some of the other interesting links by the same blogger:

List of SQL SERVER Performance articles:

http://geekswithblogs.net/workdog/archive/2005/05/11/39490.aspx

The Essential List of Vocabulary for Workplace

http://geekswithblogs.net/workdog/archive/2005/05/11/39490.aspx

This is nice, if you are not a ASSMOSIS. If you want to know the meaning, click on the above link. Definitely I am not a STRESS PUPPY. Don’t be mouse potato all the day.

SSAS Quick Tip

You want to display top 20 rows of the relational table in the diimensional table.

How you will achieve it in SSAS.

I wondered the important use of DSV is to integrate the tables from different DS.

For achieving above functionality there is one another option in DSV where you can customize your SQL to fetch the corresponding Rowset from the Relational tables.

Here is the tip as it is in the blog.

http://markiehill.blogspot.com/2005/08/restricting-rows-in-analysis-services.html

When working with a cube and you want to restrict the rowset that is returned for testing etc you can do this within the DSV.Right click on the fact table and select the replace table with named query option. You will then be presented with a query editor, edit the select statement to include select top x where x is the number of rows you want to test with. This saves you from having to ask for or change database objects / views etc , pretty cool !

Meet you all in the next blog. Awaiting the next meet.