Receipt for a Top 1...N SSAS / SSRS solution

Scenario : Olap based SSRS reports with one or more cascaded parameters.
One of the parameters is Top Count letting the user specify whether to display Top 5 or Top 15 or ...Top N of best selling products or most profitable companies, or the like.
The list of values is predefined according to the business requirements.
Hereby the main steps and the pseudocode for implementing the solution using the Analysis Services data provider:

1) Create one dimension, TopN. It may have one attribute TopN.
Source it from a view or a named query with a trivial t-sql like :

select <first value> as TopN
union
select <second value> as TopN
......
union
select <last value> as TopN

Set the property IsAggregatable = False for the attribute TopN, so the attribute cannot be aggregated in any hierarchy.
The dimension is not related to any measure groups.
Process the dimension, browse it's only attribute and verify that there is only one level, and no (All) level at all.

2) Create a calculated member [Top N Value]  as :

CREATE MEMBER CURRENTCUBE.[Measures].[Top N Value]
 AS [Top N].[Top N].currentmember.member_caption,
VISIBLE = 1 ,  DISPLAY_FOLDER = '<your folder>';

If your reporting solution further needs sets for filtering based on the newly created [Top N Value] you may add them as : 

CREATE DYNAMIC SET CURRENTCUBE.[Your set]
 AS TOPCOUNT([Dimension].[Hierarchy].[Attribute].MEMBERS,[Measures].[Top N Value],[Measures].[Your measure]), DISPLAY_FOLDER = '<folder for diplaying sets>';   
Deploy and process the cube.

In your SSRS report:

3) create a parameter, let's call it TopNTopN.
Your parameter is normally one of latest in the chain of cascading parameters.
That is why the Source MDX for the available values may include some subcubes :

WITH MEMBER [Measures].[ParameterCaption] AS [Top N].[Top N].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS
[Top N].[Top N].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Top N].[Top N].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Top N].[Top N].ALLMEMBERS ON ROWS
 FROM ( SELECT ( STRTOSET(@<param1>, CONSTRAINED) ) ON COLUMNS
 ...........
 FROM ( SELECT ( STRTOSET(@<paramn>, CONSTRAINED) ) ON COLUMNS
 FROM [Your cube]) ...)

Select one value among <first value> , <second value> , ... <last value> as the default parameter value.  You must be now ready for testing your report.

For an alternative solution using the OLE DB Provider for Analysis Services, please see the pages 583-584 from the book 'Applied Microsoft Reporting Services' by Teo Lachev. 

2 Tips when saving your tablix based SSRS reports to Excel

Hereby two tips related to tablix based SSRS reports using parameters saved to Excel.

1) The saved report has to start at column A and row 1.

Two conditions have to be fulfilled her :

• If you have a report title and/or logo either in the report header or in the body above the tablix then delete them. Dynamically setting the hidden property of a textbox or image does not help.Recall that you only can statically hide the report header, but this does not help either.     
• In the properties window for your tablix, set the location to 0, 0. ( Left = 0 , Top = 0 ). 

2) After saving the report in Excel, the users must be able to see the parameter values.

To accomplish this, you may create a new table with two rows : the first one with headers containing  your parameter names ( the Prompt: values you entered for each of your parameters for instance).

The second row, an expression that concatenates - for a multivalue parameter - the values selected by the user.

An example, that also distinguishes between the 'All'  and the other values might look like :

= Microsoft.VisualBasic.Interaction.IIF(Parameters!<param1>.Label(0) <> "All", Microsoft.VisualBasic.Strings.Join(Parameters!<param1>.Label, " "), "All")  

 

where you will replace <param1> with your own parameter(s).

You may also add a page break before the tablix, set the DocumentMapLabel and PageName properties in order to customize the link name in the Document Map and the name of the worksheet allocated to the parameters.  

A non existing sale or a sale with a 0 revenue ?

Who does not want to distinguish between a non existing event - sale for instance - and an existing event with a 0 as a numeric result, a revenue due to an promotional discount, or an exceptional 0 cost, etc. ?

Your data path may look like :

Data Source -> Data Mart -> SSAS Cube -> Client Reporting tool (SSRS, Excel)

The absence of data has to be transferred on the whole path from the Data Source to the Client.
Simply put, do not make 0's or ' ' out of the absence of data neither in DataMart, SSAS cube or at the report layer.

Your 'false friends' in this process may be :

1) Lack of awareness of this issue for at least one person in the normal chain :
ETL developer -> SSAS developer -> Client developer.

2) Using functions such as isnull() or coalesce() in the ETL processes. I still found a misconception even among skilled database developers, that avoid null values at the datamart level, as they are 'difficult to tackle in t-sql calculations'. 

