Business Intelligence with SQL Server & ME

Setup Date Dimension

2011-08-07T08:42:00.001-07:00

You cannot think of business intelligence without date dimension and it is so much so that all our measures mostly spins around date dimension for date/time slicing, YTD, MTD, Month to Month comparison and goes on. In my current assignment I had to build date dimension and that’s when I thought of checking with the BI core group in my company. I got plenty of replies from the group. However I was looking for something which is very easy to understand and implement and thinking so I built one date dimension. Note that in my post I have not kept attributes for fiscal year but if you need to, you would merely have to add bunch of attributes to store fiscal month, quarter etc.

I am straight away jumping into the script. We need 4 scripts as explained below -

Date dimension table. Name of the table is [OS_Date_Dimension].
Stored procedure to populate date dimension table.
Script to populate your fact with UNKNOWN time member if lookup does not find one key in the date dimension.
Call stored procedure to fill date table.

Date dimension table. Name of the table is [OS_Date_Dimension]

CREATE TABLE [dbo].[Os_Dim_Date](
    --Keys
    [DateKey] [int] PRIMARY KEY CLUSTERED NOT NULL,    --20110101
    [AlternateDateKey] [nvarchar](10) NOT NULL,        --2001-07-01
    [CalenderDate] [datetime] NOT NULL,                --2000-01-01 00:00:00.000
    
    --Day
    [DayOfWeek] [nvarchar](9) NOT NULL,                --Sunday
    [ShortDayOfWeek] [nvarchar](3) NOT NULL,        --Sun
    [DayNumOfYear] [int] NOT NULL,                    --1, 2, 3..365
    [DayNumOfQuarter] [int] NOT NULL,                --1, 2, 3..90
    [DayNumOfMonth] [int] NOT NULL,                    --1, 2, 3..31
    [DayNumOfWeek] [int] NOT NULL,                    --1, 2, 3..7
    
    --Week
    [WeekName] [nvarchar](20) NOT NULL,                --2000-Week-1
    [WeekNumOfYear] [int] NOT NULL,                    --1, 2, 3..49
    [WeekNumOfQuarter] [int] NOT NULL,                --1, 2, 3..14
    [WeekNumOfMonth] [int] NOT NULL,                --1, 2, 3..6
    
    --Month
    [MonthName] [nvarchar](20) NOT NULL,            --January 2001
    [MonthShortName] [nvarchar](6) NOT NULL,        --Jan
    [MonthNumOfYear] [int] NOT NULL,                --1, 2..12
    [MonthNumOfQuarter] [int] NOT NULL,                --1, 2 and 3
    
    --Quarter
    [QuarterName] [nvarchar](10) NOT NULL,            --2000 Q1
    [QuarterShortName] [nvarchar](5) NOT NULL,        --Q1
    [Quarter] [int] NOT NULL,                        --1,2,3 and 4
        
    --Year
    [YearName] [nvarchar](7) NOT NULL,                --2011
    [Year] [int] NOT NULL,                            --2011
        
    --Additional
    [SemiMonth] [nvarchar](50) NULL,                --SemiMonthP1 January 2011
    [Semester] [int] NOT NULL,                        --1 and 2
    [SemesterName] [nvarchar](11) NOT NULL            --H1 CY 2001
)
GO

Stored procedure to populate date dimension table

--Stored procedure to create date dimension table
CREATE PROCEDURE [dbo].[Os_Populate_Date_Dim]
(
  @FIRST_DATE       DATETIME,
  @LAST_DATE        DATETIME
)
AS
BEGIN
    WITH TE_Os_Dim_Date as 
    ( 
    SELECT Cast (@FIRST_DATE as DateTime) Date        --Start Date 
    UNION ALL 
    SELECT Date + 1 FROM TE_Os_Dim_Date 
        WHERE Date + 1 < = @LAST_DATE                --End date 
    ) 
    INSERT INTO [dbo].[Os_Dim_Date] (
         [DateKey]
        ,[AlternateDateKey]
        ,[CalenderDate]
        ,[DayOfWeek]
        ,[ShortDayOfWeek]
        ,[DayNumOfYear]
        ,[DayNumOfQuarter]
        ,[DayNumOfMonth]
        ,[DayNumOfWeek]
        ,[WeekName]
        ,[WeekNumOfYear]
        ,[WeekNumOfQuarter]
        ,[WeekNumOfMonth]
        ,[MonthName]
        ,[MonthShortName]
        ,[MonthNumOfYear]
        ,[MonthNumOfQuarter]
        ,[QuarterName]
        ,[QuarterShortName]
        ,[Quarter]
        ,[YearName]
        ,[Year]
        ,[SemiMonth]
        ,[Semester]
        ,[SemesterName]
    )
    SELECT 
        CAST(CONVERT(NVARCHAR(30),DATE, 112) AS INT) AS DateKey
        ,LEFT(CAST(CONVERT(NVARCHAR(30),DATE, 120) AS VARCHAR), 10) AS AlternateDateKey
        ,DATE AS CalenderDate
        
        ,DateName (dw, date) as DayOfWeek
        ,LEFT (DateName (dw, date), 3) as ShortDayOfWeek
        ,DATEPART (DY, DATE) AS DayNumOfYear 
        ,DATEDIFF(DD,DATEADD(QQ,DATEDIFF(QQ,0,DATE),0),DATE)+1 AS DayNumOfQuarter 
        ,DAY (DATE) AS DayNumOfMonth 
        ,DATEPART (DW, DATE) AS DayNumOfWeek 
        
        ,'Week ' + CAST(DatePart (wk, Date) AS VARCHAR) + ' CY ' + convert(varchar(4),YEAR(Date)) AS WeekName
        ,DatePart (wk, Date) as WeekNumOfYear 
        ,datediff(wk,dateadd(qq,datediff(qq,0,date),0),date)+1 as WeekNumOfQuarter 
        ,datediff(wk,dateadd(mm,datediff(mm,0,date),0),date)+1 as WeekNumOfMonth 

        ,DateName (mm, date) + ' ' + datename(year,Date) as MonthName 
        ,LEFT ( DateName (mm, date), 3) MonthShortName 
        ,MONTH (date) as MonthNumOfYear 
        ,DateDiff(mm,DateAdd(qq,DateDiff(qq,0,date),0),date)+1 as MonthNumOfQuarter 
        
        ,'Q' + datename(quarter,Date) + ' CY ' + datename(year,Date) AS QuarterName
        ,'Q'+ datename(quarter,Date) AS QuarterShortName
        ,DatePart ( qq, date) as Quarter 
        
        ,datename(year,Date) AS YearName
        ,YEAR (date) as Year 
        
        ,'SemiMonthP' +  CASE WHEN Day(Date) <= 15 then '1' ELSE '2' END + ' ' +  DateName (mm, date) + ' ' +  datename(year,Date) AS SemiMonth
        ,CASE WHEN MONTH(Date) <= 6 then 1 ELSE 2 END AS Semester
        ,'H' + CAST(CASE WHEN MONTH(Date) <= 6 then 1 ELSE 2 END AS VARCHAR) + ' CY ' +  datename(year,Date) AS [SemesterName]
    FROM TE_Os_Dim_Date 
    
    OPTION (MAXRECURSION 0)
END
GO

Script to populate your fact with UNKNOWN time member if lookup does not find one key in the date dimension

--Insert a record for unknown lookup
INSERT INTO [dbo].[Os_Dim_Date] (
         [DateKey]
        ,[AlternateDateKey]
        ,[CalenderDate]
        ,[DayOfWeek]
        ,[ShortDayOfWeek]
        ,[DayNumOfYear]
        ,[DayNumOfQuarter]
        ,[DayNumOfMonth]
        ,[DayNumOfWeek]
        ,[WeekName]
        ,[WeekNumOfYear]
        ,[WeekNumOfQuarter]
        ,[WeekNumOfMonth]
        ,[MonthName]
        ,[MonthShortName]
        ,[MonthNumOfYear]
        ,[MonthNumOfQuarter]
        ,[QuarterName]
        ,[QuarterShortName]
        ,[Quarter]
        ,[YearName]
        ,[Year]
        ,[SemiMonth]
        ,[Semester]
        ,[SemesterName]
    )
VALUES
    (
         19000101
        ,'1900-01-01' 
        ,'1900-01-01 00:00:00'
        ,'Unknown' ,'Unk', 1, 1 ,1 ,1
        ,'Unknown', 1 ,1 ,1
        ,'Unknown', 'Unk' ,1 ,1
        ,'Unkown', 'Q1', 1
        ,'1900', 1900
        ,'Unknown', 1
        ,'Unknown'
    )
GO

Call stored procedure to fill date table

