Calculated Measure: Formula limitations

Hi,

I am using AMO to programmaticaly create the SSAS objects like Cubes, MeasureGroups, Measures, Calculated Measure, partitions .etc.

I pick the necessary information from an existing Oracle OLAP/datawarehouse. In the Oracle OLAP the calculated measure can contain any formulae which the oracle db can support. This means its a huge list....! for example, Log, Decode functions.

Though SQL Db provides these functions, they are not avaliable to use directly in the MDXScript of the Calculated Measure.

so now when I am synchronizing the Oracle OLAP to SSAS programmatically at runtime, how do I resolve these inconsistencies in the formulae?

Regards

Vijay R

I think you mixing several concepts.

You can still referece Oracle functions while in DSV. You can create Named query or Named caclulation referencing any feature avaliable in your relational database.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

I guess I did not explain it correctly, I will try and explain it again.

I have a Oracle OLAP with an J2EE web app. I can create measures, calculated measures and the likes. when i create calculated measures in the Oracle OLAP using the J2EE UI, I can provide any formulae which the Oracle core can understand.

so now for eg: I have defined 2 measures M1 and M2 in Oracle OLAP. Next I define a calculated measure in Oracle OLAP, Here I define a formula like (M1 + M2)/100. Now when i view the data I get three columns M1, M2 and Calculated Values (M1+M2)/100.

Now I have a AMO program in .NET that automatically picks up the new measures and their related table info from the Oracle Relational tables and creates the same ones in SSAS. This means it creates a cube, then creates a measure group, sets the source of the measure group to the Oracle relational table (fact table), then creates the measures and partitions. The source property for all these is still the oracle relational database. but the information as to which tables, columns to use is picked from the existing oracle OLAP.

finally I process the cube in SSAS, and it is browseable.

The problem is when i try to create the calculated measure that is there in Oracle OLAP. I retrive the formula of the measure as (M1 + M2)/100. Now what I do in AMO is add a MDX script that creates a Member and adds this formula to it. Now this becomes a calculated measure in SSAS. whose value is got on the leaf cells using the "CALCULATE" command in the MDX script.

This works fine for all operators like + , - , * , / and also for Avg, Sum. but when it gets a formula like decode .... or abs, the formula in the MDX script is not recognised by SSAS. This is because MSXScript does not have those functions. I cannot map them to any Oracle functions because they are all built in Oracle funtions. like you know 'decode' is a common Database function avaliable in SQL also.

I am still stuck with this. currently we are not syncronizing the measures which have such functions. I have written a filter for them :-). so how do we do this. I was thinking that we write a SQL CLR function and pass the formula to it, which converts the formula to a MDX normalized form or something. still not sure.

Okay, I forgot. I have not used names queries or named calcualtions. I thought that names queries are for a partition. I have a fixed SSAS datasource which is mapped to the oracle db. when i use AMO to create the partition i just specify the table name in the SSAS datasource. About named calculations, I have to frankly accept I have no idea about it. ( I will look at this)

today there was another problem while using aggregation functions like avg and sum in the calculated measures (see my new post ). Not sure what is happening.

Thanks for the reply.

Regards

|||

Several things.

One, please take a look at the named calculations. This allows you to create a new column in your DSV "pusihng" calculations to relational database.

Second, take a look at the Analysis Services stored procedures. See if you can write a stored procedure implementing the functionality you need. Later you will use stored procedure in your MDX query.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.