3) The default SSAS Nullprocessing option at the measure level is set to Automatic. A better alternative is the Preserve in my opinion. You may recall, that Preserve will preserve the NULL value, the automatic is equivalent to ZeroorBlank option. It converts the null value to zero for numerical columns or to a blank string for text-based columns. 

4) You are not safe at the client level. Your Excel olap pivot table may be set to show 0 for empty cells, or you may use SSRS expressions, even a simple division by 2 for fields values may  accidentally create false 0's.

Exists, the MDX variant of 'Cogito ergo sum'

While reviewing or maintaning SSRS 2008/2012 cascading parameters reports based on SSAS olap cubes, I face quite often the lack of 'narrowing' of the values of param n, based on the values of the preceding 1...n-1 parameters.
Scenario : The reporting solution deals with customer sales on time perspective.
When selecting dates from a YMD hierarchy for instance, only customer with sales within the selected period of time must be retrieved and nothing else.
And if there are no customers, the user has to stop her instead of continuing to select values for the next parameters and experience an Empty dataset message at last.
Using the cascading parameters appropriately, can save your users a lot of frustrations.

The MDX template for defining the parameters may look like :

WITH MEMBER [Measures].[ParameterCaption] AS <<[Dimension].[attribute]>>.CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS <<[Dimension].[attribute]>>.CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS <<[Dimension].[attribute]>>.CURRENTMEMBER.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
<<[Dimension].[attribute]>>.Members ON ROWS FROM
( SELECT ( STRTOSET(@param1, CONSTRAINED) ) ON COLUMNS FROM
( SELECT ( STRTOSET(@param2, CONSTRAINED) ) ON COLUMNS FROM
( SELECT ( STRTOSET(@param3, CONSTRAINED) ) ON COLUMNS FROM
......................
( SELECT ( STRTOSET(@param(n-1), CONSTRAINED) ) ON COLUMNS
 FROM   <<Cube>>))))... )

That will only work IRL, if @param1 ... @param(n) are sourced from attributes from the same dimension due to the 'autoexists' feature.  
As soon as at least one paramter is based on another dimension, the 'narrowing' effect is gone. 
To reinforce it, you may replace the line :
<<[Dimension].[attribute]>>.Members ON ROWS with an Exists construction, that at a basic level may look like :
Exists ( <<[Dimension].[attribute]>>.ALLMEMBERS , <<[Other Dimension].[Other attribute]>>.AllMembers , <<Measure Group related to the two dimensions>>  )

The Members in the set { <<[Dimension].[attribute]>>.ALLMEMBERS } must be related to the members in the set { <<[Other Dimension].[Other attribute]>>.AllMembers } in the measure group <<Measure Group related to the two dimensions>> .

How's your week, aka YWD hierarchy looking ?

I'm quite suprized to realize the lack of 'week corrections' for the Time dimension in lots of SSAS BI solutions.

Scenario : ( affecting the weeks with days spanning two following years) .

The YQMD hierarchy is right but the YWD hierarchy is wrong. Ex: 20120101 is wrong in the YWD hierarchy as it’s ‘Week Year’ is 2011 ( as the day belongs to a week in 2011) while  - using the same Year attribute in both hierarchies – we set it to 2012 . ( right of course for the YQMD hierarchy)

You can fix it at either the relational - view or table level - or at the UDM level and in either a generic way or at detailed level.  The recommended way : at the relational level and in a generic way, but you can mix the : level ( generic or detailed ) X level ( relational or UDM ) and have 4 combinations to select.
I'll try to exemplify two of the alternatives :  

 

Generic x Relational :

 

In your view you may write a statement like :

Select ..., <Week Year> = cast(case

           when Month_Number = 12 and WeekNo = 1 then Year_Number + 1

           when Month_Number = 1 and WeekNo in (52,53) then Year_Number - 1

           else Year_Number

end as nvarchar(4)) + '-' + [Week]

from <Time view or table> 

Detailed x UDM :

In the Data Source View, select your Date DataTable, right click on it , select New Named Calculation. Name it for instance Week Year and create it with an expression like :

CASE <DateKey>

When 20071231 then 2008

When 20081229 then 2009

...............................................

When 20120101 Then 2011

…………………………………………….

ELSE <Year>

END

                           Replace <DateKey> and <Year> with the corresponding names of your columns.

Last common steps 

In your Date dimension now, drag and drop Weak Year from DSV to the dimension attributes pane .

Update the attribute relationships correspondingly.
Only in your Week hierarchy ( YWD), replace on the first level the Year attribute with the Week Year attribute.

Process the Date dimension and the cube and test the ‘week adjustments’ at the start//end of the years where you implement it.

Customize and automate reports delivery with SSRS and data-driven subscriptions