--Populate Date from 2001 to 2020
EXEC [dbo].[Os_Populate_Date_Dim] '2001-01-01', '2020-12-31'
GO

Now let’s setup dimension attribute key, name and hierarchies in the cube. Your Dimension structure (Hierarchies) and attributes relationship should look like this at the end of the exercise.

Set attribute key and names as illustrated below -

Attribute Name	KeyColumns Property	NameColumn Property
DateKey	Os_Dim_Date.DateKey (Integer)	Os_Dim_Date.CalenderDate (WChar) ValueColumn: Os_Dim_Date.CalenderDate (WChar)
Month Name	Os_Dim_Date.Year (Integer) Os_Dim_Date.MonthNumOfYear (Integer)	Os_Dim_Date.MonthName (WChar)
Quarter	Os_Dim_Date.Year (Integer) Os_Dim_Date.Quarter (Integer)	Os_Dim_Date.QuarterName (WChar)
Year	Os_Dim_Date.Year (Integer)	Os_Dim_Date.YearName (WChar)
Week Name	Os_Dim_Date.MonthName (WChar) Os_Dim_Date.WeekNumOfMonth (Integer)	Os_Dim_Date.WeekName (WChar)

Next process dimension and browse Date and Week Hier. Find below the illustrations.

You may need more attributes related to fiscal year, holidays etc. which you should be able to add easily by extending table and stored procedure. Hope this helps.

Reports using Cube or Relational Database..some points to consider

2011-06-16T08:46:00.001-07:00

We should try and use cube and not relational database for building SSRS reports. Here are the some of the advantages that cube has over relational database –

Cube is much faster source for canned and ad-hoc reporting. Reason, aggregated and processed data is stored in cube database (of course if implemented using MOLAP).
Information or the measures are organized in a way which is easy to build excel pivot table which business user can easily understand.
It is very easy to implement hierarchy and drill down using cube and MDX compared with Relational database and SQL queries.
With cube we can use power of MDX to build complex reports, like YTD, Running total, Semi additive measure, trend, parallel period etc.
Calculated measures can be stored in the cube and can be used by developer to build report or business user browsing them directly using any client application or excel. Other hand we never store calculated column in the relational database table.
Even ad-hoc reporting becomes handy and easy with cube as some of the client software like ProClarity only work with cube.
Using cube we can easily get different view of a measure using linked dimensions. Say spend by vendor, location etc.
However not all the report should be build using the cube. For example, detail reports where we want to view detail data as they were pulled from the source system, we should use SQL report. In this case we are not making use of aggregated data from the cube and SQL query can returns the detail rows much master than MDX.

SSAS NULL value default behavior

2011-06-06T16:06:00.001-07:00

One of my direct reports asked me last week about how SSAS handles NULL measure if present in the fact table. I think he encountered this in one of his business intelligence job interview :).

I thought let’s write a small post about the topic and this way others will also be get benefited plus I don’t have to explain it to him separately.

I have a sales fact table which is linked to product, measure type, organization and time dimensions. Here is part of the fact table -

As you see we have got plenty of NULL value under plan and forecast measures. We will stick to Actual and Plan measures for our discussion. Let’s browse the cube and put this in perspective for you to see how SSAS treat NULL measure value. Any guess at this point?

I have filter products with PRODUCT_SK in (48, 55 and 56) where we have NULL under plan but has got some Actual sales value. This is bit strange with SSAS which replaces NULL with 0 by default, meaning does not preserve NULL. However, this may mislead the business and you may want to preserve NULL for all intense purposes. In this case it is just that business has not decided on the plan, forecast etc for certain products does not necessarily mean that plan amount is 0.

Lets get straight to the SSAS measure properties and there is a way to preserver NULL value. Go to the cube solution, select you measure and change NullProcessing property under Source from Automatic to Preserve. Next process the cube and reconnect to OLAP db to refresh.

Here is what I mean. We don’t see 0 anymore. You should carefully decide whether or not you want to keep NULL and make use of this property.

Please leave any feedback or suggestions you have!

NON EMPTY vs NONEMPTY(<set>, <set>)

2011-06-06T12:20:00.001-07:00

To remove NULL values from the query output we use NON EMPTY or NONEMPTY functions. There is small difference between NON EMPTY and NONEMPTY function, I am providing difference with examples through this post.

Let’s run following MDX query -

SELECT 
{
    [Measures].[INV PROD SALES ACTUAL],
    [Measures].[LEAD TO CALL PRODUCT ACTUAL]} ON COLUMNS,
{
    [MEASURE TYPE].[MEASURE TYPE].Children    
} ON ROWS
FROM [YOUR CUBE]

Result of the above query. As you see there are plenty of NULL values we got for the MEASURE TYPE set.

Now let’s use NON EMPTY on the set, here is the changed query -

SELECT 
{
    [Measures].[INV PROD SALES ACTUAL],
    [Measures].[LEAD TO CALL PRODUCT ACTUAL]} ON COLUMNS,
NON EMPTY
{
    [MEASURE TYPE].[MEASURE TYPE].Children    
} ON ROWS
FROM [YOUR CUBE]

Here is the output with NON EMPTY -

As you see now wherever we had NULL for both the measure those measure types have been filtered (meaning removed). So NON EMPTY works on the overall result, meaning after set get first evaluated, all the tuples with NULL values for all the measures, are removed.

On the other hand NONEMPTY() takes 2 sets as parameters. Its uses tuples of the first set and filtered out all empty tuples based on the cross product with second set. Meaning behind the scene what happens is, NONEMPTY is evaluated when the set defining the rows axis is evaluated. Let’s analyze the query -

SELECT 
NON EMPTY
{
    [Measures].[INV PROD SALES ACTUAL],
    [Measures].[LEAD TO CALL PRODUCT ACTUAL]} ON COLUMNS,
{
    NONEMPTY([MEASURE TYPE].[MEASURE TYPE].Children,
    [Measures].[LEAD TO CALL PRODUCT ACTUAL])    
} ON ROWS
FROM [YOUR CUBE]

Before we get into the result, let’s analyze the above query. As we discussed above NONEMPTY will be evaluated with LEAD TO CALL PRODUCT ACTUAL measure in the ROW axis first and will bring all the MEASURE TYPE tuples wherever we have value for LEAD TO CALL PRODUCT ACTUAL measure. Also note that if we don’t pass any measure in the NONEMPTY it takes default measure and filter non empty value for the set. From the first query output I am graying out the cells that we don’t expect in the query result.

Here is the above query output.

NONEMPTY should be used to get rid of empty tuples in the set for specified measure and it helps improve query performance. Specially when you cross join the dimension(s) members you should always consider NONEMPTY. Leave your comments..see you back soon!

SCD Type – 2 using MERGE and Output $action

2011-06-03T14:28:00.001-07:00

In this article we will talk about achieving SCD behavior using Sql MERGE statement. MERGE join works well based on join condition with source table and through available condition you can have INSERT, UPDATE or DELETE actions. Primarily what it does is that it compare key value of target with source table and perform different operations specified like WHEN MATCHED, WHEN NOT MATCHED etc.

The main focus of this post is to make use of $action virtual output column when we execute MERGE sql and decide as in whether INSERT or UPDATE has been performed on the source.

Let get to the business area and see these in action.

Create a table first and populate some records, or you can use the same table from my previous post.
http://takeatourtobi.blogspot.com/2011/06/delete-and-truncate-behavior.html

--Create student table
CREATE TABLE [DBO].[TTB_STUDENT](
    ID INT,
    NAME VARCHAR(100) NULL,
    AGE INT NULL,
    ACTIVE CHAR(1)
)

--Insert some records
INSERT INTO [DBO].[TTB_STUDENT] VALUES
    (1, 'Mark', 32, 'Y'),
    (2, 'Elaine', 31, 'Y'),
    (3, 'Elton', 33, 'Y'),
    (4, 'John', 40, 'Y'),
    (5, 'Smith', 30, 'Y')

Select student table. This is our target table -

Create another table which will have the delta which we have to apply on student table. Note that we will be using ID column value as key to decide whether or not student already exists. Here is the delta table script and rows..

CREATE TABLE [DBO].[TTB_STG_STUDENT](
    ID INT PRIMARY KEY,
    NAME VARCHAR(100) NULL,
    AGE INT NULL
)

INSERT INTO [DBO].[TTB_STG_STUDENT] VALUES
    (1, 'Mark Luther', 42),
    (2, 'Elaine Wang', 41),
    (6, 'Elton B', 22)

Since we are doing SCD Type – 2, this is what will be the scenarios -

If get a new student (New ID value), we will insert into the target table and set Active = ‘Y’
If get an existing student, we will insert it to the target table with Active = ‘Y’ and set Active = ‘N’ for all other records of that student. Meaning, at a any given point in time, we should have only one active record for each student.

