"Publish" 3 or more dimensional cube from TM1 to SQL server

[jimicron]

Hi all,

I currently have 4 two dimensional cubes that I "publish" (to use Cognos Analyst terminology) (write to a SQL server) on a daily basis. I have these on a scheduled shore and it works great - and is MUCH faster than doing it in Analyst.

Now, I'd like to "publish" a five dimension cube and I am unsure of how to do it.

I created detail instructions on publishing a 2 dimension cube but am wondering if it's a simple tweak to what I already do? Or is it totally different?

I've attached the document on publishing a 2 dimension cube... with the hopes that it will help others... but again, what I want to do is publish a five dimension cube now...

My cube has the following dimensions:
1) Site
2) Part Number
3) Expense Type
4) Months
5) Measures

Thanks a lot!!

[jimicron]

I'm not sure why... but I got an email notification that Tomok had replied... but after refreshing, etc., his reply isn't showing up. He didn't send me a PM so I will paste his response here:

From Tomok:

It depends on what you mean by "publish", and what your expected use of the "published cube' table. If the intent is to just dump the contents of a cube to a SQL table it really doesn't matter how many dimensions there are, the logic is the same. You'll end up with one record for each leaf-level intersection in the cube that has data (assuming you are skipping consolidations and zero suppressing). With one column for each dimension element name and one column for the value in the intersection. It only gets complicated when you want to have one column for each dimension, except for Measures where you want each measure to be a separate column.
What I do in this case is to write a custom stored procedure that will tell me if the record already exists in the table. I call this function on the Data tab and if it returns a 0 (meaning it doesn't exist) then I INSERT a new record into the table. Then just below I UPDATE the table for the value in the TI record. What your TI will do is then for each intersection from the View (your data source), check to see if the record exists in SQL, add it if it doesn't, and then UPDATE that record for the value from the TI.

To answer that, I am trying to do what you state in your first paragraph - only exporting detail (not consolidations) and also zero supressing.

Where I am struggling right now is how to do the Variables tab and then the Data tab in the TI process. I am sure I am close because I have done this for 2 dimension cubes... but there is something I am missing. Sigh.

I want it to look like Illustration B below where the different measures are my columns vs Illustration A.

Thanks a lot!

[declanr]

Just pull one measure in the data source and then do a CellGetN or CellGetS for the other measures.
Remember not to do zero supression though in case the measure you chose for your source doesn't have a value but the other measures do.

[tomok]

I removed my post because it was incorrect. The proposed solution I gave actually requires more work than I originally indicated and I didn't think it was the best solution. The best route is what declanr suggested, however, I would improve it by adding a consolidated element to the Measures dimension that adds all the measures together and use that node in your view, with zero suppression. That will give you all the intersections that have data. Then in the Data tab you can do a separate CellGetN for each of the individual children of that consolidated measure with an IF statement that does the SQL INSERT when the result of the CellGetS is <> 0. If you have a large cube, just cycling through the entire thing without zero suppression, hunting for values, may not be the best thing performance-wise.

[jimicron]

Thankg guys!
I don't think a CellPutN will work here b/c my Site dimension has about 10 things in it, my Expense Type has about 15, my Months has over 6 years in it, and the Part Number list has around 8000 parts in it. Thus, I'm not able to point to a specific cell.

If I could say something like DB(!Month, !Site, !Expense Type, !Part Number, 'Measure A') that would be nice. But it doesn't allow that in the variables formula.

This would be so simple in Analyst.

I have it where it works... but it's being mutally exclusive on the values (measures)... and with the size of this.. it's going to needlessly eat up a lot of database space. Thus, why I was trying to just get it to populate all measures for one month.

Thanks again.

[declanr]

I don't think a CellPutN will work here b/c my Site dimension has about 10 things in it, my Expense Type has about 15, my Months has over 6 years in it, and the Part Number list has around 8000 parts in it. Thus, I'm not able to point to a specific cell.

If I could say something like DB(!Month, !Site, !Expense Type, !Part Number, 'Measure A') that would be nice. But it doesn't allow that in the variables formula.

Now come on, I can't believe for 1 second that in the course of your almost 100 posts we haven't at least touched on how TI variables work!

If your source cube has 5 dimensions and the first 4 of them you select showing all elements (or all leaf elements etc) and the 5th is your measures pointed to a specific element. In your variables tab call them V1 through to V5 Respectively.

Code: Select all

nValue = CellGetN ( sCub, v1, v2, v3, v4, 'Measure' );

This would be so simple in Analyst.

You do have my sympathies that it's not quite as intuitive a tool as analyst for people who want to cross over (or are forced to) but I wouldn't say it's that far beyond simple in TI.