Scenario :
Some weeks ago I was suprized to realize that a few analysts used a lot of time extracting data from an application and sending reports saved in Excel format to customers. The extract was based on customers account numbers and a customer might have one or even 10 account numbers. The reports were sligthly customized and delivered by mail on a daily, weekly or monthly basis. Besides, that was an extra requirement regarding the timeline of each type of report: the weekly
reports could reflect the latest n weeks ( n= 1...24), and n was different from one customer to another. The same idea for monthly and daily reports.

Challange : To fully automate this task and give the analysts the opportunity to maintain it, to create new customers or change data for the existing
customers such as time delivery , e-mail adresses, cancelling one or more deliveries for good or for a certain period of time, etc.

Keywords: Data-driven subscriptions based on a Reporting Services in Sharepoint integrated mode infrastructure and a dialogue with the business.
Hereby the following main steps for the solution :

1) Agree on a separate prototype for the daily, weekly and the monthly report. It's a matter of 'aggregating' all content requirements for all customers for each type of report. Some customers may receive some extra information not originally required, but this is seldom a problem.

2) Ask questions. Agree on all the definitions if you'are in doubt. Weekly for instance: American or European way ? Calendar week or the latest 7 days ? Can we agree on three schedule definitions for the daily, weekly and monthly deliveries ?

3) You are now ready to start designing the three reports and highly parametrize your datasets. Create 10 parameters @acc_1 ... @acc_10 for covering up to ten account numbers for potentially each of your customer and a @dd parameter covering the timeline.

4) Create a table for your data driven subscription already at this stage.  The statement may look like:  CREATE TABLE [dbo].[SubscriptionTable](
 [SubscriptionID] [smallint] IDENTITY(1,1) NOT NULL,  [Name] [nvarchar](50) NULL,  [Acc_1] [int] NOT NULL,
 [Acc_2] [int] NULL,  [Acc_3] [int] NULL,  [Acc_4] [int] NULL,  [Acc_5] [int] NULL,  [Acc_6] [int] NULL,
 [Acc_7] [int] NULL,  [Acc_8] [int] NULL,  [Acc_9] [int] NULL,  [Acc_10] [int] NULL,  [Email] [nvarchar](150) NOT NULL,  [Daily] [smallint] NULL,  [Weekly] [smallint] NULL,  [Monthly] [smallint] NULL,
 CONSTRAINT [PK_SubscriptionTable] PRIMARY KEY CLUSTERED
(
 [SubscriptionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

5) Design the queries for the above mentioned parameters. The @acc_1 ... @acc_10 parameters are based on datasets like:  select distinct acc_1 from SubscriptionTable, and so on. That is why the data-driven subscription table has to be created so early. Your main query, used by the tablix is UNION based with a prototype like :    
select 
  < information from the columns>
  from 
    < join her all the tables you need to retrieve the information>
  where
                                  C.Customerid in ( @acc_1 )
   and datediff ( day , <your extract date> , getdate() ) between  1 and @dd
UNION       
....
UNION
< information from the columns>
  from 
    < join her all the tables you need to retrieve the information>
  where
                                  C.Customerid in ( @acc_10 )
   and datediff ( day , <your extract date> , getdate() ) between  1 and @dd
       
6) Test all the queries by assigning values for the parameters and finish designing of the three reports. In my case, the weekly and monthly reports were displaying the same summary and detailed information but the time perspective was of course different.

7) Deploy the three reports on a sharepoint library and create a data-driven subscription for each of them.

8) The query for the weekly subscription may look like : select
Name + ' ' + ' - Weekly report for latest ' + convert( char(2),Weekly) + ' week(s) issued ' + convert( varchar(12), GETDATE(), 104) as Subject  , [Acc_1]  ,[Acc_2]  ,[Acc_3]  ,[Acc_4]  ,[Acc_5]  ,[Acc_6]  ,[Acc_7]  ,[Acc_8]
      ,[Acc_9]  ,[Acc_10]  ,[Email]  ,Weekly from SubscriptionTable where Weekly > 0

9) You may perhaps realize now that the table contains one row pr. customer. Name is a user friendly customer name used as part of the subject mail, email contains the customer email adresses and weekly serves as the number of weeks covering data for that particular customer.    

10) Find a user friendly way for the business users in order to setup and maintain the solution and instruct them. It's often easier and more effective to produce a video clip for this purpose: http://youtu.be/ADoMp_SdGYo 
If you have other alternatives or even a way for a better scaling of the solution - when a customer can be related to more then 10 accounts - than do not hesitate to contact me. 

A MSSQL / SSRS / Sharepoint Integration solution

Scenario: A data change event in a table in a relational database - like sailing of a ship in my case - should trigger the delivery of a SSRS report in Sharepoint integrated mode as an e-mail attachment
to a certain list of recipients. 
Start: some t-sql code for the trigger of the table where the data change occurs.
End :  data or data-driven subscription to your report.
But how can we link the 'loose ends' together ? 
Hope the following guide and pieces of code may solve your problem:

1) The template for your trigger code it's perhaps the most trivial part and may look like :

CREATE TRIGGER <trigger_name>
   ON  <your table name>
   AFTER UPDATE
AS
IF UPDATE(<your column name>)
BEGIN

     SET NOCOUNT ON;

     DECLARE <variables> 
    
     SET <varibles> from Inserted, Deleted , etc.)
    
    < Your way to extract, transform and separate from her the parameters that you will use in the query for your data-driven subscription>
                             
       --- code that fires the Manifest subscription.
                                                           
END

2)      Create a shared schedule that will never fire, that is that executes once in the past.

3)      Create a subscription to your report. I used a data-driven subscription. The query of the subscription has to provide the values for your report parameters.
 Put down your subscription id.
You have to fire now a TimedSubscription event for your Reporting Services instance and you can accomplish it in two different ways :

4) By calling a remote stored procedure on the database server that hosts the ReportServer db :
       exec   <remote db server>.ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=<your subscription id from pct. 3>
Remember when configuring the linked server that RPC and RPC out options are set to 'TRUE', your remote login account has permissions to execute the stored procedure from above
and that you solve potential collation conflict in your subscription query. 

5)  Write a .Net application that calls the FireEvent method on the report server SOAP API. In the FireEvent method set the first variable EventType to “TimedSubscription” and the second one to your Subscription ID.
Your app. may be a console application, so your final trigger statement may be: xp_cmdshell <your exe .net application>. 
Make sure you grant the account that runs this process permission to “Generate events” System task.
                                                          
6) The idea in your .net app. is adding a web reference to the 'right' Reporting Services web service. If SSRS 2008 is in Sharepoint Integrated mode, than your web service url may look like:
http://<web site>/_vti_bin/ReportServer/ReportService2006.asmx
Some 'basic' code to start with in your C# main function :
 static void Main(string[] args)
        {
           
            <your web service reference>.ReportingService2006 rs = new <your web service reference>.ReportingService2006();
            rs.Url = "http://intranet.dk.dfds.root/_vti_bin/ReportServer/ReportService2006.asmx";
            rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
            string site = <your site>;
            // Get the subscriptions
            <your web reference>.Subscription[] subs = rs.ListMySubscriptions(site);
           //     rs.ListAllSubscriptions(site);
            try
            {
                if (subs != null)
                {
                    // Fire the first subscription in the list
                    rs.FireEvent("TimedSubscription",
                        subs[0].SubscriptionID, site);  
                    Console.WriteLine("Event fired.");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

        }
    }
}

I implemented the first option, as described at 4).
If you have other alternatives to solve this problem, than do not hesitate to contact me. 

Stumbling on 'Report Server has encountered a Sharepoint error'

Scenario: A Sharepoint 2007 or 2010 environment. You copy or move a Reporting Services report - based on a shared data source found on a dedicated Data Sources library -
from one library to another or even to another folder within the same library.

Result: Opening the report fails with the error:
Report Server has encountered a Sharepoint error ( rsSharepointError ).
The same error if you look at the Data Sources or Manage Parameters: Report Server has encountered a Sharepoint error.

Changing the report name and refreshing the page will not help.
But here is a way that may solve your problem :

1) Select Open in Report Builder. You may get the error :
"The Report Definition was retrieved from the server but one or more errors occurred while retrieving report properties".
If you click on Details button a new generical error message pops up:
"An error occurred when retrieving report parameters for report ....." . Click on OK and ignore the message so far.

2) Run the report in Report Builder. You may set the credentials to report's data sources in order to make it work.

3) Save the report to your sharepoint library. (The library where you opened the report in step 1). But the item still exists and if you accept replacing it than it will fail with the same error message : "Report Server has encountered a Sharepoint error".  

4) Delete the report item from the list then save the report.

5) Select the report item from the sharepoint library. Select your report Data Sources. If saving the report caused a custom data source then replace it with the original shared Data Source. Launching the report will succeed.

But looking back at all those steps, it may make sense not to copy or move the report. Open it in Report Builder and save it to the target sharepoint library.       

But one question still remains : Why all those steps for a basic copy or move task ?

Data and a box of matches

The way we think IT is both inspired and explained from our normal daily life.

You may perhaps still remember that inheritance in OOP is introduced in most of the books by an association with 'genetic' inheritance in the animal world, polymorphism with transportation by vehicles operated differently, etc.

A common pattern you often encounter is to retrieve some data. The retrieved data is used for ongoing lookup tasks, normally based on an input key.

At a conceptual level there are perhaps two ways to accomplish it :