As said earlier idea here is to make use of OUTPUT $action to retrieve the records that got updated and once we have this list, we can insert it finally and set flag to ‘N’ for all other records for a student.

Here is the final SQL statement and target table output.

INSERT INTO [DBO].[TTB_STUDENT]    
SELECT ID, NAME, AGE, 'Y' FROM
(
    MERGE [DBO].[TTB_STUDENT] AS TRG
        USING [DBO].[TTB_STG_STUDENT] AS SRC
            ON (SRC.ID = TRG.ID AND TRG.ACTIVE = 'Y')
        WHEN NOT MATCHED THEN 
            INSERT VALUES (SRC.ID, SRC.NAME, SRC.AGE, 'Y')
        WHEN MATCHED THEN 
            UPDATE SET TRG.ACTIVE = 'N'
        OUTPUT $action, SRC.ID, SRC.NAME, SRC.AGE

) AS DELTA (action, ID, Name, Age)
WHERE action = 'Update'

As you see, we had 2 existing students in the delta table for which active is set to ‘N’ and 2 new entries have been created in the table with Active = ‘Y’. There were one new student (ID = 6), which got inserted into the table. This way we can achieve SCD Type – 2 using MERGE SQL. Please leave your comments/feedback.

DELETE and TRUNCATE behavior

2011-06-03T08:26:00.001-07:00

We will create a student table then apply Delete and Truncate within Transaction and rollback. Leave your comment about the behavior and/or output you expect.

--Create student table
CREATE TABLE [DBO].[TTB_STUDENT](
ID INT IDENTITY(1, 1) PRIMARY KEY,
NAME VARCHAR(100) NULL,
AGE INT NULL,
ACTIVE CHAR(1))

--Insert some records
INSERT INTO [DBO].[TTB_STUDENT] VALUES
    ('Mark', 32, 'Y'),
    ('Elaine', 32, 'Y'),
    ('Elton', 32, 'Y'),
    ('John', 32, 'Y'),
    ('Smith', 32, 'Y')

--Delete records using DELETE 
--Rollback to bring back the records
--You can because it write to the log and
--can be rolled back
BEGIN TRANSACTION
DELETE [DBO].[TTB_STUDENT]
ROLLBACK 

--Select table
SELECT * FROM [DBO].[TTB_STUDENT]

Here is the output of the above select - 
 


--Do the same but use TRUNCATE TABLE
--Note that it does not write to log  by definition
BEGIN TRANSACTION
TRUNCATE TABLE [DBO].[TTB_STUDENT]
ROLLBACK 

--What do you expect ??
SELECT * FROM [DBO].[TTB_STUDENT]

Dynamic vs Static set in SSAS

2011-06-02T13:50:00.001-07:00

We deal with named set a lot when it comes to access multi dimensional data from the cube. There are two types of named set – Static and Dynamic. Till SQL Server 2005 we had only Static named set but now in SQL Server 2008 both are supported (static and dynamic named set).

You create named set either using CREATE SET (when defined in the cube) or WITH SET MDX (when defined in the MDX/Report) syntax. In case of static member, it get evaluated when the CREATE SET statement is executed or MDX is executed in case when it is defined in MDX using WITH SET. In case of static named set once it is executed it persists static during whole session or query or cube. In effect what will happen since named set is static and cached it will give same set of members even when you query with different condition and expect different result.

Assume a case where you need to get top 10 product by sales for whole period and then for a month. So after you run the query for whole period, you will end up getting same set of products for a month also when you execute next. So in such glitches we now have option to create dynamic set which works well.

I will explain what exactly I mean by all this.

Create a static set in the cube as under. In my example I am retrieving top 5 products based on sale.

CREATE SET CURRENTCUBE.[TOP PRODUCT SALES]
 AS TOPCOUNT(
        [INVESTMENT PRODUCT].[PRODUCT NAME].[PRODUCT NAME].MEMBERS,
        5,
        [MEASURES].[INV PROD SALES ACTUAL]) ;

Next, run following MDX to get top 5 product sales. Note that I am not using any time slicer here so what I am getting in here is across the time.

SELECT 
{
    [MEASURES].[INV PROD SALES ACTUAL]
} ON COLUMNS,
{
    NONEMPTY([TOP PRODUCT SALES], 
    [MEASURES].[INV PROD SALES ACTUAL])
} ON ROWS
FROM [YOUR_CUBE];

Here is output of above MDX.

Next let’s apply February time filter in the MDX and I expect at least 1-2 different product than one we got above.

SELECT 
{
    [MEASURES].[INV PROD SALES ACTUAL]
} ON COLUMNS,
{
    NONEMPTY([TOP PRODUCT SALES], 
    [MEASURES].[INV PROD SALES ACTUAL])
} ON ROWS
FROM [YOUR CUBE]
WHERE [TIME].[YQMD].[Month].&[2011-02-01T00:00:00]

Here is the output of above MDX - 
  
Note that though we now got different sales actual, it is the same set of products we earlier got when we ran without using any time filter. So what is happening here is when we created the set using CREATE SET statement in the cube and processed it, it evaluated the top 5 products and same set is being returned.

Now let’s change the set type to dynamic and retry the MDX and this time we expect it to be dynamic. Meaning, we should get different set of products based on top 5 sales when passed different time filter.

CREATE DYNAMIC SET CURRENTCUBE.[TOP PRODUCT SALES]
 AS TOPCOUNT(
        [INVESTMENT PRODUCT].[PRODUCT NAME].[PRODUCT NAME].MEMBERS,
        5,
        [MEASURES].[INV PROD SALES ACTUAL]) ;

Now run above 2 MDX queries and analyze the result.They should be different now.
SELECT 
{
    [MEASURES].[INV PROD SALES ACTUAL]
} ON COLUMNS,
{
    NONEMPTY([TOP PRODUCT SALES], 
    [MEASURES].[INV PROD SALES ACTUAL])
} ON ROWS
FROM [YOUR CUBE]



SELECT 
{
    [MEASURES].[INV PROD SALES ACTUAL]
} ON COLUMNS,
{
    NONEMPTY([TOP PRODUCT SALES], 
    [MEASURES].[INV PROD SALES ACTUAL])
} ON ROWS
FROM [YOUR CUBE]
WHERE [TIME].[YQMD].[Month].&[2011-02-01T00:00:00]

Now you would have noticed that we are getting different set of top 5 products and it is correct now and reflecting per sales. Please leave your feedback/comment.

Date dimension to support multiple fiscal year

2011-06-01T13:16:00.001-07:00

If you have requirement to present your data based on the fiscal year user is using, you can provide user with option to either select fiscal year he wants or can make it dynamic in the report such that it dynamically select the current user location and render the report.

This is one way to model your time dimension to support multiple fiscal year. There are obviously other ways to support various fiscal year like by adding more columns to time dimensions, storing into separate fact for each single fiscal year you may need to support, building separate hierarchies for each fiscal year etc. I insist the approach because you don’t have to add columns in the database time dimension table to support and of course it is not considered good to add columns to support piece of business. We should always try and see if it can be done using same table columns.

We will create 3 tables. Find script below to create these tables. We will create a calendar dimension table with all the attributes, a Date dimension table with key and date and a bridge table to connect these two dimension tables (Calendar and Date).

Here is the script to create 3 tables -

//Calendar dimension table
CREATE TABLE [DBO].[TTB_DIM_CALENDAR](
            [CALENDAR_KEY] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY,
            [CALENDAR_DATE_ID] [VARCHAR](100) NOT NULL,
            [CALENDAR_NAME] [VARCHAR](50) NOT NULL,
            [CALENDAR_ORDER] [TINYINT] NOT NULL,
            [ACTUAL_DATE] [DATE] NOT NULL,
            [DATE_NAME] [VARCHAR](50) NOT NULL,
            [YEAR] [DATE] NOT NULL,
            [YEAR_NAME] [CHAR](4) NOT NULL,
            [QUARTER] [DATE] NOT NULL,
            [QUARTER_NAME] [VARCHAR](9) NOT NULL,
            [MONTH] [DATE] NOT NULL,
            [MONTH_NAME] [VARCHAR](14) NOT NULL,
            [WEEK] [DATE] NOT NULL,
            [WEEK_NAME] [VARCHAR](12) NOT NULL,
            [DAY_OF_YEAR] [SMALLINT] NOT NULL,
            [DAY_OF_YEAR_NAME] [VARCHAR](7) NOT NULL,
            [DAY_OF_QUARTER] [TINYINT] NOT NULL,
            [DAY_OF_QUARTER_NAME] [VARCHAR](6) NOT NULL,
            [DAY_OF_WEEK] [TINYINT] NOT NULL,
            [DAY_OF_WEEK_NAME] [VARCHAR](5) NOT NULL,
            [WEEK_OF_YEAR] [TINYINT] NOT NULL,
            [WEEK_OF_YEAR_NAME] [VARCHAR](7) NOT NULL,
            [MONTH_OF_YEAR] [TINYINT] NOT NULL,
            [MONTH_OF_YEAR_NAME] [VARCHAR](36) NOT NULL,
            [MONTH_OF_QUARTER] [TINYINT] NOT NULL,
            [MONTH_OF_QUARTER_NAME] [VARCHAR](8) NOT NULL,
            [QUARTER_OF_YEAR] [TINYINT] NOT NULL,
            [QUARTER_OF_YEAR_NAME] [VARCHAR](9) NOT NULL,
)//Date dimension table
CREATE TABLE [DBO].[TTB_DIM_DATE](
        [DATE_KEY] [INT] NOT NULL PRIMARY KEY,
        [ACTUAL_DATE] [DATETIME] NOT NULL,
 )//Bridge fact table
