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 ....
*********************************************************************************
No comments:
Post a Comment