1. Access the underlying data each time the lookup operation is needed: directly or in a SOA like manner by calling the service layer on top of your data.
2. Retrieve and cache all your lookup data in a hashtable data structure. If you are dealing with 'slow changing' data, the latency and refreshing policies are acceptable, then this might be your choice. You will gain a better performance, reduce locking and even cost of usage if your data provider charges your 'data trip' as perhaps is the case for your mainframe platform.

According to my experience option 1 is mostly used and often abused.

But what do you do every time you turn your gas oven on ?
 

1. You rush to the grocery store and buy exactly one match. Then 'same procedure as....'  
2. You already have a box of matches in your kitchen and just pick up a match out of it. No trip, no extra cost.

 Does this 'parallel' make any sense and can you give it a thought ?

You as BI and .Net Developer

As a BI consultant on a Microsoft based platform, you must have a profound knowledge or experience as a .net developer.

There at least two main reasons for it :

A business application is required by the business and often a .net client sounds like the best choice. That was the case with a Forecast and Monitoring application in my case, as mentioned in a previous blog entry.

.Net coding is an integrated part of all of your BI project types and herby only a few examples :

• SSIS - Script task in VB.net or C#
• SSAS - Store Procedures for calculations or dynamic security
• SSRS - Extending reports with custom code - embedded  or external within an assembly.

Sometimes you may even face the option of developing a .net application not directly related to your magic BI world.

That was the case with my last windows service application, briefly explained her from a business point of view :

http://youtu.be/LIBtYVTLTn8

Lessons from the real DW/BI life

Based on my latest experience from the field, I'd like to mention a few things that are close to my heart.

This is not a thorough check list and I'm not going to plunge you into lots of details for each single issue.

Although your experience may be different, please give them a thought anyway and you're welcome to share your opinion.

 

 

Be driven by adding or enhancing the business value. This is easier said end done, but I've seen that often the focus is on technology.

The long-term vs. short-term goal of your DW/BI team regarding building of an enterprise information infrastructure. And for it's data modeling freaks : there are pros and cons regardless of your usage of an Inmon, Kimball or Data Vault approach or not. Sooner or later - just like in marriage - there is a chance that you will regret it.

The ETL part does not equal the whole DW/BI, but it's a very important part of your DW/BI solution. Although the ETL part is 'transparent'  for the users, I faced situations tendencing to emphasize the metadata part of the ETL or the 'bureaucracy' of data stewardship in stead of listening to the business users and deeply analyse their requirements.   

The Toolset: When it comes to the architecture and product selection for your DW/BI system choose one vendor. If you choose a mix, then there is a huge risk that you'll spend your time integrating the systems or platforms. Microsoft DW/BI platform delivers according to Gartner the best ROI and TOC, but you may feel free to choose IBM, Oracle, SAP or another platform  if you really have strong arguments for it.

The BI solution fails in targeting users of different profiles and the natural flow of information between them: from top level management to analysts and information workers and to fullfill both strategical, tactical and operational business.
I worked on solutions where the ad-hoc or selv-service BI was not taken into account and where the entire solution tried to fulfill the daily operational business insight. No implementation of a Strategy Map, a Six Sigma, a top 5 or bottom 4 culture or even a deep understaing of the notion of KPI or KPI objectives as the underlying pieces of the business perspectives.

Data Mining is still one of the most underestimated parts of the DW/BI solution and I do not have any explanation. According to a IDC study, data mining should be the fastest growing business intelligence segment surpassing any other BI field. Unfortunately I can not recognize this trend. Customers prefer to explorer the yesterday's figures and not to discover patterns of data or predict the metrics with a huge potential to improve their business for a low cost.    
    
Heppy New DW/BI Year.

Compare Cube space in an indirect way

A couple of weeks before, as we migrated our BI solution to different environments, an important task was to ensure that everything was in sync.
This makes sense as we run separate ETL flows on a daily basis in each environment, that in turn pulls out data from their own system of records, not to mention that the SSAS databases were transferred out of our control.
How can we check that both schema and the cube space for the SSAS databases is the same ?
I was not aware of any tool that does this job. On the other side, there are tools that accomplish the task for a relation database.
So, what should do the trick : to compare schema and data for the underlying databases and compare the SSAS XMLA schemas.

I used my Visual Studio Studio 2008 Team Database Edition GDR for the first task
When started, I saw duplicate dropdown items appear under Data menu. I proceeded any way but any attempt failed with an error message like:   
"The operation is not supported within this release."
A schema or data compare between two SQL 2008 R2 databases not supported in a Visual Studio 2008 SP1 release ?
I realized that the incompatibily issue sounds misleading and that the 'error' is related to the duplicate menues.
I was afraid of the idea to repair / uninstall / install the products in the 'right' order but the following steps solved my problem :

• I closed all instances of Visual Studio Team System 2008 editions.
• At the Windows Command Prompt, typed the following command:
• %ProgramFiles%\Microsoft Visual Studio 9.0\DBPro\DBProRepair.exe RemoveDBPro2008 and pressed Enter
• At the Windows Command Prompt, typed the following command: %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe   

