How to reuse calculated columns in IDT

[Former Member]

Hi,

I am new to IDT and have a basic question. I have created a calculated column in the data foundation layer using existing measures in the fact table. Can I use this newly created calculated column in the definition of another calculated column.

Example:

CC1 definition = Measure 1 - Measure 2 + Measure 3

CC2 definition = CC1 + Measure 4

I did not find a way to define CC2 without writing the whole formula for CC1 all over again (i.e. Measure 1 - Measure 2 + Measure 3 + Measure 4).

Any advice would be really helpful.

Thanks.

[nscheaffer · Accepted solution]

Does it need to be in the Data Foundation?  You could do exactly what you are wanting to do in the Business Layer.  Would that work for you?

Noel

[Former Member · Accepted solution]

Hi,

Thanks for answering my question. I tried it and it is working great and also performing very efficiently over the business layer.

Are there any disadvantages of defining a measure over a business layer instead of the data foundation?

BTW, I received a syntax error when the original measure had a comma in its name, I am guessing this is due to SQL syntax restrictions.

[nscheaffer · Accepted solution]

The only advantage I can think of to doing this in the Data Foundation rather than the Business Layer is that you can have multiple Business Layers based on the same Data Foundation.  If you do what you are trying to do in the Data Foundation then you don't have to redefine it in each Business Layer.  I suppose there might be performance differences if you are dealing with massive amounts of data.  So it may be a non-issue for you.

You could also achieve the same logical result with the use of views on your database.

Noel

[Former Member · Accepted solution]

Thanks for the replies.

My reason for building this universe - I cannot use BEx query as a source in BO Explorer tool due to current limitations, so I am defining a universe over the BW InfoCube in the IDT. The calculated columns are the substitute for calculated key figures that are already available in BEx query designer.