CREATE TABLE [DBO].[TTB_DIM_DATE_CALENDAR](
            [DATE_KEY] [INT] NOT NULL REFERENCES 
[DBO].[TTB_DIM_DATE]([DATE_KEY]),
            [CALENDAR_KEY] [INT] NOT NULL REFERENCES 
[DBO].[TTB_DIM_CALENDAR]([CALENDAR_KEY])
)
GO

Let’s setup data source view in the OLAP. Add above 3 tables in the DSV, you should get this -

Next populate above tables using SQL script. Make sure you have entries for each single day in this table. You need to have entries per Gregorian calendar and same number of entries for each month of a year starting February (and of course for each year) . Reason, we are not creating entries for January because if fiscal starts from January it will be same as calendar year.

Here is the sample script to populate above tables.

DECLARE @STARTDATE DATETIME = '2008-01-01'
DECLARE @ENDDATE DATETIME = '2010-12-31'

DECLARE @COUNTER INT
SET @COUNTER = 1
;WITH DATES( D ) AS 
(  
     SELECT @STARTDATE AS D 
           UNION ALL 
     SELECT DATEADD(DAY, 1, D)  
           FROM DATES  
           WHERE D < @ENDDATE  
)
,MONTHS(M) AS
(
    SELECT @COUNTER AS M
          UNION ALL
    SELECT M + 1
          FROM MONTHS
          WHERE M <= 11
)

INSERT INTO TTB_DIM_CALENDAR (
CALENDAR_DATE_ID, CALENDAR_NAME, CALENDAR_ORDER, ACTUAL_DATE,
DATE_NAME, YEAR, YEAR_NAME, QUARTER, QUARTER_NAME, MONTH, 
MONTH_NAME, WEEK, WEEK_NAME, DAY_OF_YEAR, DAY_OF_YEAR_NAME,
 DAY_OF_QUARTER, DAY_OF_QUARTER_NAME, DAY_OF_WEEK, 
DAY_OF_WEEK_NAME, WEEK_OF_YEAR, WEEK_OF_YEAR_NAME, MONTH_OF_YEAR, 
MONTH_OF_YEAR_NAME, 
MONTH_OF_QUARTER, MONTH_OF_QUARTER_NAME, QUARTER_OF_YEAR, 
QUARTER_OF_YEAR_NAME)
SELECT
     CASE WHEN LEN(FISCALSTARTMONTH) = 1 THEN '0' ELSE '' END + 
CONVERT(VARCHAR(8), FISCALSTARTMONTH) + '_' + CALENDARKEY AS 
CALENDARDATEID
    ,CALENDARNAME
    ,FISCALSTARTMONTH
    ,ACTUALDATE
    ,[DATENAME]
    ,[YEAR]
    ,CONVERT(CHAR(4), DATEPART(YYYY, [YEAR])) AS YEARNAME
    ,DATEADD(Q, CEILING((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 
1)/3.0) - 1, [YEAR]) AS [QUARTER]
    ,CONVERT(CHAR(4), DATEPART(YYYY, [YEAR])) + ' Q' + CONVERT
(VARCHAR,CEILING((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 1)/3.0)) 
AS QUARTERNAME
    ,DATEADD(MM, DATEDIFF(MM, 0, ACTUALDATE), 0) AS [MONTH]
    ,CONVERT(VARCHAR, DATENAME(MM, ACTUALDATE)) + ' ' + CONVERT
(CHAR(4), DATEPART(YYYY, ACTUALDATE)) AS [MONTHNAME]
    --,(DATEDIFF(MM, [YEAR], ACTUALDATE) + 1) AS [MONTHNUMBER]
    ,DATEADD(WK, DATEDIFF(WK, 0, ACTUALDATE), 0) AS [WEEK]
    ,'WEEK ' + CONVERT(VARCHAR,(DATEDIFF(WK, [YEAR], ACTUALDATE) + 
1)) + ' ' + CONVERT(CHAR(4), DATEPART(YYYY, [YEAR])) AS [WEEKNAME]
    ,DATEDIFF(DY, [YEAR], ACTUALDATE) + 1 AS [DAYOFYEAR]
    ,'DAY ' + CONVERT(VARCHAR, DATEDIFF(DY, [YEAR], ACTUALDATE) + 
1) AS DAYOFYEARNAME
    ,DATEDIFF(DY, DATEADD(Q, CEILING((DATEDIFF(MONTH, [YEAR], 
ACTUALDATE) + 1)/3.0) - 1, [YEAR]), ACTUALDATE) + 1 AS DAYOFQUARTER
    ,'DAY ' + CONVERT(VARCHAR, DATEDIFF(DY, DATEADD(Q, CEILING
((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 1)/3.0) - 1, [YEAR]), 
ACTUALDATE) + 1) AS DAYOFQUARTERNAME

    ,DATEPART(DW, ACTUALDATE) AS [DAYOFWEEK]
    ,'DAY ' + CONVERT(VARCHAR, DATEPART(DW, ACTUALDATE)) AS 
DAYOFWEEKNAME
    ,DATEDIFF(WK, [YEAR], ACTUALDATE) + 1 AS WEEKOFYEAR
    ,'WEEK ' + CONVERT(VARCHAR, DATEDIFF(WK, [YEAR], ACTUALDATE) + 
1) AS WEEKOFYEARNAME
    ,DATEDIFF(MM, [YEAR], ACTUALDATE) + 1 AS MONTHOFYEAR
    ,'MONTH ' + CONVERT(VARCHAR, DATEDIFF(MM, [YEAR], ACTUALDATE) 
+ 1) AS MONTHOFYEARNAME
    ,DATEDIFF(MM, DATEADD(Q, CEILING((DATEDIFF(MONTH, [YEAR], 
ACTUALDATE) + 1)/3.0) - 1, [YEAR]), DATEADD(MM, DATEDIFF(MM, 0, 

ACTUALDATE), 0)) + 1 AS MONTHOFQUARTER
    ,'MONTH ' + CONVERT(VARCHAR, DATEDIFF(MM, DATEADD(Q, CEILING
((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 1)/3.0) - 1, [YEAR]), 
DATEADD(MM, DATEDIFF(MM, 0, ACTUALDATE), 0)) + 1) AS 
MONTHOFQUARTERNAME
    ,CEILING((DATEDIFF(MONTH, [YEAR], ACTUALDATE) + 1)/3.0) AS 
QUARTEROFYEAR
    ,'QUARTER ' + CONVERT(VARCHAR, CEILING((DATEDIFF(MONTH, 
[YEAR], ACTUALDATE) + 1)/3.0)) AS QUARTEROFYEARNAME

FROM 
(
    SELECT
           CONVERT(CHAR(8), D, 112) AS CALENDARKEY
          ,M AS FISCALSTARTMONTH
          ,CASE
                WHEN M = 1 THEN 'GREGORIAN'
                ELSE CONVERT(VARCHAR, DATENAME(MONTH, DATEADD
(MONTH, (M-1), 0))) 
+ ' BASED FISCAL'
           END AS CALENDARNAME
          ,D AS ACTUALDATE
          ,DATENAME(DW, D)+ ', ' + DATENAME(MM, D) + ' ' + RIGHT
('00' + CONVERT(VARCHAR(2), DATEPART(D, D)), 2) + ' ' + CONVERT
(CHAR(4), DATEPART(YYYY, D)) AS [DATENAME]
          ,CASE
                WHEN DATEPART(MONTH, D) >= M THEN DATEADD(MONTH, 
(M-1), DATEADD(YEAR, DATEDIFF(YEAR, 0, D), 0))
                WHEN DATEPART(MONTH, D) < M THEN DATEADD(MONTH, (M-
1)-12, DATEADD(YEAR, DATEDIFF(YEAR, 0, D), 0))

           END AS [YEAR]
    FROM
          DATES
          CROSS JOIN 
                MONTHS
) DATES

OPTION (MAXRECURSION 10000) 

;WITH DATES( D ) AS 
(  
     SELECT @STARTDATE AS D 
           UNION ALL 
     SELECT DATEADD(DAY, 1, D)  
           FROM DATES  
           WHERE D < @ENDDATE  
)

INSERT INTO TTB_DIM_DATE (DATE_KEY, ACTUAL_DATE)
SELECT
    CONVERT(INT, CONVERT(CHAR(8), D, 112)) AS DATEKEY,
    D AS ACTUALDATE
FROM
    DATES
OPTION (MAXRECURSION 10000)

INSERT INTO [TTB_DIM_DATE_CALENDAR] (DATE_KEY, CALENDAR_KEY)
SELECT
    D.DATE_KEY, C.CALENDAR_KEY
FROM
    TTB_DIM_DATE D
    INNER JOIN TTB_DIM_CALENDAR C ON
          D.DATE_KEY = CONVERT(INT, RIGHT(C.CALENDAR_DATE_ID, 8))

At this point if I assume you have just populated one year entries into the calendar dimension this is how number of entries should be in the calendar dimension table -

Add calendar and set dimension properties as under -

Set additional calendar attribute properties as under -

Next build hierarchy and set attribute relationship as under -

Add Date dimension and leave everything to default value. I however changed the key column to Date from Date_Key. Here is the snap of the Date dimension -

Now we will add a fact table (actual fact) for sales. Fact table will be linked to calendar and date dimensions.

CREATE TABLE [DBO].[TTB_FACT_SALES](
    [DATE_KEY] [INT] NOT NULL REFERENCES 
[DBO].[TTB_DIM_DATE]([DATE_KEY]),
    [CALENDAR_KEY] [INT] NOT NULL REFERENCES 
[DBO].[TTB_DIM_CALENDAR]([CALENDAR_KEY]),
    [SALES_AMOUNT] DECIMAL(10,2)
)

Add above fact table to data source view and it should look like this at this point -

Create a measure from the bridge table. This will act as intermediate fact table when we join with sales fact through calendar dimension (using many to many relationship and will be discussed later). It is nothing but the fact less fact table and we will not be using this for anything. Let’s add count measure on Date_key column. Here is what I mean -

Populate sales fact table as under. I just created for all the possible date and calendar key combination for us to be able to test with some data.

INSERT INTO [DBO].[TTB_FACT_SALES]([DATE_KEY], [CALENDAR_KEY], [SALES_AMOUNT]) 
SELECT DATE_KEY, CALENDAR_KEY, RIGHT(DATE_KEY, 2) FROM [DBO].[TTB_DIM_DATE_CALENDAR]

Next add sales measure to the cube and leave everything else to default. This is how cube structure should look like.

Set the dimension usage tab as under. Link DATE CALENDAR measure group with Calendar and Date dimension using Regular relationship type. This measure group we will be using when linking with actual fact which is sales in this case.

Link sales fact with Date dimension using Regular relationship type and with calendar dimension using many to many relationship type through bridge table measure group (above one). Find below dimension usage tab and many to many relationship snap for your to connect well.

Process the cube and analyze what we got in here. Drag sales in the measure area and calendar hierarchy. This is what we get, all the values are same for the Calendar name because we had set IsAggregatable = False.

What we gonna see next is interesting. In Gregorian calendar March falls in Q1 and in March based fiscal again it will fall in Q1 but in April based fiscal April will fall in Q1. Similarly in Gregorian calendar May falls in Q2 but in April based fiscal it will fall in Q1 etc. So what I am driving here is, sales value will be accrued to different quarter in a year based on what is the starting month of a fiscal.

Rest you browse the cube and check the sales value distribution. Hope this will help you design time dimension when you have to support different calendar, fiscal etc. Thank you and bye for now!

SSIS Lookup and Conditional Split tasks to load data incrementally

2011-05-27T10:48:00.001-07:00

Microsoft integration service slowly changing dimensions out of box task can be used to implement SCD Type 1 and SCD Type 2. The main challenge ETL developer faces is around selecting right approach in loading data incrementally. We have to consider the aspects like – amount of data, whether to implement Type – 1 or Type – 2 of SCD.

There are lot of ways to load data incrementally like -

SSIS Component (Lookup, conditional split etc)
Third party components
Using SQL MERGE statement

I have plan to cover above three implementation approach in my coming blogs..today will talk about SCD using Lookup, Conditional Split tasks. We will create a SSIS package using these tasks to -

Figure out whether incoming value in the pipe already exists in database.
Lookup column data has been changed or same as one in the database.

First Create a table and populate some records.

CREATE TABLE [DBO].[TTB_STUDENT](
   ID      INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
   NAME    VARCHAR(100) NULL,
   AGE     INT
)
GO
INSERT INTO [DBO].[TTB_STUDENT] VALUES
('Elaine', 30),
('Kevin', 42),
('Robin', 22),
('Alice', 15)
GO

Select above table and see the result -

SELECT * FROM [DBO].[TTB_STUDENT]

Find below the package and the tasks we used.

We are using script component task as Source (you will be prompted with options like source, target etc when you drag a script component tasks on package designer) to create source for the downstream transformation tasks. Set the output name as MyOutput and add 2 columns as under.

Go back to Script tab and click on Edit Script… button and place following code snippet. We are adding some student rows to test all 3 scenarios.

public override voidCreateNewOutputRows()
{
/*
Lets add some student rows such that we cover
New student, Student with same age and student with
different age.
*/

//Existing student with different age
MyOutputBuffer.AddRow();
MyOutputBuffer.StudentName = "Elaine";
MyOutputBuffer.Age = 33;

//Existing student with same age
MyOutputBuffer.AddRow();
MyOutputBuffer.StudentName = "Kevin";
MyOutputBuffer.Age = 42;

//New student
MyOutputBuffer.AddRow();
MyOutputBuffer.StudentName = "Cameron";
MyOutputBuffer.Age = 55;
}

I will just run the package at this point by adding data viewer for you to check dataset that we have built to be used as source for lookup task (note that you have to connect script component output to a task before you run the package).

Drag a lookup tasks on the designer and set editor properties as under. Make sure you set error output for lookup match output to redirect row. I am placing the screen shot for you to be able to connect well.

Set the link between available input and lookup column as under. Here we are using student name for the equijoin and lookup column is Age.

This is how the error output should be set so that row is redirected and it can be used in the downstream transformation.

From lookup task we will have two outputs. First output will be for newly added students (from the source) and second output will be for existing students.

Newly added students flow we can simply connect to underline database table. Note that I have used row count tasks and store in a variable which can be used for any auditing or emailing purposes. However this is totally depends on your requirement and shall be altered per that.

Second flow (existing student with same and different age), we will connect to conditional split task for us to be able to split 2 outputs - 1. Student with same age 2. Student with different age. Note that student with same age, we don’t need to take any action. Student with different age we will connect to OLE DB Command SSIS task through UNION ALL task. This is how your conditional split task should be setup -

This is how your OLE DB Command task should be setup. In the Connection Manager tab set the connection you used to connect to your relational database. In the Component Properties tab set SqlCommand properties with the SQL Update statement (Shown in the dialog). Note that In the Column Mapping tab you have to specify parameters we have used in the update.

Component Properties tab -

Column Mappings tab -

Finally run the package and observe the data viewers for the 3 outputs. Remember we are not taking any action if it is existing student with same age as in database table. -

As you could make out by looking the ETL flow, Elaine went to OLE DB update, Kevin went to Age is same flow (no change in age) and Cameron went to OLE DB Insert.

Let’s select from the table again -
SELECT * FROM [DBO].[TTB_STUDENT]

You can do the same using out of box SCD tasks however it comes with some drawbacks and this is one of the best alternative approach. Hope the article will be helpful for you to understand and compare with other available solutions to load data incrementally. Request you to leave any feedback or comments you guys have.

Bye for now, will be back with more. Enjoy long weekend!

Unpivot data transformation in SSIS

2011-05-25T11:57:00.001-07:00

I get sales product data in an excel spreadsheet. This file holds yearly sales data of all the products. Following is the excel template business users are using to capture sales data month to month. The requirement is to unpivot the sales data and store in a table with appropriate snapshot. Find below file template with sample data -

Find below table structure where above file has to be imported using package.

CREATE TABLE [DBO].[TTB_PRODUCT_SALES](
  [PRODUCT_NAME]    NVARCHAR(100) NOT NULL,
  [SALES_AMT] DECIMAL(15,4) NULL,
  [SNAPSHOT_DATE] VARCHAR(50) NULL
)

