ssas

Accessing Member Properties on Parent-Child Hierarchies from SSRS Reorts

1) Edit MDX and add properties to DIMENSION PROPERTIES of the axis as below…..

SELECT	non empty {[Measures].[PnL],[Measures].[PL Limit]} ON COLUMNS , 
		NON EMPTY [Organization].[MyTree].members
   DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Organization].[MyTree].[My Level Name] ON ROWS  
   FROM [MYCUBE] 
   WHERE ([Date].[Date].&[20141210]) 

[Organization].[MyTree].[My Level Name] is a property of the MyTree at any given level.

2) SSRS query designer will automatically add a field (“My_Level_Name”) to the dataset, but it doesn’t show any data when we add the field to the Table (only in this case of Parent-Child Hierarchy, it works fine in other cases), so it is of no use to us, we might as well bin it.

3) When we need to access Member properties of the tree, we can use expression something like this….
=Fields!MyTree(“My Level Name”)

(Same as retrieving Cell Properties….=Fields!FieldName(“FORMAT_STRING”))

Tags: , , ,

Monday, December 15th, 2014 SSRS No Comments

Generating large numbers of SSAS partitions using Excel

Link to Chris Webb’s BI Blog page:

http://cwebbbi.wordpress.com/2008/08/27/generating-large-numbers-of-partitions-using-excel/

Tags: , ,

Friday, March 2nd, 2012 ssas No Comments

Process SSAS Measure Groups (Fact tables) using SSIS

for my own reference:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices

Public Class ScriptMain
Public Sub Main()
Dim strFactNames As String = Dts.Variables("FactNames").Value.ToString
Dim strCubeName As String = Dts.Variables("CubeName").Value.ToString
Dim bt(0) As Byte
Dim ExecutionSuccessfull As Boolean = True
Dim oConnection As ConnectionManager

oConnection = Dts.Connections("SSAS")
Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
Dim sDatabaseID As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))

Dts.Events.FireInformation(-1, "Fact Tables to process: ", strFactNames, "", -1, True)
Dts.Log("Fact Tables to process:" + strFactNames, 0, bt)

Dim oServer As New Server
oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
Dim oDB As Database = oServer.Databases.FindByName(sDatabaseID)
Dim oCube As Cube = oDB.Cubes.FindByName(strCubeName)

Dim ProcessType As ProcessType
ProcessType = ProcessType.ProcessFull

If oDB Is Nothing Or oCube Is Nothing Then
ExecutionSuccessfull = False
GoTo Done
Else

Dim oFact As MeasureGroup

oServer.CaptureXml() = True ' Start capturing XML.
For Each oFact In oCube.MeasureGroups
' This will generate XMLA, but because CaptureXML is True, will not execute it!
If InStr(1, strFactNames, oFact.Name.Replace(" ", "")) > 1 Then
oFact.Process(ProcessType)
End If
Next
oServer.CaptureXml() = False ' Stop capturing XML

' Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected
Dim oResults As XmlaResultCollection
Dim oResult As XmlaResult

Dts.Log("Processing Facts Now", 0, bt)
Dts.Events.FireInformation(-1, "Process Facts Script: ", "Processing Facts Now", "", -1, True)
oResults = oServer.ExecuteCaptureLog(True, True, True)

Dim oMessage As XmlaMessage

'Log the errors and warnings
For Each oResult In oResults
For Each oMessage In oResult.Messages
If oMessage.GetType.Name = "XmlaError" Then
Dts.Log("XMLA Error while processing facts:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Error while processing facts:", oMessage.Description, "", -1, True)
'The processing failed
ExecutionSuccessfull = False
Else
'It's just a warning.
Dts.Log("XMLA Warning while processing facts:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Warning while processing facts:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = True ' if you want to fail on warning, change this to False
End If
Next oMessage
Next oResult
End If
Done:
oServer.Disconnect() ' disconnect from the server -- we are done

If ExecutionSuccessfull Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub

End Class

Tags: , ,

Friday, March 2nd, 2012 ssas, SSIS No Comments

Process SSAS Dimensions using SSIS

for my own reference:

populate DimensionNames var from a exec sql task:

Select CAST(REPLACE(STUFF(
(SELECT ',' + a.tablenamecube AS [text()]
from config.BusinessprocessTables a
WHERE TableType ='D' AND BusinessProcess = 'Metrics' for xml PATH('')),1,1,'' ),' ','') AS VARCHAR(1000)) AS DimensionNames


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices

Public Class ScriptMain
Public Sub Main()
Dim strDimensionNames As String = Dts.Variables("DimensionNames").Value.ToString
Dim bt(0) As Byte
Dim ExecutionSuccessfull As Boolean = True
Dim oConnection As ConnectionManager
oConnection = Dts.Connections("SSAS")
Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
Dim sDatabaseID As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))

Dts.Events.FireInformation(-1, "Dimensions to Process:", strDimensionNames, "", -1, True)
Dts.Log("Dimensions to Process:" + strDimensionNames, 0, bt)

Dim oServer As New Server
oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
Dim oDB As Database = oServer.Databases.FindByName(sDatabaseID)

Dim ProcessType As ProcessType
ProcessType = ProcessType.ProcessFull 'ProcessUpdate

If oDB Is Nothing Then
ExecutionSuccessfull = False
GoTo Done
Else
Dim oDim As Dimension

oServer.CaptureXml() = True ' Start capturing XML.
For Each oDim In oDB.Dimensions
' This will generate XMLA, but because CaptureXML is True, will not execute it!
If (oDim.MiningModel Is Nothing) Then
If InStr(1, strDimensionNames, oDim.Name.Replace(" ", "")) > 1 Then
oDim.Process(ProcessType)
End If
End If
Next
oServer.CaptureXml() = False ' Stop capturing XML

' Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected
Dim oResults As XmlaResultCollection
Dim oResult As XmlaResult

Dts.Events.FireInformation(-1, "Process Dimensions script", "Processing Dimensions ow", "", -1, True)
Dts.Log("Processing Dimensions Now", 0, bt)
oResults = oServer.ExecuteCaptureLog(True, True, True)

Dim oMessage As XmlaMessage

'Log the errors and warnings
For Each oResult In oResults
For Each oMessage In oResult.Messages
If oMessage.GetType.Name = "XmlaError" Then
Dts.Log("XMLA Errors while processing Dimensions:" + oMessage.Description, 0, bt)
'The processing failed
Dts.Events.FireInformation(-1, "XMLA Errors while processing Dimensions:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = False
Else
'It's just a warning.
Dts.Log("XMLA Warnings while processing Dimensions:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Warnings while processing Dimensions:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = True ' if you want to fail on warning, change this to False
End If
Next oMessage
Next oResult
End If
Done:
oServer.Disconnect() ' disconnect from the server -- we are done

If ExecutionSuccessfull Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub

End Class

Tags: , ,

Friday, March 2nd, 2012 SSIS No Comments