Sunday, December 25, 2011

How to automate cube processing


After creating a cube, a first question strike to the professionals is "How to automate cube processing?” So I thought let's share some methods by which one can automate daily cube processing. There are several methods present by using those you can automate cube processing but I am going to discuss few of them which are most widely used across the organizations.
Following are the 2 most widely used approaches; 
(Note :- Following example is demonstrated using Adventure-works sample database)

1) Using XMLA:-

By using following approach you can Process any object of Analysis services database i.e. SSAS database itself, Measure Group, Cube, Partitions etc....
I am going to explain the approach for Processing “SSAS database” and following are the steps which one can follow to accomplish the same.

a)      Connect to Database engine and expand “SQL Server Agent” node, right click on the “Jobs” folder and select “New Job” option.



b)      When you click on “New Job” option, it opens “New Job” wizard, under “General” tab, mention all the required details like name of Job, Owner etc…



c)      Click on the “Steps” tab and click on “New…” option.



d)      Under “New Job Step” wizard, give the appropriate "Step name" and select “SQL Server Analysis Services Command” option under “Type”.



e)      Mention the server instance name under “Server” option and paste the following XMLA script under “Command”. (Note :- Following sample XMLA is for Adventure-works but you can simply use the name of your SSAS database name instead of "Adventure Works DW 2008R2" in the following XMLA).

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
             xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
             xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
             xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200"
             xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
      <Object>
        <DatabaseID>Adventure Works DW 2008R2</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>



Click on the OK button of “New Job Step” wizard and then click on the OK button of “New Job” wizard.
So now your SQL Server Job for processing SSAS database is ready and you can schedule the job as per your requirement.


2) Using AMO (Analysis Management Objects).

In this approach, SSIS package needs to be created using “Script Task”. You can follow following steps for achieving the same.

a)      Create a new SSIS package using “Script Task”; refer following screen shot for the same. (I have given the name of Script task as “Process SSAS database” in my sample and that’s why same is appearing in the screen shot). Right click on “Process SSAS database” named script task and select “Edit” option, when you click on “Edit” option, it will open “Script Task Editor” wizard. Under ScriptLanguage you can select "Visual C# or Visual Basic". I have demonstrated the sample using "Visual Basic" and that's why I have selected the same in the "ScriptLanguage". After selecting ScriptLanguage, click on "Edit Script" button.




b)   When you click on “Edit Script” option, it will open VB script code window, open “Project Explorer” (press “Ctrl+R”for the same). Click on “Show All files” option. Right click on “References” node and click on “Add Reference” option.



c)   Under “Add Reference” window, Select “Analysis Management Objects” under “.Net” tab and click on OK button.


d)  When you click on OK button, you will find the reference of "Microsoft.AnalysisServices" dll under the References. For using the classes and objects of referenced dll write,            Imports Microsoft.AnalysisServices at the top of .VB code file.


e)  Write following AMO code under Main() procedure, save and close the file.



Public Sub Main()
Dim objServer As Server
Dim objDatabase As Database

objServer = New Server
objServer.Connect("localhost") 'Write the instance name of SSAS server"  
objDatabase = objServer.Databases.FindByName("Adventure Works DW 2008R2")
objDatabase.Process(ProcessType.ProcessFull)

objServer.Disconnect()
Dts.TaskResult = ScriptResults.Success

End Sub


f)   Now your SSIS package is ready, you can create a SQL Server Job for scheduling SSIS package.
      Follow same steps a,b,c from "Using XMLA" (i.e. 1st approach)  and in step d, under "Type", instead of Analysis Services command, select "SQL Server Integration Services Package" and mention the package path (if you want to select "Package source" as File system).Click on OK button of New job step wizard and then OK button of New job and your SQL Server job for processing SSAS database using SSIS package is ready.You can schedule a job as per your requirement.



      You can also create a SSIS package using "Analysis Services Processing Task" and create a SQL Server Job for scheduling SSIS package.
      








Thursday, December 22, 2011

Populate Date dimension table

Date dimension plays very important role in most of the BI solutions and if someone wants to create a Date dimension then first requirement is "how to populate the required Date dimension table". So I thought let's share a SQL script which I have created for populating Date dimension table.
If you execute the following SQL script, then that will create and populate the Date dimension table with the same data as used in Adventureworks sample database.


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))
DROP TABLE [dbo].[DimDate]

GO

WITH DateDimension_CTE as
(
SELECT Cast ('2008-01-01' as DateTime) FullDate
UNION ALL
SELECT FullDate + 1
FROM DateDimension_CTE
WHERE FullDate + 1 < = '2020-12-31'
)
SELECT
CAST(CONVERT(CHAR(8),CAST(FullDate AS DATETIME),112) AS INT) AS DateKey
,CAST(FullDate AS Date) AS FullDate
,DatePart (dw, FullDate) AS DayNumberOfWeek
,DateName (dw, FullDate) as EnglishDayNameOfWeek
,DAY (FullDate) as DayNumberOfMonth
,DatePart (dy, FullDate) as DayNumberOfYear
,DatePart (wk, FullDate) as WeekNumberOfYear
,DateName (mm, FullDate) AS EnglishMonthName
,MONTH (FullDate) AS MonthNumberOfYear
,DatePart ( qq, FullDate) AS CalendarQuarter
,YEAR (FullDate) AS CalendarYear
, (CASE WHEN MONTH (FullDate)>=1 AND MONTH (FullDate) <=6 THEN 1 ELSE 2 END) CalendarSemester
INTO DimDate
FROM DateDimension_CTE

OPTION (MAXRECURSION 0)

GO

After executing above SQL, you will find the "DimDate" dimension table under the specified database with all the columns populated with the required data.

How to find which edition of SSAS is installed ?

Recently one of mine colleague asked me "how to find which edition of SSAS is installed" and the solution is very simple but most of the professionals are not aware of this So I thought lets share this with this blog so that will help others too..

1. Connect to Analysis services and right click on the instance name.

2. Click on "General" under "Standard Reports" which is present under "Reports" options. Refer following    screen shot for the same.


3. When you click on "General" options, it will open a report which shows the edition either Standard/Enterprise/Evaluation....Report looks similar to the following one.



Monday, September 26, 2011

Create an Analysis Services Project Based on an Existing Analysis Services Database

In most the MSBI projects, if the volume of data is huge then generally we create a SSIS package for creating partitions automatically under the corresponding measure group on yearly basis, quarterly basis (or monthly basis...depends on the volume of data and the requirement). For achieving this, generally we create a package with a "Script task" and write AMO (Analysis Management Objects) code for creating partitions. We then schedule that package using "SQL Server Agent" in such a way that it should create a partition before the new year,quarter or month begins (I have explained the steps in one of my blog to create the package in order to automate the creation of partitions Automate creation of cube partitions). So if you execute the package then it will create a partition in the cube but that partition does not exist in your original SSAS database project  file which you have created while designing cube (Original project file).So basically the original SSAS project file and the another one which exist on the SSAS Server (On-Line mode copy) does not match. Now just consider, server copy of your SSAS database contains some partitions which are not present in the original project copy and then client comes with the requirement in which they want to add some more dimensions and measures to the existing cube and that will create a question in front of you, either to do the changes in the "On-Line mode" or to create those extra partitions manually in the original solution file in order to get both the versions in sync. But basically most the people are not aware that there is one option in BIDS i.e. "Import Analysis Services Database" and by using this option, you can import a project file (i.e. create a BIDS project file) from the existing SSAS database which is present on SSAS Server so I thought lets share this with this post. You can follow following steps for importing analysis services database from the existing one which is present on SSAS server.


1. Open BIDS and press "Ctrl + Shift + N", so this will open a "New Project" wizard.Under "Project Types", select "Business Intelligence Project" and then select "Import Analysis  Services Database" option. Mention the proper "Name", "Location" in the Wizard and click on OK  button. Refer following screen shot for the same.




2. When you click on OK button of "New Project" wizard, it will open "Import Analysis Services Database Wizard" window, click on "Next" button. Under "Source database" wizard, give the analysis server name under "Server" option and select analysis services database name from the "Database" drop-down. (As I am demonstrating using "Adventure Works DW 2008R2", I have selected the same which you can refer in following snapshot).





3. After doing appropriate selection in the prior step, click on "Next" button. As soon as you click on Next button, wizard start creating the copy of "Adventure Works DW 2008R2" SSAS database project file. After completion, click on "Finish" button and you will find the SSAS project file named "AdventureWorks" (as we have given the name as "AdventureWorks" in step 1, so this wizard creates the project file with whatever name you provide). If you compare the partitions under project file then you will find all the new partitions there as this is the same copy which is present on server. Now you have a project file similar to server copy and you can made changes on it.






Friday, September 23, 2011

Error while Synchronizing SSAS database

While doing Google, I found that most of the professionals are facing following error message when they are trying to Synchronize the cube between two servers.

Database synchronization started.
The physical TCP/IP connection failed: An existing connection was forcibly closed by the remote host.  
The peer prematurely closed the connection. 
Backup and restore errors: An error occurred while synchronizing the 'AdventureWorks' database. 
An error was encountered in the transport layer. 
Database synchronization failed.

Following is the screen shot for the same.



If you want to synchronize the SSAS database from one server to another then your active directory user should have administrative permissions on the destination SSAS services. I have already mentioned the steps in one of mine blog for the same.
http://aniruddhathengadi.blogspot.com/2011/02/how-to-give-administrative-rights-on.html

For resolving above mentioned issue, run analysis services with domain user who has admin rights instead of local system account.
Follow following steps for the same.

  • Logon to SSAS "Destination server" (i.e. the destination server on which you want to Synchronize SSAS database from source server).
  • Open "Computer Management" (you can open "Computer Management" simply by typing shortcut "compmgmt.msc" in the "Run" window).
  • Under "Computer Management", expand "Services and Applications" node and then select "Services" node. 
  • Under Services panel, right click on "SQL Server Analysis Services(MSSQLSERVER)" and select Properties.
  • Under Properties window, click on "Log On" tab and select option "This account" instead of "Local System account". Mention the credentials of the active directory user who has admin rights (i.e username and password) and restart the services.
                      



Tuesday, June 21, 2011

MDX for retrieving the sum of first 5 months for each year


Sometimes client comes with the requirement in which they want a report which should show the SUM of first 5 months or sum of first 6 months from each year. So let’s discuss this by using MDX. You can create MDX for getting sum of first five months in the following manner. I have build the below MDX on adventureworks sample.

