February 23, 2005 at 7:27 am
I have a cube in which I need to repetitively add 150 + calculated members (Quarter to date, Year to Date, Previous Year Period, Previous Year YTD).
I have generated all the statements and names in a text editor and am in the process of adding all of these to my cube when I decided there might just be some sort of way to add these to the cubes via code. I know about MDX enough to know how to "create" cube columns in there, but these are not actually saved inside the cube so far as I can tell.
Is there anyway to script these changes? Wouldn't be so bad if it was just this cube, but I need to add most of these columns to each of the 30 or so cubes that are out there, and unless I get a legion of DBA's unexpectedly showing up at my door, I will be copying and pasting my butt off for the forseeable future.
Any help would be greatly appreciated!!
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
February 24, 2005 at 9:42 am
Don't get excited. I don't have a direct answer to your question. But I do have a suggestion for you from 30,000 feet. You may have fallen into a trap: analysis services as a substitute for reporting services. I know cubes can be great data sources for all kinds of things. But remember that they are primarily designed to be used for analysis. The fact that you have so many measures indicates to me that you have left the analysis arena. Don't forget about your schemas that are the source of your cubes. It is much easier to produce dozens of calculations from there. Don't let your users need for reported information drive cube design.
February 25, 2005 at 8:23 am
I agree about the trap part. My boss understands and I am to create two seperate versions of the cube, one with totals generated by BI Portal (the client is not using reporting services), and the other from formuals inside the cube. We are trying to get them to understand why it is not a good idea to use calculated totals.
But even without the totals, I still have things like tuples, parallelperiods, variances, std deviation in formuals that still give me cramps in my brain from copying and pasting so much.
Being generally lazy at heart - I have deeply embraced the concept of scripting things in SQL. After all - with all the tools I have - I can easily create a base formula and point it at a field list, and viola - there is a list of the calculated member formulas and names.
To me - even if it was just one calculated field it is still overly manual to have to type it in. I have to type it in my local dev area, then our main dev area, then over to our test area. Once it is approved internally, I then get to go through the same thing over on the client - local dev - dev - then acceptance. Finally, I need to make a release package for the client's Prod box to be released based on a schedule. Just thinking of all this copying and pasting is driving me nuts.
So unless I can get them to approve all the cubes at one time (it is more likely that I will get elected as the World President before this happens) so I can move the whole Analysis Services DB, it is 7 times the number of formulas that I will be typing things.
Please help!!!!
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
February 25, 2005 at 7:55 pm
You could try out the following tool, not supported by MSft but (co-)written by one of the MSft guys who co-wrote the performance and Ops guides (Dave Wickert). It basically scripts the structure as XML, which you could then of course copy and paste to, and then allows for the creations DSO objects (ie cubes, catalogs, calc members etc) from an input xml file.
for a quick reference on it's use, look to the Ops guide -> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx
tool link --> http://www.microsoft.com/downloads/details.aspx?FamilyID=8d9e7a70-eef4-44c3-a0c5-deece0f8b4b4&displaylang=en
I would seriously suggest trying this on your dev/test environment first.
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply