Hello,
I'm currently building partitions into my cube. My scenario is thus:
I have 74 measure groups (yeah I know - it sounds ridiculous but trust me, it IS valid)
Each measure group is to be partitioned by year
We are (currently) going to partition them over 12 years
In other words I have to build 74*12 = 888 partitions
Quite apart from the fact that its going to take most of my working week (and it'll be the most boring working week of my life) there is huge potential for human error here.
As stated above I am applying the same partitioning strategy for each of my 74 measure groups so I am repeating the same basic steps 74 times. It occurs to me that there MUST be a better way and if there isn't - there should be!
Does anyone know of a better way? I'd like to automate it somehow but I don't see how that is possible seeing as I have to edit a SQL statement for each one.
Anyone got any thoughts? I'm happy to provide feedback to MSFT about how this could be improved - contact me offline if interested - jamie.thomson[@.]nospam.conchango.com
ta
Jamie
I've done something similar using an SSIS package - I just scripted out the XML/A necessary to build a partition to use as a template, then created a FOR loop inside my package, used some script to generate the XMLA (and the SQL inside it) and then executed it. It worked pretty well, and I'll send you the code even though I know you're more than capable of doing this yourself. I agree it would be nice to have this functionality built into BIDS.
One thing that caught me here though was the problem with setting the slices for partitions that came up at the BI evening we were at, and which is discussed on Mark Hill's blog:
http://markhill.org/blog/?p=11
I created around 600 partitions on my cube and some of them turned out to have very little or no data in; this meant that no slicers got set automatically and my query performance suffered as a result.
Chris
|||[Chris sent me this stuff offline]
Cheers Chris. That's good stuff and works well.
HOWEVER, this only works once the cube has been deployed. I want the partitions to appear in my BIDS solution (I should have said that originally - sorry about that). Is there a way to reverse-engineer them back in?
-Jamie
|||
Yes, you can reverse engineer a project from a deployed database very easily. All you need to do is open BIDS, then File/New/Project and select "Import Analysis Services 9.0 Database".
Chris
|||After looking at Chris' package its just occurred to me....hod useful would it be to have an AMO Enumerator in the SSIS ForEach loop?
That would be brilliant!! Microsoft Connect here I come!!!
-Jamie
|||
Jamie Thomson wrote:
After looking at Chris' package its just occurred to me....hod useful would it be to have an AMO Enumerator in the SSIS ForEach loop?
That would be brilliant!! Microsoft Connect here I come!!!
-Jamie
And here it is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=163202
Click through and vote would you please!!! And leave a comment. Much more likely to get it that way!
|||Hi Jamie:
You could probably have relational one-to-one for your partitions. That way - you could iterate that in SSIS and conditionally create the partitions if required - and then process them.
Hope that helps.
Suranjan
No comments:
Post a Comment