How to source output of one build or datastream into another build or datastream

[MPK25]

I have 3 products. Product 1, product 2, and product 3.Ã,  Each of these products has two items say item A and item B.Ã,  I have a time period of 5 years.Ã,  I need to show percentage of product 1 sold in year 1 through year 5, percentage of product 2 sold from year 1 through year 5 and percentage of product 3 sold from year 1 through year 5 along with items and the amount sold for each item.Ã,  My final output should look like this when I execute the build.Ã, 

Product 1   Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, %      Product 2   Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  %      Product 3   Ã,  Ã,  Ã, %   Total
00 A   50         Ã,  Ã,  30         Ã,  Ã,  40Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 120
Ã,  Ã,  Ã, BÃ,  Ã,  Ã,  Ã,  70         Ã,  Ã,  Ã, 40         Ã,  Ã,  60Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 170

TotalÃ,  Ã,  120         Ã,  Ã,  Ã, 70         Ã,  Ã,  100Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  290



I have all the data in one table in oracle.Ã,  Product 1,2,3 each are separate columns.Ã,  Item is also a column with either A or B.Ã, 

What I have done till now is created 4 separate builds for Product 1, Product 2, product 3 and Target output build.Ã,  I want to combine the output of builds Product 1, Product 2 and Product 3 and show it in the Target output build.Ã,  Can someone please tell me how do I do this?Ã, 

I have created functions but am not able to link the functions of one build into the other builds.Ã,  I have the output in each individual build ie. product 1 to 3.Ã,  But I am not able to get that output as a combined figure in the target build.Ã,  It would be really great if some one can help me with this.Ã, 

Thank you in advance.

MP

[CoginAustin]

This sounds more like a report. It sounds like you should create a fact table using your Product and Time dimensions with a Amount fact. Then use a reporting tool to do what you want instead of an ETL tool.

[CoginAustin]

I guess I can answer you question as well :)

Or, atleast what I think it may be.

If you are trying to combine all product builds into one (this is what I am not sure your trying to do) you can add each product to a fact build. Each product would be its own datasource. On the datastream side(properties) just use the same datastream item for each product. This will in essence do a union of all the results.

Or, do the union yourself in the SQL from one data source.

[BIsrik]

One way would be having a common column with common values in all the three product builds and execute all of them. Then create one more build ( for the total) having all the columns from the three tables joined on the common column.

Srik

[MFGF]

Hi MP,

I'm not entirely sure what you're trying to achieve here. Could you post some sample source data and the exact result you're trying to achieve with it, and we can see if we can come up with an efficient way of achieving it (in one build if possible).

Thanks,

MF.

[MPK25]

Hi MF,

From the solutions posted by others Iââ,¬â,,¢ve partially got what I wanted.Ã,  Its not possible for me to put sample data.Ã,  But for you to make it clear here is an example.

I have two columns say

Quarterly sales for Item A
Month      Quarter I Sales   Ã,  Ã, Total      Month      Quarter II Sales
Jan         50   Ã,  Ã,  150      Ã,  Ã, Apr         80
Feb         40      Ã,  Ã,    Ã,  Ã, May         20
Mar         60      Ã,  Ã,    Ã,  Ã, June         40


Sales for each month are derived by using functions where the weekly sales are added.Ã, 
Now I want to calculate two things.Ã,  One is total sales for Ist and IInd quarters.Ã,  I have already got total sales for two quarters as
Total sales for 1st Half
1st Half sales
Item A 290
Item B 320 (assumed)
Item C 450 (assumed)

And the percentage of sales for each quarter will be calculated based on this total sales.Ã,  For ex the percentage of sales for item A for quarter I will be calculated as

(Item A total sales qtr 1)/(Item A 1st Half sales) * 100

Which will be (150/290)*100 = 51.72%

Now my question is
Both 150 and 290 are derived by using functions in DS.Ã,  So is it possible to derive another value (the percentage) using already derived values i.e. 150 and 290.Ã,  Ã, 

I am trying but am not able to calculate the percentages.Ã, 

MF I guess I am clear from what I have mentioned above.Ã, 

Plz help.Ã, 

Thank you.

[CoginAustin]

Once a derivation is created it can be used in other derivations. It you look under something like Datastream or Datasource Derivations (under the derivations tab) you will see all the other derivations it has access to and you can use these just as you would any other  datastream item.

[MFGF]

Hi MP,

If you continue to develop this in the way you are currently heading, then use CoginAustin's advice - create an extra derivation which uses the results of your previous derivations to calculate the percentage.

Having said that, it strikes me that you are going about this in a very complex way.  Assuming you have a dimensional time hierarchy with year, half-year, quarter, month and week levels, your fact build could validate the weeks in your sales data against the week level of this, then could use aggregation to work out the monthly totals, quarterly totals and half-yearly totals of your sales measure (aggregate the week rows up to the month, quarter and half-year levels of the time hierarchy).  All of these could thus be calculated in one build without requiring any derivations.  Lastly, you would then require one simple derivation in your build to work out the percentage of each quarterly aggregate of it's half-yearly parent aggregate.

Best regards,

MF.