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.