Next step would be to create a SSIS package to import data from excel file into the database table recursively such that all these months are loaded. Note that we can do this using unpivot transformation task.

Steps to create package -

Go to your integration service project in your solution and add new SSIS package.

Add a data flow task under Control Flow tab as under -

Double click on the above task to go to the data flow tab. Next, right click on Connection Managers tab and select Excel from the connection manager list, click on Add… button and select sales excel as your source. Make sure you checked First row has column names in the Excel Connection Manager.

You can add data viewer and run the package to view current state of dataset. It will be like this  -

Add Unpivot transformation tasks to unpivot sales columns into rows. Double click on Unpivot transformation task and editor as under -
  
As you see we have to pass through Product as we want to keep product and transform other columns (months like Jan, Feb etc) into rows.  Meaning, month columns will be converted into rows for each product. Set Destination Column to SALES_AMT to store unpivoted value in the database table. Set Pivot key value column name to “PKeyMonth”. This variable will hold value of the pivot key value. In our example we have put month name as key (Jan, Feb etc). Add a data viewer to check incoming rows and variable value -

Let’s add a script component task to read “PkeyValue” value and convert it into snapshot date. Select Transform in the Select Script Component Type dialog. In the Script Transformation Editor go to Inputs and Outputs tab and add a output column name SnapshotDate. Next switch to Script tab and place following script to assign snapshot date value to newly added variable. We will use this variable to set snapshot in the database table.

public override void Input0_ProcessInputRow(Input0Buffer Row)
  {
        string varSnapShotDate = string.Empty;
      switch (Row.PKeyMonth)
      {
         case "Jan": varSnapShotDate = "2011-01-31"; break;
         case "Feb": varSnapShotDate = "2011-02-28"; break;
         case "Mar": varSnapShotDate = "2011-03-31"; break;
         case "Apr": varSnapShotDate = "2011-04-30"; break;
         case "May": varSnapShotDate = "2011-05-31"; break;
         case "Jun": varSnapShotDate = "2011-06-30"; break;
         case "Jul": varSnapShotDate = "2011-07-31"; break;
         case "Aug": varSnapShotDate = "2011-08-31"; break;
         case "Sep": varSnapShotDate = "2011-09-30"; break;
         case "Oct": varSnapShotDate = "2011-10-31"; break;
         case "Nov": varSnapShotDate = "2011-11-30"; break;
         case "Dec": varSnapShotDate = "2011-12-31"; break;
      }
  Row.SnapshotDate =  varSnapShotDate;
  }

Your SSIS Package data flow tab should look like this. Run the package and query database table
(SELECT * FROM [DBO].[TTB_PRODUCT_SALES]).

Filter dimension member using MDX VBA function

2011-05-23T10:58:00.001-07:00

If you are passing month time member in your MDX to slice cube and need to get quarter number for some calculation, you can try something like this. Assume a case where you are showing monthly measure value on the report but the quarterly target. In this case you will need to derive quarter number from month parameter passed in the MDX and define a calculated member for target.

Find MDX example below –

WITH 
  MEMBER [INVESTMENT PRODUCT].[PRODUCT NAME].SYKESPRODUCT AS 
    Aggregate
    (
      {
        Filter
        (
          NonEmpty
          (
            [INVESTMENT PRODUCT].[PRODUCT NAME].[PRODUCT NAME].MEMBERS
           ,[Measures].[ONCE DONE SURVEYS]
          )
         ,
            VBA!Left
            (
              VBA!Ltrim([INVESTMENT PRODUCT].[PRODUCT NAME].CurrentMember.Name)
             ,5
            )
          = "SYKES"
        )
      }
    ) 
  MEMBER [INVESTMENT PRODUCT].[PRODUCT NAME].OTHERPRODUCT AS 
    Aggregate
    (
      Filter
      (
        NonEmpty
        (
          [INVESTMENT PRODUCT].[PRODUCT NAME].[PRODUCT NAME].MEMBERS,
         ,[Measures].[ONCE DONE SURVEYS]
        )
       ,
          VBA!Left
          (
            VBA!Ltrim([INVESTMENT PRODUCT].[PRODUCT NAME].CurrentMember.Name)
           ,5
          )
        <> "SYKES"
      )
    ) 
SELECT 
  {[Measures].[ONCE DONE SURVEYS]} ON COLUMNS
 ,{
      {
        [INVESTMENT PRODUCT].[PRODUCT NAME].SYKESPRODUCT
       ,[INVESTMENT PRODUCT].[PRODUCT NAME].OTHERPRODUCT
      }
    * 
      [TIME].[YQMD].[Month].&[2010-11-01T00:00:00]
  } ON ROWS
FROM [YOUR CUBE];

MDX - Get quarter number from current month time member

2011-05-19T12:39:00.000-07:00

Find MDX example below –

WITH 
  //Find quarter number from time member
  MEMBER MEASURES.[QUARTER NUMBER] AS 
    Cint(Round(YTD().Count + 1) / 3) 
  MEMBER [Measures].[TARGET VALUE] AS 
    MEASURES.[QUARTER NUMBER] * [Measures].[TARGET] / 4 
SELECT 
  {
    MEASURES.[QUARTER NUMBER]
   ,[Measures].[TARGET VALUE]
  } ON COLUMNS
 ,{StrToSet(@ParamTimeHier)} ON ROWS
FROM 
(
  SELECT 
    StrToSet(@ParamTimeHier) ON COLUMNS
  FROM [YOUR CUBE]
);

MDX - Prorating target for current month or quarter

2011-05-19T11:08:00.000-07:00

In the report, if you need to prorate your measure value based on the month or quarter you are showing, you can do something like the example explained below.

Example. You might get yearly target of say 400 million dollar and based on the quarter you are in, might want prorate the value as under –

Quarter Number	Prorated $ Value
Quarter 1	100
Quarter 2	200
Quarter 3	300
Quarter 4	400

MDX snippet –

WITH
MEMBER MEASURES.MONTHNUMBE AS
Month(Cdate(Left(Right([TIME].[YQMD].CurrentMember.UniqueName,20),10)))

MEMBER MEASURES.PRORATED_TARGET AS
CASE
WHEN MEASURES.MONTHNUMBER = 1 THEN 1 * [MEASURES].[TARGET] / 4
WHEN MEASURES.MONTHNUMBER = 4 THEN 2 * [MEASURES].[TARGET] / 4
WHEN MEASURES.MONTHNUMBER = 7 THEN 3 * [MEASURES].[TARGET] / 4
ELSE 4 * [MEASURES].[TARGET] / 4
END

SELECT
{
MEASURES.PRORATED_TARGET
,MEASURES.MONTHNUMBER
} ON COLUMNS
,
NonEmpty
(
StrToMember(@ParamTime).Parent.Lag(3) : StrToMember(@ParamTime).Parent
,[Measures].[TARGET]
)
DIMENSION PROPERTIES
MEMBER_CAPTION
,MEMBER_UNIQUE_NAME
,PARENT_UNIQUE_NAME
,LEVEL_NUMBER
ON ROWS
FROM [YOUR_CUBE];

In case of you want your prorating to be done monthly, meaning like –

Month Number	Prorated $ Value
January	1*400/12
February	2*400/12
…….	---
December	12*400/12

Replace above calculated member as under -

MEMBER MEASURES.PRORATED_TARGET AS
YTD().Count*[Measures].[TPM FY TARGET]/12

Dynamic Management Views for the Analysis Service Db Monitoring

2011-02-22T11:09:00.000-08:00

SQL Server 2008 has some of the cool DMV (Dynamic Management View) which can be used for analysis and monitoring of analysis server databases. You can in fact drill to the details to figure out when the cube and other objects like dimesions, measure groups etc were last processed or modified, whether hierarchy is being used etc.

You can run these DMV’s either in DMX query window or using link server on the relational database query window.

Here find list of DMV’s and their categories –

Analysis Service database Schema

$SYSTEM.DBSCHEMA_CATALOGS

$SYSTEM.DBSCHEMA_COLUMNS

$SYSTEM.DBSCHEMA_PROVIDER_TYPES

$SYSTEM.DBSCHEMA_TABLES

Data Mining Schema

$SYSTEM.DMSCHEMA_MINING_COLUMNS

$SYSTEM.DMSCHEMA_MINING_FUNCTIONS

$SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT

$SYSTEM.DMSCHEMA_MINING_MODEL_CONTENT_PMML

$SYSTEM.DMSCHEMA_MINING_MODEL_XML

$SYSTEM.DMSCHEMA_MINING_MODELS

$SYSTEM.DMSCHEMA_MINING_SERVICE_PARAMETERS

$SYSTEM.DMSCHEMA_MINING_SERVICES

$SYSTEM.DMSCHEMA_MINING_STRUCTURE_COLUMNS