A word of caution here: data compare is naturally based on a primary key constraint for the corresponding tables. If your fact tables do not have a primary key constraint, then you have to figure out another way in order to compare their data.

For the SSAS part, I used the BIDS helper. I strongly recommand this free add-on to BIDS and you can download it from: http://bidshelper.codeplex.com/
I scripted first out my SSAS databases in SSMS by right click -> script database as --> create to --> and saved the XMLA files
Then I used BIDS helper "smart diff" to compare the XMLA files and tracked the differences that may influence the final result from a user perspective..

Writeback bug or feature in SSAS 2008 R2

I wrote on my last project a .net desktop application used for forecast and

monitoring in contact centers as reflected in the blog entry 'Cube writeback with multiple values in one shot'.

I won't reveal all the details of this application but it is based on OWC on a windows form, cube actions intercepted in events fired when user invokes a context menu and finally writing the changes to the cube.

You may recall that cube changes are not written directly to cube cells but to a relational table

called writeback table.

    

The writeback table in my case has columns for all measures in the measure group

- [OfferedCalls_0], [ForecastedOfferedCalls_1], [AdvisorsAbsent_2] ,

 [Distribution_4], [EmployeesPlanned_5], [CallsPerAdvisorDistribution_6] -,

the interecting dimensions :

[SKTime_7] foreign key which points to a Time dimension covering all the 96 quarters of the hours during a day ,

[SKCallFlowDepartment_8] foreign key which points to an organisational dimension  

[SKDate_9] foreign key pointing to the Date Dimension

and two additional columns for auditing purposes - MS_AUDIT_TIME_10] ,  [MS_AUDIT_USER_11] .

The table looks like :

CREATE TABLE [dbo].[WriteTable_Forecast Measures](

 [OfferedCalls_0] [int] NULL,

 [ForecastedOfferedCalls_1] [int] NULL,

 [AdvisorsAbsent_2] [int] NULL,

 [Support_3] [int] NULL,

 [Distribution_4] [float] NULL,

 [EmployeesPlanned_5] [int] NULL,

 [CallsPerAdvisorDistribution_6] [float] NULL,

 [SKTime_7] [bigint] NULL,

 [SKCallFlowDepartment_8] [bigint] NULL,

 [SKDate_9] [bigint] NULL,

 [MS_AUDIT_TIME_10] [datetime] NULL,

 [MS_AUDIT_USER_11] [nvarchar](255) NULL

) ON [fgCurrent]

What it really happens is that the delta changes written to the writeback table inserts a row with a null value for the [SKTime_7] column and this is certainly neither expected or desired.

Cube processing with the default settings fails due to the null key. If you ignore the errors and succeed in processing the cube, the numbers the users entered once are different then the ones displayed by the client, so you have a very serious problem.

You have no influence of this process: playing with the mdx update cube ... statement, changing among the four allocation options ( USE_EQUAL_ALLOCATION is the default option) or

trying to enforce the foreing key constrains will not help you.

So I had to find a work-around based on the following steps : 

• Retrieve the row with the null key in a temporary table with a select into statement.
• Update the writeback table by adding the values retrieved from the temporary table to the values of the corresponding measure for a particular row generated by the client.
• Delete the 'orphan' row.

 as implemented in the bellow script .

select ForecastedOfferedCalls_1 , SKCallFlowDepartment_8 , SKDate_9 , SKTime_7, MS_AUDIT_TIME_10 , MS_AUDIT_USER_11
into #Write_Temp   from dbo.[WriteTable_Forecast Measures]
where SKTime_7 is null

update  dbo.[WriteTable_Forecast Measures]
set ForecastedOfferedCalls_1 = a.ForecastedOfferedCalls_1 + b.ForecastedOfferedCalls_1
from dbo.[WriteTable_Forecast Measures] a, #Write_temp b
where a.SKDate_9 = b.SKDate_9
and a.SKCallFlowDepartment_8 = b.SKCallFlowDepartment_8
and a.MS_AUDIT_TIME_10 = b.MS_AUDIT_TIME_10
and a.MS_AUDIT_USER_11 = b.MS_AUDIT_USER_11
and a.SKTime_7 in ( select SKTime from dim.LocalTimeOfDay
where MNQuarterInterval = '00:00-00:15' )

delete dbo.[WriteTable_Forecast Measures]
where SKTime_7 is null

This seems to solve the problem. But I did not encounter this issue when running the application in the previous SSAS release and this makes me think of the never ending dilemma : bug or feature of the latest SSAS release. This is the question...

 

SCD Type 1 , 2, 3 or 1 + 2 + 3

Question: When you define a SCD (slow changing dimension) as type 1, 2 or 3 ?

