I need to create one single cube which is a combination of 4 separate cubes that all use a line number dimension, so there are multiple 1s, 2s, 3s etc.
I cannot group them together by dimension since there are vendor and comments associated with each individual line so I need to maintain each as a separate line and then re-number (based on some additional criteria) as they flow into the main cube
The end result of the one main cube is an ASCIIOUTPUT file ?
I can certainly do this in SQL but am new to TM1 so have really no clue how to do this in TurboIntegrator. Can anyone provide some assistance as to how I would approach this ?
Here are examples of 2 of the 4 cubes:
Combine multiple cubes and re-number
-
- Posts: 4
- Joined: Wed Feb 22, 2017 1:18 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Combine multiple cubes and re-number
- Attachments
-
- Cube Examples.docx
- (219.53 KiB) Downloaded 193 times
Last edited by imacanuk on Wed Feb 22, 2017 7:23 pm, edited 1 time in total.
-
- Regular Participant
- Posts: 424
- Joined: Sat Mar 10, 2012 1:03 pm
- OLAP Product: IBM TM1, Planning Analytics, P
- Version: PAW 2.0.8
- Excel Version: 2019
Re: Combine multiple cubes and re-number
You explanation of situation need bit more effort in describing as TM1 guru Alan Kirk said in below link:
http://www.tm1forum.com/viewtopic.php?t=11374
Thanks
http://www.tm1forum.com/viewtopic.php?t=11374
I think that you would need to be a lot more specific than that. You haven't indicated whether you want to do this through Ti or through rules, or what the relationship is between the data in the first two cubes and the third one. You haven't given an outline of the cube structures and their dimensions, nor what the level of granularity (dimensionality) is in the three cubes.
Thanks
"You Never Fail Until You Stop Trying......"
- Steve Rowe
- Site Admin
- Posts: 2410
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Combine multiple cubes and re-number
Can I suggest that you remove your doc and replace with one that has the numbers hidden, you appear to be posting real information.
One way of doing this is to create a dimension that contains the current cube names and put this in your new cube.
You'd then need to blend your measures dimension so that it contains all the measures of the 4 other cubes.
but....
The key points are
1. What is the business requirement for this? Doing what you propose doesn't seem to deliver obvious benefits to the end user.
2. Why do you have these tables in TM1 in the first place? It is not impossible for this to be correct but for me is an early indicator that the design and approach is wrong, impossible for me to say more with the information provided.
HTH
One way of doing this is to create a dimension that contains the current cube names and put this in your new cube.
You'd then need to blend your measures dimension so that it contains all the measures of the 4 other cubes.
but....
The key points are
1. What is the business requirement for this? Doing what you propose doesn't seem to deliver obvious benefits to the end user.
2. Why do you have these tables in TM1 in the first place? It is not impossible for this to be correct but for me is an early indicator that the design and approach is wrong, impossible for me to say more with the information provided.
HTH
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 4
- Joined: Wed Feb 22, 2017 1:18 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: Combine multiple cubes and re-number
Thanks for the suggestions, I will provide more details.
The cubes are published as web sheets and are separated because of security. The end use of this to format an ASCIIOUTPUT file so that it can be imported by their financial ERP system. The reason all the of data has to reside in one cube is that the elements need to be grouped together where there are similar dimensions and then numbered accordingly. For example, there are labor costs in Cube 2 for the same client, scope, facilility and Cost_Element_type as in Cube 1, so those 2 sets of data would be groupted together and then numbered in order.
I have already created one cube that summarizes all of the data in the 4 separate cubes but that was easier as I could group/sum on dimension(s). This new requirement needs line item detail.
Here are the dimensions in each cube:
Cube 1: Cost Budget
Scenario, Calendar_Year, Line_Number, Client and Input_Cost _Measure (picklists for Scope, BOMA Category, BOMA, Cost_Element_Type) Jan Thru Dec and free form Comment)
Cube 2: Other Cost Budget
Scenario, Calendar_Year, Data_Type, Line_Number, Site, Input_Cost _Budget_Measure (picklists for Scope, Client, Facility, BOMA Category, BOMA, Cost_Element_Type Jan thru Dec and free form Vendor and Comment
Cube 3: Other Lines of Business
Scenario, Calendar_Year, Line_Number, Client, Input_Other_Budget_Measure (picklists for LIne of Business, Data_Category) Jan thru Dec and and free form Comment
Cube 4: Revenue
Scenario, Calendar_Year, Line_Number, Client, Input_Revenue _Budget_Measure (picklists for Scope, Revenue_Source_Type) Jan thru Dec and and free form Comment
The numbering requirements are as follows:
Fixed width of 7 characters:
Char Position 1: 1 where Data_Catagory in (subcontractor or Materials) and Scope is (Inscope) : Cubes 1, 2 and 3 could have similar data_types
Char Position 1: 2 where Data_Catagory in (Labor) and Scope is (Inscope) : Cubes 1, 2 and 3 could have similar data_types
Char Position 1: 3 where Data_Catagory in (Total Cost) and Scope is (Out of scope) : Cubes 1, 2 and 3 could have similar data_types
Char Position 1: 4 where Data_Catagory in (Total Revenue) and Scope is (Out of scope) : Cubes 1, 2 and 3 could have similar data_types
Char positions 2-7 will be the chronological line numbers and will restart at 1 when the Client changes
As for rules or feeders, I am open to the best approach as this cube will only be used to summarize all the data and then exported to an ASCII file. It will not be an end user cube nor will it be visible to any end users.
I have also edited attached file to add more details and blank out the actual data
The cubes are published as web sheets and are separated because of security. The end use of this to format an ASCIIOUTPUT file so that it can be imported by their financial ERP system. The reason all the of data has to reside in one cube is that the elements need to be grouped together where there are similar dimensions and then numbered accordingly. For example, there are labor costs in Cube 2 for the same client, scope, facilility and Cost_Element_type as in Cube 1, so those 2 sets of data would be groupted together and then numbered in order.
I have already created one cube that summarizes all of the data in the 4 separate cubes but that was easier as I could group/sum on dimension(s). This new requirement needs line item detail.
Here are the dimensions in each cube:
Cube 1: Cost Budget
Scenario, Calendar_Year, Line_Number, Client and Input_Cost _Measure (picklists for Scope, BOMA Category, BOMA, Cost_Element_Type) Jan Thru Dec and free form Comment)
Cube 2: Other Cost Budget
Scenario, Calendar_Year, Data_Type, Line_Number, Site, Input_Cost _Budget_Measure (picklists for Scope, Client, Facility, BOMA Category, BOMA, Cost_Element_Type Jan thru Dec and free form Vendor and Comment
Cube 3: Other Lines of Business
Scenario, Calendar_Year, Line_Number, Client, Input_Other_Budget_Measure (picklists for LIne of Business, Data_Category) Jan thru Dec and and free form Comment
Cube 4: Revenue
Scenario, Calendar_Year, Line_Number, Client, Input_Revenue _Budget_Measure (picklists for Scope, Revenue_Source_Type) Jan thru Dec and and free form Comment
The numbering requirements are as follows:
Fixed width of 7 characters:
Char Position 1: 1 where Data_Catagory in (subcontractor or Materials) and Scope is (Inscope) : Cubes 1, 2 and 3 could have similar data_types
Char Position 1: 2 where Data_Catagory in (Labor) and Scope is (Inscope) : Cubes 1, 2 and 3 could have similar data_types
Char Position 1: 3 where Data_Catagory in (Total Cost) and Scope is (Out of scope) : Cubes 1, 2 and 3 could have similar data_types
Char Position 1: 4 where Data_Catagory in (Total Revenue) and Scope is (Out of scope) : Cubes 1, 2 and 3 could have similar data_types
Char positions 2-7 will be the chronological line numbers and will restart at 1 when the Client changes
As for rules or feeders, I am open to the best approach as this cube will only be used to summarize all the data and then exported to an ASCII file. It will not be an end user cube nor will it be visible to any end users.
I have also edited attached file to add more details and blank out the actual data
- Attachments
-
- Cube Examples.docx
- (219.53 KiB) Downloaded 173 times
-
- Posts: 4
- Joined: Wed Feb 22, 2017 1:18 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: Combine multiple cubes and re-number
Ok so I have come to the conclusion that this will never work using TI processes solely. So I was thinking about either one of these approaches:
1) Export the 4 cubes as text files
2)Use a powershell functions to combine them, sort and re-number
3) Import back into TI, perform the lookups and formatting
4) Export ASCII file from TI
or:
1) Export the 4 cubes into a table in Oracle
2) Execute a stored proc to perform all of the ETL
3) Import back into TM1 using T1, perform lookups and formatting
4) Export ASCII file from TI
I have never used PowerShell or called and Oracle stored proc from TI. Has anyone had experience doing either ?
1) Export the 4 cubes as text files
2)Use a powershell functions to combine them, sort and re-number
3) Import back into TI, perform the lookups and formatting
4) Export ASCII file from TI
or:
1) Export the 4 cubes into a table in Oracle
2) Execute a stored proc to perform all of the ETL
3) Import back into TM1 using T1, perform lookups and formatting
4) Export ASCII file from TI
I have never used PowerShell or called and Oracle stored proc from TI. Has anyone had experience doing either ?
-
- Regular Participant
- Posts: 424
- Joined: Sat Mar 10, 2012 1:03 pm
- OLAP Product: IBM TM1, Planning Analytics, P
- Version: PAW 2.0.8
- Excel Version: 2019
Re: Combine multiple cubes and re-number
Personally,I would prefer option1,Anyway please look at below links for further information on power shell and stored procedures with respect to TM1 :
http://www.tm1forum.com/viewtopic.php?t=12453
https://everanalytics.wordpress.com/201 ... rocedures/ Thanks
http://www.tm1forum.com/viewtopic.php?t=12453
https://everanalytics.wordpress.com/201 ... rocedures/ Thanks
"You Never Fail Until You Stop Trying......"