August 24, 2009 at 7:47 am
I have a fact table that I am using to build a measure group in a cube.
The number of columns and names of these columns changes dynamically. I am using a named query to extract the results to the cube dynamically, but I need to be able to delete the measure group and re-create it as part of the processing package.
I can use SSIS tasks to delete the measure group for re-processing, but I am not sure how to re-create the measure group in SSIS.
I am not well versed in xmla... is there a way to build a dynamic ddl script that will accept paramters for the new columns within the query each time the cube is processed to create the measure group?
September 16, 2009 at 6:39 pm
First ... I guess you get the wrong idea about Business Intelligence/Data Warehouse (BI/DW). BI/DW is not an operational tools that could change dynamically, but an analysis tools. This means when you create BI/DW you already know exactly what you are going to analyze.
Maybe I can help you find an alternate solution if you could give me a little more detail about your problem.
Second ... You can use .NET to automatically create your SSIS package based on certain configuration. But before you can execute the package automatically, you must deploy the package first.
September 17, 2009 at 6:26 am
Hi, thanks for the reply, but you guess incorrectly. I am well aware of the intent of BI.
What is being analysed is a series of forecasted values. My client prepares multiple versions with unique names. Keeps them in a table and each scenario name is a unique column in that table... inserts another each time a new version is developed.
My solution transposes them to one column in a fact table with a genaric measure name, which is working fine... however the table is quite large and I was pursuing an alternative structure to reduce processing times on the ssis package and the cube.
September 17, 2009 at 8:56 am
Hey Jason,
Not saying it's exactly easy, but it's not too taxing either - if you go to your existing MG in SSMS, and right click, select Script-As Create-To New Query window. This will give you the XMLA for the create. Using this, you can either work out a way in SSIS to 'reach into' the XML and modify the nodes (e.g. using the XML DOM in a script task would pretty easily let you add/edit/delete nodes) and then use the contents in a SSAS DDL task.
HTH,
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply