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.