Load CUBE data to SQL table using Script task

Recently I faced an issue while working with SSAS 2008 cube. I was using a data flow task and inside that I have OLEDB source (The Analysis Services 9.0 OLE DB Provider (msolap90.dll)) for fetching data using MDX query. I was running this package in BIDS, its working fine. But as I was trying to run it with DTEXEC command, it’s throwing following error:


• ERROR message:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E05. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2008." Hresult: 0x00000001 Description: "Error Code = 0x80040E05, External Code = 0x00000000:.".


Component "OLE DB Source" (2297) failed the pre-execute phase and returned error code 0xC0202009.

Environment:
SSIS package on SQL Server 2005. BIDS 2005
SSAS cube on SQL server 2008.


Solution:
I have tried many possible solutions but no success. Finally I wrote a script task code which is connecting to CUBE and dumping the cube data in flat file with comma delimited format. Later on I used this flat file to laod data in SQL table.

I would like to share this code, may this helps other.

Important: Please add the "Microsoft.AnalysisServices.AdomdClient.dll" dll to your reference.


Microsoft.AnalysisServices.AdomdClient.dll is available under the link below,http://www.microsoft.com/downloads/en/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en
Download "Microsoft ADOMD.NET " from the link above and after running msi file you will get
Microsoft.AnalysisServices.AdomdClient.dll under folder.
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.AnalysisServices.

**********************************************************
Public Sub Main()
Dim serverName As String = "Manish"
Dim databaseName As String = "CRM"
Dim databaseID As String = databaseName
Dim cn As String = "Provider=MSOLAP;Data Source=" & serverName & ";Initial Catalog=" & databaseName
Dim connex As New AdomdConnection(cn)

connex.Open()

Dim commandText As String

commandText = "SELECT [Country].[Country Code].MEMBERS ON 0, [Time].[Gregorian Calendar].[Year] ON 1 FROM [CUBE2008];"

Dim cmd As AdomdCommand = New AdomdCommand(commandText, connex)
Dim dr As AdomdDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

' output the rows in the DataReader
Using writer As StreamWriter = New StreamWriter("c:\myfile.txt")
While dr.Read()
For i As Integer = 0 To dr.FieldCount - 1
If (dr(i) Is Nothing) Then
writer.Write(" , ")
Continue For
Else
writer.Write(dr(i).ToString() + " , ")
End If
Next
writer.WriteLine()
End While
End Using
dr.Close()

Dts.TaskResult = Dts.Results.Success

End Sub

***************************************************************

Let me know your valuable comment to make this post better.

Processing Options for SSAS Objects

PROCESSING OPTION SSAS2008

Process Default: (Applicable for **All objects)
Performs the minimum number of tasks required to fully initialize the object. The server converts this option to one of the other options based on the object state.

Process Full (Applicable for All objects)
Drops the object stores and rebuilds the object. Metadata changes, such as adding a new attribute to a dimension, require Process Full.

Process Update (Only for Dimensions) 
Applies member inserts, deletes, and updates without invalidating the affected cubes.

Process Add (Only for Dimension & Partition)
Adds only new data.

Process Data (Dimension, cube, measure group, partition)

Loads the object with data without building indexes and aggregations.

Process Index(Dimension, cube, measure group, partition)
Retains data and builds only indexes and aggregations.

Unprocess (All Objects) 
Deletes the object data or the data in the containing objects.

Process Structure ( Only CUBE)
Deletes the partition data and applies Process Default to the cube dimensions.

------------------------------------------------------------

**SSAS objects list

• Database
• Dimension
• Cube
• Measure group
• Partition
• Mining structure
• Mining model

What is Factless Fact table?

Today, I faced an interview for MSBI Developer. The guy asked me many questions and most of them i answered perfectly. The Guy asked me a question on factless fact table. I like to share it with you, if you are not already familiar with it.

Question: What is FACTLESS Fact table?

Answer:

The “Factless Fact Table” is a table which is similar to Fact Table except for having any measure; I mean that this table just has the links to the dimensions. These tables enable you to track events; indeed they are for recording events.


Factless fact tables are used for tracking a process or collecting stats. They are called so because, the fact table does not have aggregatable numeric values or information. They are mere key values with reference to the dimensions from which the stats can be collected