$SYSTEM.DMSCHEMA_MINING_STRUCTURES

Meta Data of Analysis Service Database objects like Cube and ther partitions, Hierarchies etc

$SYSTEM.MDSCHEMA_CUBES

$SYSTEM.MDSCHEMA_DIMENSIONS

$SYSTEM.MDSCHEMA_FUNCTIONS

$SYSTEM.MDSCHEMA_HIERARCHIES

$SYSTEM.MDSCHEMA_INPUT_DATASOURCES

$SYSTEM.MDSCHEMA_KPIS

$SYSTEM.MDSCHEMA_LEVELS

$SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS

$SYSTEM.MDSCHEMA_MEASUREGROUPS

$SYSTEM.MDSCHEMA_MEASURES

$SYSTEM.MDSCHEMA_MEMBERS

$SYSTEM.MDSCHEMA_PROPERTIES

$SYSTEM.MDSCHEMA_SETS

To run and get audit and monitoring details of Analysis Service Database, say find the details when the cubes were last updated and processed –

Open Sql Server Management Studio
Connect to your one of the analysis service database.
Right click on the database name and select DMX query.

Copy and paste following query in the DMX query window –
SELECT * FROM $SYSTEM.MDSCHEMA_CUBES WHERE CUBE_SOURCE = 1

Find below the result of above query. Only relevant and important columns are included -

CUBE_NAME	LAST_SCHEMA_UPDATE	LAST_DATA_UPDATE
Audit	5/19/2011 16:01	5/20/2011 6:46
Cost Center	5/19/2011 16:01	5/20/2011 6:46
Dashboard Metrics	5/19/2011 16:01	5/20/2011 6:46
High Stability	5/19/2011 16:01	5/20/2011 6:46
Manage Risk	5/19/2011 16:01	5/20/2011 6:46
Resource Management	5/19/2011 16:01	5/19/2011 15:43
SR Practices	5/19/2011 16:01	5/20/2011 6:46
Superiror Delivery	5/19/2011 16:01	5/20/2011 6:46
Top Programs	5/19/2011 16:01	5/19/2011 15:43
World Class People	5/19/2011 16:01	5/20/2011 6:46

Alternatively you can run query connecting to the underline relation database query window. You would need to use link server for query to be able to connect to the analysis service database.

Above query can be run in the relation query window –

SELECT * FROM OPENQUERY(YOUR_LINK_SERVER_NAME,'select * from $SYSTEM.MDSCHEMA_CUBES WHERE CUBE_SOURCE = 1')

Format value using UDF based on format type

2011-02-07T11:50:00.000-08:00

I encountered a situation where I had to return formatted value to the front end site based on the format type passed. Format type can be dollar ($), number (#) or percentage (%). I am not sure if this is good way to format value based on type passed, but worked for me perfectly –

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DW].[fnGetFormattedValue]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [DW].[fnGetFormattedValue]
GO

CREATE FUNCTION [DW].[fnGetFormattedValue](
    @Value FLOAT,
    @FormatType Varchar(50)
)

RETURNS VARCHAR(50) 
AS

BEGIN

    DECLARE @ReturnValue VARCHAR(100)
    DECLARE @TempValue DECIMAL(10, 2)

    SET @TempValue = CONVERT (DECIMAL(10, 2), ROUND(@Value, 2))
    SET @ReturnValue =
    
    CASE @FormatType WHEN '$' THEN '$' + CAST (@TempValue AS VARCHAR)
    WHEN '%' THEN CAST (@TempValue AS VARCHAR) + '%'
    WHEN '#' THEN CAST (@TempValue AS VARCHAR)
    ELSE CAST (@TempValue AS VARCHAR)
    END

    RETURN @ReturnValue

END
GO

SQL find duplicate using Group and Partition By

2011-02-03T08:25:00.000-08:00

There are various ways to find duplicate records in SQL Server. Like using group and count, partitioning result set etc.

This article talks about finding duplicate records using two of these methods. I will straight away jump into the SQL example for you to understand easily and quickly.

Create a table name with [STG].[TMS_TOP_PROGRAMS] with following entries into it. This table I have all the programs along with some other attributes like ID, Sort Order etc -

PROGRAM_ID	PROGRAM_NAME	SORT_ORDER	BATCH_ID
11	Align the Right Customers to the Right Solutions at Optimized cost	1	100
11	Align the Right Customers to the Right Solutions at Optimized cost	1	100
12	Create competitive advantage through business analytics and benchmarking	6	100
13	Deploy future state CSSG operating model	2	100
13	Deploy future state CSSG operating model	2	100
14	Enhance employee engagement	7	100
14	Enhance employee engagement	7	100
15	Expand Direct Sales Capabilities to Grow Revenue	3	100
16	Optimize Service Contacts to Create Sales Opportunities	4	100
16	Optimize Service Contacts to Create Sales Opportunities	4	100
17	Simplify End to End Customer Experience	5	100
17	Simplify End to End Customer Experience	5	100
6	OpEx Enterprise-Wide Programs	6	100
6	OpEx Enterprise-Wide Programs	6	100

As you see there are lot of duplicate programs in this table. One ways to find duplicate records is by using Group clause as under -

SELECT 
    PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, 
    SORT_ORDER,  [BATCH_ID], COUNT(PROGRAM_ID)
FROM [STG].[TMS_TOP_PROGRAMS] 
GROUP BY PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, PROGRAM_SORT_ORDER, [BATCH_ID]
HAVING COUNT(PROGRAM_ID) > 1

Another and yet more efficient way to find duplicate by partitioning the table based on the key, here is what I mean..

SELECT PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, PROGRAM_SORT_ORDER, [BATCH_ID], RANK_ID FROM
(
    SELECT PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, 
    SORT_ORDER, [BATCH_ID],
    ROW_NUMBER() OVER  (
       PARTITION BY PROGRAM_ID ORDER BY PROGRAM_ID
    ) AS RANK_ID
    FROM [STG].[TMS_TOP_PROGRAMS]
 ) AS INTBL WHERE RANK_ID > 1

Filter and Count in MDX

2011-02-02T13:01:00.001-08:00

Filter and Count in MDX
If you have database table as shown and you requirement is to filter value out before you count the row. In my example, notice the highlighted row, I got 0 score for those 2 rows and I don't want such row included when I either create a calculated member in the cube or write report using MDX. In this example I need to get sum of the programs only if score > 0. This is how you the filter condition with count will look -
MEMBER [Measures].[Program_Count] AS
count (filter([TOP PROGRAMS].[PROGRAM ID].[PROGRAM ID], [Measures].[PROG SCORE]>0))

SQL Insert Update using MERGE

2011-02-02T12:57:00.000-08:00

This will be a very short blog post to help you understand the syntax and usage of Merge SQL statement. I will use same program table ([STG].[TMS_TOP_PROGRAMS]) as source table which I am used in my some of of the other blog posts.

MERGE is very handy when it comes to update or insert using a single SQL statement. In my example I will use PROGRAM_ID to check whether row already exists and if so will update all other attributes else will insert into [STG].[TMS_TOP_PROGRAMS] table.

This is the source table ([STG].[TMS_TOP_PROGRAMS]).

PROGRAM_ID	PROGRAM_NAME	SORT_ORDER	BATCH_ID
11	Align the Right Customers to the Right Solutions at Optimized cost - New	1	100
12	Create competitive advantage through business analytics and benchmarking - New	6	100
13	Deploy future state CSSG operating model	2	100
14	Enhance employee engagement - New	7	100
15	Expand Direct Sales Capabilities to Grow Revenue	3	100
16	Optimize Service Contacts to Create Sales Opportunities - New	4	100
17	Simplify End to End Customer Experience	5	100
6	OpEx Enterprise-Wide Programs	6	100
7	Enterprise Services	8	100

This is how destination table ([DW].[TMS_DW_TOP_PROGRAMS]). Idea is, key from the source table (PROGRAM_ID in my example) will be compared against destination table (PROGRAM_ID column) and if match found a particulate row will be updated else new row will be inserted into the table.

PROGRAM_ID	PROGRAM_NAME	SORT_ORDER	BATCH_ID
1	Organization Alignment	7	100
11	Align the Right Customers to the Right Solutions at Optimized cost	1	100
12	Create competitive advantage through business analytics and benchmarking	6	100
13	Deploy future state CSSG operating model	2	100
14	Enhance employee engagement	7	100
15	Expand Direct Sales Capabilities to Grow Revenue	3	100
16	Optimize Service Contacts to Create Sales Opportunities	4	100
17	Simplify End to End Customer Experience	5	100
6	OpEx Enterprise-Wide Programs	6	100

Let’s write and check query now. Following query will insert Program ID 7 into destination table as it does not exist and will update all other records from the source. Note that Program ID 1 in the destination table will not have any impact as it is not present in the source.

