Cube data into SQL table columns

[Markuss]

Hi,

I'm new to TM1 world, wonder if someone can help solve this. I've created Ti process that writes data into SQL table, which is fine. However my measures all gets written in same column.
Cube contains 4 dimensions. From measure dimension I need 5 elements to be into individual columns and values underneath for each product number.

This is what I currently have in SQL table:

Version |ProductNum |Description |Category |Measures |WeekNum |Value
Forecast 1234567 Product Description Product Cat Measure 1 202012 0.45
Forecast 1234567 Product Description Product Cat Measure 2 202012 1.34
Forecast 1234567 Product Description Product Cat Measure 3 202012 3.25
Forecast 1234567 Product Description Product Cat Measure 4 202012 0.98
Forecast 1234567 Product Description Product Cat Measure 5 202012 1.23


This is what I'm trying to archive: Note that value is now for each measure.

Version |ProductNum |Description |Category |Measure1 |Measure2 |Measure3 Measure4 |Measure5 | WeekNum
Forecast | 1234567 |Product Description | Product Cat | 0.45 | 1.34 | 3.25 | 0.98 | 1.23 | 202012
Forecast | 8912345 | Product Description |Product Cat | 0.46 | 1.35 | 3.27 | 0.92 |1.22 | 202012


Hope this make sense. Thanks in advance

[mvaspal]

Hi,

In this case you may want to create a C: element, which sums up all the 5 measures, and use that in the cube view data source
Then on the Data tab:
vMeasure1 = CellGetN(Cube,...,Measure1)
...

So 5x CellGetN from the cube, and then 1x ODBC Output in the Data tab

This will work fine as long as you do not have an exceptional case when the sum of the measures on one record would be 0

[Wim Gielis]

1 trick I picked up from this forum, is to have indeed a C element with 5 children, but assign the children with a random weight. This reduces the chance of 0 at the C level even more.

[mvaspal]

oh yes, now that you write this Wim, I remember I read it somewhere here years ago, and a really nice trick

[tomok]

If you don't want to change anything in TM1 you can also modify your SQL to accomplish the same thing. In your SQL you have an IF EXISTS that searches for the existence of the record and, if it does not exist, adds it with no values for the measures. Then below that you do an UPDATE that updates the specific measure in question. It will definitely be less efficient from an SQL perspective but you won't have to rig TM1 for this. Depending on how much data you are talking about it may be a better solution.

[Markuss]

Hi,

In this case you may want to create a C: element, which sums up all the 5 measures, and use that in the cube view data source
Then on the Data tab:
vMeasure1 = CellGetN(Cube,...,Measure1)
...

So 5x CellGetN from the cube, and then 1x ODBC Output in the Data tab

This will work fine as long as you do not have an exceptional case when the sum of the measures on one record would be 0

Thank you, your suggestion worked well. I've managed to push data into SQL table in way I wanted.

One more thing to clarify: Does it makes any difference where I create measure variables? By that I mean - variable tab or Data tab.

What I did is in variable tab I created all five measures with formulas in them (example: vMeasure1=CellGetN('My Cube',vWeek,vVersion,vProduct,'Units per Box'); )


I can see that all my five measure variables has been generated across Metadata & Data tabs.

#****Begin: Generated Statements***
Measure 1
Measure 2
....
#****Begin: Generated Statements***

Would that be reason why I can see multiple times repeated each product in my SQL table?

[Wim Gielis]

The ODBCoutput line should only be in either Metadata or Data tab, and Data tab makes more sense.

If you define variables in the Variables tab, you should indicate that you only need them in the Data tab (in this case). If not, TM1 will iterate over the data source for both Metadata and Data tab where this is a waste of time for the Metadata tab if nothing happens except retrieving cube data.

[tomok]

The view used as the data source in your TI process needs to be configured to only have the C level measure in it. No need to have all the individual measures in it since you are going to retrieve them with the CELLGET.