I experienced some confusion during the past, so that is why I'd like to put the 'selection' process into perspective, although the discussion may seem a little bit theoretical:
For each SCD dimension :

• Define the business key, the key that identifies your identity, as for instance the Social Security Number ( US) or Cpr. nr (DK) for a customer dimension.
• Pick-up each attribute you have to track changes over time. Do not include her the business key, the surrogate key, and focus only to the attributes that describes, in other words add pieces of information to your business key. ( Addresses, birth day, number of children, marital status , etc. for a customer.)
• Start by diving the attributes you idendified from above in two categories first : attributes where you are not interested at all in tracking the changes (birth date for instance), let' call it category 1 and attributes where you are interested in tracking the changes ( name, adresses, ) , as category 2
• For the attributes in category 2, make a clear decision from the start whether:
• when an attribute value changes you are only interested in the last value. That is an in place override of the previous value and no posibility to track the historical changes. Such an attribute is a type 1 attribute and might be a name or address.
• if you have to keep the historical chages, then you have to choose between two practical ways to implement it :
• type 2 - the most common . You may add either ( StartDate, EndDate) or IsCurrent or even both of them.
• type 3 - less common

Keep in mind that the business requirements decide whether your attribute falls into one category or type.

You will often end with a dimension having attributes of category 1 and category 2 and the latter having both type 1 and type 2 and in rarely cases a mix of type 1, 2 and 3.

So the answer to the original question may sound like:

If you keep all the attributes in your dimension, and your dimension has at least one type 2 attribute , than people call it a SCD type 2 dimension.

    

But what about if you have at least one type 2 and at least one type 3 attribute ?       

It's both a type 2 and type 3 and often not to mention type 1.

In that case, by only calling it type 2, you may hide the representation of the two other types of attributes.

So why not call it : 1 + 2 + 3 ......

We all live in a suspect DB world

I have Reporting Services 2008 R2 developer edition installed on my laptop.
As I tried to connect this morning to my reporting services url : http://w30141:8080/ReportServer_W30141 , I received the following error message :

Reporting Services Error

---

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) Get Online Help
Cannot open database "ReportServer$W30141" requested by the login. The login failed. Login failed for user 'NTXXXX\YYYYYY'.  ( I won't disclose my domain login account even on my blog)

---

I checked the service and it was up and running but the reporting service database ReportServer$W30141 was in suspect mode. You can not access a database in suspect mode and the option of making a backup or restore of this database was disabled as well. So how can we handle this situation ?

We all remember that SQL Server 2005 introduced a new DB Status called Emergency. This mode can change the DB from Suspect to Emergency mode, so that you can retrieve the data in read only mode.
Emergency repair mode it's a one-way operation. Anything it does cannot be rolled back or undone.
As it's a one-way operation, you cannot wrap it in an explicit user-transaction.
You may use the option REPAIR_ALLOW_DATA_LOSS to ensure the database is returned to a structurally and transitionally consistent state and this is actually the only repair option available in emergency mode.  You may be tempted to use REPAIR_REBUILD, but it won't work.
So I used the following script :

EXEC sp_resetstatus 'ReportServer$W30141'
ALTER DATABASE ReportServer$W30141 SET EMERGENCY
DBCC checkdb('ReportServer$W30141')
ALTER DATABASE ReportServer$W30141 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('ReportServer$W30141', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE ReportServer$W30141 SET MULTI_USER

I did not find any data loss as far as I could see, as I could access and run all my reports hosted in this database.

So if you encounter in the future a database in suspect mode, use the above script. It worked for me once again for the AdventureWorks 2008 database. 

A new approach of building a dimensional data model

I was once presented an 'untraditional' approach of building a dimensional data model and I'll try to briefly introduce it to you.
I think it's easier to exemplify it with just a fact table Fact1 and a Date dimension but imagine the 'extension' applying for all your dimensions and fact tables.

1) Let's say you have a Date table with just one column [datetime] type [datetime] ( clustered index on it ) and a fact table Fact1 with two columns a DateKey and Measure1.

2) You create a Keys table with a statement like :
CREATE TABLE [dbo].[Keys](
[sk_key] [int]
IDENTITY(1,1) NOT NULL,
[Dimension] [nvarchar](50) NOT NULL,
[Attribute] [nvarchar] (50) NOT NULL,
[AttributeValue] [nvarchar] (250) NULL,
[ValidFrom] [int] NULL,
[ValidTo] [int] NULL
) ON [PRIMARY]

You create a clustered index covering all columns and an nonclusted index on sk_key.
The Keys table will contain all the attributes and their values for all dimensions and a track of their changes in order to support evt. SCD type2 dimensions.
You find a way - by writing a stored procedure -  to populate the keys table for all your dimensions and adapt your ETL flows to 'target'  this table.