MERGE [DW].[TMS_DW_TOP_PROGRAMS] AS SR
USING
(
SELECT PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, SORT_ORDER, [BATCH_ID], RANK_ID FROM
(
    SELECT PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, 
    SORT_ORDER, [BATCH_ID],
    ROW_NUMBER() OVER (
      PARTITION BY PROGRAM_ID ORDER BY PROGRAM_ID) AS RANK_ID
  FROM [STG].[TMS_TOP_PROGRAMS]
) AS INTBL WHERE RANK_ID = 1
) AS TR
ON SR.PROGRAM_ID = TR.PROGRAM_ID
WHEN MATCHED THEN 
    UPDATE SET SR.[PROGRAM_NAME] = TR.[PROGRAM_NAME], 
            SR.KEY_PROGRAM = TR.KEY_PROGRAM, 
            SR.SORT_ORDER = TR.SORT_ORDER, 
WHEN NOT MATCHED THEN 
    INSERT (
            PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM,      
            PROGRAM_SORT_ORDER, [BATCH_ID]
            )
    VALUES (
            TR.PROGRAM_ID, TR.[PROGRAM_NAME], TR.KEY_PROGRAM,  
            TR.PROGRAM_SORT_ORDER, TR.[BATCH_ID]
           );

Null value is eliminated by an aggregate or other SET operation.

2011-02-01T11:35:00.000-08:00

Null value is eliminated by an aggregate or other SET operation
You get this error while executing SQL statement or stored procedure, if you use aggregate function like sum, max, min etc on a column which contains NULL value.
Example:
SET @METRIC_ID = (SELECT MAX(Convert(int,Metric_ID))+1 from [WEB].[TMS_TOP_PROGRAMS_ENTRY]);
Change above statement with this to get rid of above warning (ANSI):
SET @Metric_ID = (SELECT MAX(Convert(int,isnull(Metric_ID,0)))+1 from [WEB].[TMS_TOP_PROGRAMS_ENTRY]);

SQL Query - Transaction on DDL

2010-07-02T15:44:00.000-07:00

Execute this.
Begin transaction
Create table dbo.t1 (i int);
Rollback transaction
What do you think about the output of following statement?
select * from dbo.t1

SQL Query - You Can (1)

2010-06-30T08:50:00.000-07:00

I have a table with with entries.

Need to split , separated value to create detail entries like as under

2010-06-30T08:31:00.000-07:00

Best practices for designing and developing cubes
http://msdn.microsoft.com/en-us/library/cc966399.aspx#EDAA
What is Degenerate or junk dimension?
http://technet.microsoft.com/en-us/library/ms167409.aspx
Explain with an example how many-to-many dimension usage works?
What is the use of ADOMD.NET data provider?
What do you understand by MDX, DMX, ASSL?
What are the system tables and their usage - sysobject, syscomments?
When do we execute query using OpenQuery or OpenRowSet command?
http://www.sqlyoga.com/2009/04/sql-server-difference-between-openquery.html
How to process large dimension / cube partition?
http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=7
What is the use of AMO (Analysis Management Object)?
http://msdn.microsoft.com/en-us/library/ms345084(v=SQL.90).aspx
In Which Scenarios incremental loading should be done.
http://vsteamsystemcentral.com/cs21/blogs/applied_business_intelligence/archive/2007/05/21/ssis-design-pattern-incremental-loads.aspx
Rank() and Partition by in SQL?
Type of slowly changing dimensions?
http://www.mssqltips.com/tip.asp?tip=1442
Merge or Conditional split to load incremental data?
http://consultingblogs.emc.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx
Difference between multicast and conditional split tasks?
http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx
Event handling in SSIS
Explain parent-child dimension?
Cube securing without Active Directory.
Report deployment, native mode and share point mode?
Partitioning Strategy for cube
Should we select materalize in the dimension usage?
http://msdn.microsoft.com/en-us/library/ms178448.aspx
ETL packate configurations and when to use which one?
http://www.mssqltips.com/tip.asp?tip=1405
Parent and child package variables and call.
SQL server and file system package deployment
Execute child pacakge using execute pkg or execute process tasks?
DTEXEC to execute package and various parameters.
http://msdn.microsoft.com/en-us/library/ms162810.aspx
How to call package using job?
Managing and running etl packages?
http://technet.microsoft.com/en-us/library/cc966389.aspx
Create, build and publish reports and model?
http://www.accelebrate.com/sql_training/ssrs_2008_tutorial.htm
Report server and report temp db roles?
MDX - Set, tuples, named query etc.
http://www.databasejournal.com/features/mssql/article.php/10894_3325131_2/MDX-in-Analysis-Services-Named-Sets-in-MDX-An-Introduction.htm
Handling slowly changing dimension in SSIS if lot of records required to be processed.
http://blogs.msdn.com/b/mattm/archive/2009/11/13/handling-slowly-changing-dimensions-in-ssis.aspx

Error processing OLAP project

2009-10-12T06:41:00.001-07:00

If you get this error - "Key not valid for use in specified state"
One probable solution can be to change the a/c under which analysis service is running. I changed from "NT AUTHORITY\LOCAL SERVICE" to "Local Service" a/c and worked for me.

Figure out whether SSIS package was executed from JOB or not

2009-09-22T02:58:00.000-07:00

Write one SP, like this -
ALTER PROCEDURE [dbo].[RunDTSPackages]
@JobID VARCHAR(255)
AS
BEGIN
SET ROWCOUNT 0
DECLARE @DtexecQuery VARCHAR(8000)
DECLARE @PackageName VARCHAR(200)
DECLARE @DatabaseServer VARCHAR(100)
DECLARE @ID INT
DECLARE @JobSYSID VARCHAR(255)
DECLARE @ReturnCode INT
----my package name to BLANK initially
SET @PackageName = ''
----my server name
SET @DatabaseServer = 'F1LT230'
--Passing the SysJobID
SET @JobSYSID ='/set \package.variables[SysJobID].Value;"\"'+@JobId+'\""'
DECLARE @tblSSISMaster TABLE(
ID INT PRIMARY KEY,
Name VARCHAR(255),
PackageName VARCHAR(255)
)
INSERT @tblSSISMaster SELECT
ID,
Name,
PackageName
FROM SSISPackageToRun
WHERE IsMaster = 1 ORDER BY ID DESC
SET ROWCOUNT 1
SELECT @ID = ID FROM @tblSSISMaster
WHILE @@ROWCOUNT <> 0
BEGIN
SELECT @PackageName = LEFT (PackageName, LEN(PackageName)-5) FROM @tblSSISMaster WHERE ID = @ID
SET @DtexecQuery = 'dtexec /sq ' + @PackageName + ' /ser ' + @DatabaseServer + ' '
SET @DtexecQuery = @DtexecQuery + @JobSYSID
--Print @DtexecQuery
EXEC @ReturnCode = xp_cmdshell @DtexecQuery
DELETE FROM @tblSSISMaster WHERE ID = @ID
SELECT @ID = ID FROM @tblSSISMaster
END
SET ROWCOUNT 0
END
Create a job and call above SP as under. Also ensure that you are logging the GUID generated from the job. Also, if same package is being executed from other way - using WEB App or directly, pass a hard coded GUID.
DECLARE @SQL NVARCHAR(72), @jobID UNIQUEIDENTIFIER, @jobName SYSNAME
SET @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'
EXEC sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT
EXEC RunDTSPackages @jobID
Query the log table for the above two unique GUIDs and get the reports generated as in to know whether the package was executed from the Job or Web.
Your log table should have following columns -
ExecutionLogId]
,[ParentExecutionLogId]
,[Description]
,[PackageName]
,[PackageGuid]
,[MachineName]
,[ExecutionGuid]
,[LogicalDate]
,[Operator]
,[StartTime]
,[EndTime]
,[Status]
,[FailureTask]
,[tblsysJobID]

Reporting Service 2008: Call .NET function in MDX

2009-09-10T04:39:00.000-07:00

Steps -

Create an .NET library. Ensure there is a public function that returns a value - String, Int etc.
Connect to Analysis Server and right click on Assembly folder.
In the Register Server Assembly window, browse and select the assembly (DLL), created in the step-1. Remove if there is any space in in the Assembly name textbox, you find.
Under the Permission section, select External access and click on OK button.
Notice that the one assemly is added under the Assembly folder.
In MDX write code as under -
MEMBER [Measures].[TECHNOLOGY NAME] AS
(
ITGExecDashboard.GetTechnologyNames ([Measures].[APPLICATION ID])
)
Select above measure in the column. Note that GetTechnologyNames .NET function is being executed and application identifier is being passed which is coming from other measure.