Simple Ways to Increase Cube Performance

There are Few Simple Steps to Increase the Performance of a Cube:
-----------------------------------------------------------------
To Decrease the Cube Processing Time :
--------------------------------------
1>The Default Member of the Cube (usually First Member of the First Measure Group)in the Cube has Large Data Spanning to Millions of Records and Your Client is Using Excel ,Reporting Services as Client Tool then Set the Null or 0 as per Experts as Default Member and Once you Pull Your Dimension Member it will not be Stuck for a Long time ! :)
2>Set the Attribute Hierchy Enabled,Is Aggregatable to Fasle as it will lead to Unnecassry Calculation Build up as it will never be used.
For ex:If you have Employee Address as Property ..and u have a report Showing to the CEO he will Never analyse by Email ID...And if you set this to true You are Tried to Set a All Level for a Default Measure...Waste of Processing Time...So Be careful and Be Smart on Chosing this Option !
To Increase Query Performance of the Cube:
--------------------------------------------
1>Create Partitions on Your Fact Table...Maro Goli If you have Fact Table less then 10 million Rows if its more try making Partitions.
2>Fine Tune Your MDX Quieries inside Your Cube.
3>Design Aggregations for Each Partition in Your Cube(dont Forget Never Design Aggregations to Leaf Members as your Cube Processing Time Will Increase Rapidly)
4>If You have Reports Which Show the Same Data (Like a Pie,Bar chart or some Tablix) where Quieries are Written in your Report and Reporting Services will in turn Ask your Analysis Query Engine then do Use User based Optimization in Your Cube by logging Your Quieries !!!!
5>Have Your Members Unique Name to be displayed Which will optimise your Dimensions.


I Will Be coming Back With More Updates !....Keep Waiting :)

Load Cube Metadata Into SQL database

Howdy Folks !
Hope this will be Helpful

Way of Finding Cube Meta Data Using SSIS Script Task
Context: To Find the Metadata about the Analysis Services Cube Many Approaches can be taken.
This Approach Doesn’t Store Cube Metadata in the Cube itself as Hidden Measure but in a Log Table Which is More Accessible.
Step 1: Create a New table in SQL Database
TABLENAME(LOG.CUBEMETADATA)
Column Name LastProcessedDate LastSchemaUpdate RequiredFurtherParameter*
Column Type String String As per Requirement

Step 2: Create a New Script Task in SSIS Package.
Create a new ADO.net Connection in Your Package before you do this 
Don’t Forget to Add Required Assemblies in Script Task
(Copy Paste the Code in Script Task )
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices
Imports System.Data.SqlClient
Imports System.Xml

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Dim asServer As Server = New Server()
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As SqlDataAdapter
Dim Query As String
Dim CubeLastProcessed As String
Dim LastCubeSchemaUpdate As String
Dim StrLastProcessed As String
Dim StrLastSchemaUpdate As String
Public Sub Main()

'For Logging all the Connection Managers in the Existing Package-Omit if You Dont Need it
If Dts.Connections("(YourADO.net Connection Name)").ConnectionString Is Nothing Or Dts.Connections("(YourADO.net Connection Name)").ConnectionString = String.Empty Then
MsgBox("No Connection In Package found")
Else
connection = CType(Dts.Connections("(YourADO.net Connection Name)").AcquireConnection(Dts.Transaction), SqlConnection)
End If

asServer.Connect(Dts.Connections("(Your Analysis Services Connection Name)").ConnectionString)

'If Your Database Has Many Cubes find the RElevent cube you Need
For Each cube As Cube In asServer.Databases("(Your Analysis Services Database Name)")
If (cube.Name = "(Your Cube Unique Name)") Then
LastProcessed = cube.LastProcessed
LastSchemaUpdate = cube.LastSchemaUpdate


Else
Me.LogMessage("No Update Found !")
End If
Next

StrLastProcessed = (CType(CubeLastProcessed, String))
StrLastSchemaUpdate = (CType(LastCubeSchemaUpdate, String))
Query = "Insert into LOG.CUBEMETADATA(LastProcessedDate,LastSchemaUpdate) Values ('" & StrLastProcessed & "','" & StrLastSchemaUpdate & "')"
command = New SqlCommand(Query, connection)
command.ExecuteReader()
Dts.TaskResult = Dts.Results.Success
End Sub

End Class

Step3:Change the variable Names As per Requirement.
Step 4:run and Voila Your Table has been Updated ! 

.dll to Find Current Month

Here is My thought on using .dll to find the Current Month in an Easy Way !

Way of Finding Current Month Using SSAS Assemblies
Context:Instead of Burdening Your SQL Engine and Analysis Engine to get the CurrentMonth by Creating a View and Adding the Attribute as Property which will Have Performance issues if you have Large Date Values.
This Method Will Create A Assembly in SSAS and Fetches the Member within Lesser Duration.
Step 1 :Create A Library Class .dll in the Visual Studio
Step 2 : Write a Query in the Format you need it as a String.
Ex:If your Date Hiercherchy Has Something Like
[Time].[Calender].[Month].&[2010]&[4] then Construte a String the Required Format using System.Date.Time Functions which will Be a String Exact to Unique Name of the Date Member.
Step 3 :Crete a set in the Cube
Create Member CurrentCube.[CurrentMonth]
<>
Visible=1) ;

Step 4:
In Your Quieries Just Convert String to Member using StrtoMember([CurrentMonth])
Note: No Need to Process the Cube if you’re Date Changes and Your Data is the same as The above Query is Independent of Analysis Services and Uses .dll to link to System date and fetch the[ Current Month].
Step 5: Another Interesting Way is to create it as Set and Use it but I found Out it doesn’t Become Dynamic and Cube Needs to be processed.

********************************************************************************
Heres a Thought on the Above Method...If You have a Database and need to look up something and Create a MDX this Method will be Quiet Useful ....
*********************************************************************************