3) Your dimensions will be based on views which basically join the 'real' table with the keys table or are entirely based on the 'real' table.
For the Date dimension the view may look like :

CREATE VIEW [dbo].[DateView] AS
SELECT cast (convert(nvarchar, [datetime], 112) as int) as DateKey,
convert (nvarchar, [datetime], 112) as DateName,
case when DATEPART(D, [datetime]) < 10 then '0' + DATENAME(D, [datetime])
else DATENAME(D, [datetime])
end
as DateShortName,
etc. , etc, etc......
from
dbo.Date
but for an Organisation dimension the statement may look like :
CREATE
view [dbo].[EmployeeView] as
select k1.sk_key as EmployeeKey, d.EmployeeName, d.EmployeeBnr, d.EmployeeTitle, k2.sk_key as EmployeeGroupKey, EmployeeGroup
from Employee d
inner join Keys k1
on d.EmployeeBnr=rtrim(k1.AttributeValue)
inner join Keys k2
on d.EmployeeGroup=rtrim(k2.AttributeValue)
where k1.Dimension='Employee' and k1.Attribute = 'EmployeeBnr'
and k1.Dimension = 'Employee' and k2.Attribute = 'EmployeeGroup'

4) You are almost done. You add all the views you created to the Data source view  - one for each 'dimension' table - DataView in our example- and the fact tables - Fact1 in our example - as well.

If you have any experience with this approach, balancing it's pros and cons, then I'd like to get
your feedback.

Parameterized SSRS reports sourced from OLAP cube with a dimension having set a default member

Imagine you develop SSRS 2008 reports based on SSAS olap cubes and face the following situation:
a default member on a dimension, let's say the Date dimension.
The report uses a parameter and it's default member is based on it ( typically the last date where the system has a non empty measure).
User picks up the default member for the date dim. and another date - in order to see the aggregated results - but only the results for the default member are displayed.

The relevant part of the query - I do take into account what the designer generates - looks like :
 SELECT NON EMPTY { [Measures.[...] } ON COLUMNS, NON EMPTY
{... } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM FROM ( SELECT ( STRTOSET(@LocalDateCalendar, CONSTRAINED) ) ON COLUMNS FROM [MISDBCC]))
WHERE ( IIF( STRTOSET(@LocalDateCalendar, CONSTRAINED).Count = 1, STRTOSET(@LocalDateCalendar, CONSTRAINED), [Local Date].[Calendar].currentmember )  ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
( I omitted other parameters , etc. )

The query includes both days but the cube retrieves only the results for the default member.
When I eliminate the default member on the dimension the results are right.
My conclusion was : There is a cube// dim. bug and I worked on the following work-around :

Delete the default member for the dimension, create different roles and create the default member only for one role, as I still need it for some reports.
Then use different connections for the reports data sources depending on the roles (Roles= directive).
And last but not least at the report level I created data sets for default member for the Date parameter involved , based on the previous logic for defining the default member at dimension level. ( Tail(Exists (...) construction .

If you were struggling with this kind of issue before or just like to reproduce and investigate it, than I'd like to get your feedback.

Navigate and select from cascading parameters SSRS reports

We all need to share information. That is why we upload strategic reports to Sharepoint to quickly disseminate insightful information across the enterprise.
We implement digital web part pages that display cascading parameters SSRS reports.
But do all users know how to navigate , select in the drop-down list boxes or even print the reports ?
Hope that the following video will help the users:
http://www.youtube.com/watch?v=8wU-kQBER94

Print a report from a Sharepoint Web application

Some of my end users are interested in printing reports from Sharepoint.
I recomended them to use the following step-by-step approach :

• Click on Report Library on the breadcrumb navigation
• Select the report item you want to print from the report library
• Select Actions -> Export on the status line and choose Acrobat( PDF) or Word or another save format.
• Choose Open or Save and use the print features that the program you saved the report provides.

The technical users may perhaps have a look at the Microsoft site :
http://technet.microsoft.com/en-us/library/bb326212(SQL.90).aspx 

Some parameter names not allowed in SSRS 2008 R2

I was suprized to realize that some previous well functioning SSRS reports failed when 'previewed' .
The error sound like : "An error occurred during local report processing. An error has occurred during report processing.Query execution failed for dataset '****'. Parser: The syntax for 'Date' is incorrect.

But the report worked once and as far as I could see nothing has changed behind time. So how can it be ?
It took me a while to realize, that all of a sudden it does not like the parameter name 'Date' , because the syntax around it has never changed. It looked like parameter names like @Date and @Application were not allowed any more. They were treated as reserved keywords.

So the solution was just dummy replacing them - by @Date1 , @Application1 or the like  - in query , parameter list and all the way around where they showed up.
Then I could preview the reports like I did it once.
If you also experienced this or have an idea of the root cause of this issue, please leave me a note.