WITH 
  MEMBER
 [Measures].[Sum Of First Five Months] AS 
    Sum

    (
      {
          StrToMember

          ("[Date].[Calendar].[Month].&["

              + 
                [Date].[Calendar Year].CurrentMember
.Member_Key
            + "]&[1]"

          )
        : 
          StrToMember

          ("[Date].[Calendar].[Month].&["

              + 
                [Date].[Calendar Year].CurrentMember
.Member_Key
            + "]&[5]"

          )
      }
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT 
  {[Measures].[Sum Of First Five Months]} ON 0
 ,[Date].[Calendar].[Calendar Year].MEMBERS
 ON 1
FROM [Adventure Works];


Following is the output of above mdx query.




Value $7,221,202.69 is the SUM of "Reseller Sales Amount" for first 5 months (i.e. Jan,Feb,March,April,May) of year 2006.

You can achieve same output by using different MDX.


WITH 
  
MEMBER [Measures].[Sum Of First Five Months] AS 
    
Sum
    (
      {
          
Descendants
          (
            [Date].[Calendar].
CurrentMember
           ,[Date].[Calendar].[Month]
           ,SELF
          ).
Item(0)
        : 
          
Descendants
          (
            [Date].[Calendar].
CurrentMember
           ,[Date].[Calendar].[Month]
           ,SELF
          ).
Item(4)
      }
     ,[Measures].[Reseller Sales Amount]
    ) 
SELECT  
  {[Measures].[Sum Of First Five Months]} ON COLUMNS
 ,{
    [Date].[Calendar].[Calendar Year].&[2006]
   ,[Date].[Calendar].[Calendar Year].&[2007]
   ,[Date].[Calendar].[Calendar Year].&[2008]
  } ON ROWS
FROM [Adventure Works]; 


If you execute above MDX query then you will get the same output as first one.

Get the list of SSAS Databases


Most of the times, I found question on MSDN forums that “how to get the list of SSAS databases?”. So I thought lets create one post to answer this question.

If you are using SSAS 2008 or higher versions then you can use following DMV (Dynamic Management Views). Open your MDX query pane in SSMS and execute below query.

SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS

You can also retrieve the list of SSAS Databases by using AMO (Analysis Management Views). but for getting a list of all SSAS databases using AMO, your active directory user should have administrator privileges on SSAS server.
For using AMO, you need the reference of “Microsoft.AnalysisServices.dll” in your project. After referencing dll, Import it for using different classes of that dll (i.e Imports Microsoft.AnalysisServices).
Following is the sample code for getting the list of SSAS databases.

Dim strDatabaseName As String
Dim objServer As Server
objServer = New Server

objServer.Connect("localhost")
If objServer.Databases.Count > 0 Then
   For Each objDatabase As Database In objServer.Databases
        strDatabaseName = objDatabase.Name
   Next
End If

objServer.Disconnect()




Thursday, March 17, 2011

Retrieve selected year data and same level data from previous year


Sometimes user comes with the requirement, in which they want to compare “this year data with the previous year’s data”, “this quarter data with the previous year’s same quarter data”, ”selected month data with the previous year’s same month data”, “selected day’s data with last year’s same day data”. This requirement is very general in most of the organizations as they want to analyze the trend of their business by comparing current data with previous one. We can solve such requirements by using some MDX functions. So let’s discuss this with some sample MDX queries.

Calculate selected year data and the previous year data:

Firstly we discuss on how to retrieve previous year data by using MDX and for that we can use MDX ParallelPeriod function.

Syntax of ParallelPeriod function is as follows;

ParallelPeriod( [Level_Expression [ ,Index [ , Member_Expression ] ] ] )

If you execute following MDX query it will return data for supplied year i.e. 2003 and previous year data i.e.2002. So basically we are using ParallelPeriod function in which we usedCalendar Year” level for getting Year level data and we have passed Index as 1 for getting last year.

WITH 
  MEMBER [Measures].[Last Year Data] AS 
    (
      ParallelPeriod
      (
        [Date].[Calendar].[Calendar Year]
       ,1
       ,[Date].[Calendar].CurrentMember
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Last Year Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2003];

If you are going to pass a member from “Calendar Year” level directly (i.e. [Date].[Calendar].[Calendar Year].&[2003]) then you can achieve your requirement by using MDX LAG function also. Following is the sample MDX in which I have used LAG function.

WITH 
  MEMBER [Measures].[Last Year Data] AS 
    (
      [Date].[Calendar].CurrentMember.Lag(1)
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Last Year Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE  ([Date].[Calendar].[Calendar Year].&[2003]);

In some cases user wants to pass particular date and want to view selected date’s year data and previous year’s data. In such requirement you can use MDX ANCESTOR function. In the following MDX query we are passing Date and retrieving data as per year.

WITH 
  
MEMBER [Measures].[Selected Year's Data] AS 
    (
      
Ancestor
      (
        [Date].[Calendar].
CurrentMember
       ,[Date].[Calendar].[Calendar Year]
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = 
"Currency"
  
MEMBER [Measures].[Previous Year's Data] AS 
    (
      
Ancestor
      (
        [Date].[Calendar].
CurrentMember
       ,[Date].[Calendar].[Calendar Year]
      ).
Lag(1)
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = 
"Currency"
SELECT 
  {
    [Measures].[Selected Year's Data]
   ,[Measures].[Previous Year's Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].[August 10, 2003]);

Output of all above mentioned MDX queries are same;


Output:


Calculate selected quarter data and data from previous year's same quarter:

Sometimes user select particular date and they want to view the selected date’s Quarter data as well as previous year’s same quarter data. You can fulfill this requirement by using some MDX functions. If you execute following MDX query then “[Measures].[Selected Day's Quarter Data]” will return Quarter data of selected date whereas “[Measures].[Previous Year's same Quarter Data] “ will return data from previous year’s same quarter.

WITH 
  
MEMBER [Measures].[Selected Day's Quarter Data] AS
    (
      
Ancestor
      (
        [Date].[Calendar].
CurrentMember
       ,[Date].[Calendar].[Calendar Quarter]
      )
     ,[Measures].[Reseller Sales Amount]
    )
   ,FORMAT_STRING = 
"Currency"
  
MEMBER [Measures].[Previous Year's same Quarter Data] AS
    (
      
Ancestor
      (
        
Cousin
        (
          [Date].[Calendar].
CurrentMember
         ,
Ancestor
          (
            [Date].[Calendar].
CurrentMember
           ,[Date].[Calendar].[Calendar Year]
          ).
Lag(1)
        )
       ,[Date].[Calendar].[Calendar Quarter]
      )
     ,[Measures].[Reseller Sales Amount]
    )
   ,FORMAT_STRING = 
"Currency"
SELECT 
  {
    [Measures].[Selected Day's Quarter Data]
   ,[Measures].[Previous Year's same Quarter Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].[September 5, 2003]);


Let me explain above MDX; Calculating value of “[Measures].[Selected Day's Quarter Data]” is pretty simple as we are simply using Ancestor function for retrieving Quarter level data. But for calculating value of a measure [Measures].[Previous Year's same Quarter Data] firstly we need to use MDX Ancestor function with “Calendar Year” level and LAG function for getting Previous year. After finding previous year, we used Cousin Function for retrieving same day of previous year. After retrieving previous year’s same day, again use Ancestor function for getting previous year’s same quarter.

Calculate month level data and previous year's same month data:

Sometimes user select particular date and they want to view the selected date’s Month level data as well as previous year’s same month data. You can fulfill this requirement by using some MDX functions. If you execute following MDX query then “[Measures].[Selected Day's Month level Data]” will return selected day’s Month level data  whereas “[Measures].[Previous Year's same Month Data] “ will return data from previous year’s same month.  

WITH 
  
MEMBER [Measures].[Selected Day's Month level Data] AS 
    (
      
Ancestor
      (
        [Date].[Calendar].
CurrentMember
       ,[Date].[Calendar].[Month]
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = 
"Currency"
  
MEMBER [Measures].[Previous Year's same Month Data] AS 
    (
      
Ancestor
      (
        
Cousin
        (
          [Date].[Calendar].
CurrentMember
         ,
Ancestor
          (
            [Date].[Calendar].
CurrentMember
           ,[Date].[Calendar].[Calendar Year]
          ).
Lag(1)
        )
       ,[Date].[Calendar].[Month]
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = 
"Currency"
SELECT 
  {
    [Measures].[Selected Day's Month level Data]
   ,[Measures].[Previous Year's same Month Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Date].[September 5, 2003]);

Let me explain above MDX query; Calculating value of [Measures].[Selected Day's Month level Data]” is pretty simple as we are simply using Ancestor function for retrieving Month level data. But for calculating value of a measure [Measures].[Previous Year's same Month Data] firstly we need to use MDX Ancestor function with “Calendar Year” level and LAG function for getting Previous year. After finding previous year, we used Cousin Function for retrieving same day of previous year. And After retrieving previous year’s same day, again use Ancestor function for getting previous year’s same month.

Calculate data for selected date and same date's data from previous year: 


In some scenarios, user wants to compare transaction of selected date and same transaction on the previous year's same date. so for achieving such requirements you can write MDX query by using Ancestor function. Refer sample MDX query for the same.

WITH 
  MEMBER [Measures].[Transaction of Selected Date] AS 
    (
      [Date].[Calendar].CurrentMember
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
  MEMBER [Measures].[Transaction of Previous Year's same Date] AS 
    (
      Cousin
      (
        [Date].[Calendar].CurrentMember
       ,Ancestor
        (
          [Date].[Calendar].CurrentMember
         ,[Date].[Calendar].[Calendar Year]
        ).Lag(1)
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Transaction of Selected Date]
   ,[Measures].[Transaction of Previous Year's same Date]
  } ON COLUMNS
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].[Date].[September 1, 2003];