Combine multiple cubes and re-number

Post Reply
imacanuk
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

Post by imacanuk »

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:
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.
BariAbdul
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

Post by BariAbdul »

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
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......"
User avatar
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

Post by Steve Rowe »

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
Technical Director
www.infocat.co.uk
imacanuk
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

Post by imacanuk »

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
Attachments
Cube Examples.docx
(219.53 KiB) Downloaded 173 times
imacanuk
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

Post by imacanuk »

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 ?
BariAbdul
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

Post by BariAbdul »

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
"You Never Fail Until You Stop Trying......"
Post Reply