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
Cube data into SQL table columns
-
- Community Contributor
- Posts: 341
- Joined: Wed Nov 03, 2010 9:16 pm
- OLAP Product: tm1
- Version: 10 2 2 - 2.0.5
- Excel Version: From 2007 to 2013
- Location: Earth
Re: Cube data into SQL table columns
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
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
-
- MVP
- Posts: 3128
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Cube data into SQL table columns
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.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Community Contributor
- Posts: 341
- Joined: Wed Nov 03, 2010 9:16 pm
- OLAP Product: tm1
- Version: 10 2 2 - 2.0.5
- Excel Version: From 2007 to 2013
- Location: Earth
Re: Cube data into SQL table columns
oh yes, now that you write this Wim, I remember I read it somewhere here years ago, and a really nice trick
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Cube data into SQL table columns
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.
-
- Posts: 5
- Joined: Thu Apr 30, 2020 3:32 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010 + 2016
- Location: UK
Re: Cube data into SQL table columns
Thank you, your suggestion worked well. I've managed to push data into SQL table in way I wanted.mvaspal wrote: ↑Tue Jul 14, 2020 9:50 am 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
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?
-
- MVP
- Posts: 3128
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Cube data into SQL table columns
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.
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.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Cube data into SQL table columns
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.