Refreshing Data in Hyperion Essbase Cubes

Hi all,

Normally I would be using SSAS but our finance department make use of Hyperion Essbase. I was wondering whether it was possible to upload data into an Essbase cube using SSIS in the same way that you can use the Analysis Services processing task? I realise there are no specific task for Essbase, but are there any suggestions about what would be the best way of going about this?

regards

Colin

Phew, not sure about this one. Is there an API for Hyperion? if so, perhaps you could call it from a Script Task?

-Jamie

|||Even less sure, but is there an OLE-DB provider which could be use?|||

I suggest asking on the Hyperion Developer network. http://dev.hyperion.com/

If it is possible to update a cube using Visual Basic .Net, then you can do this using script in SSIS. It should be possible through their API: http://dev.hyperion.com/techdocs/essbase/essbase_61/Docs/api/api_vb/mavb/homemav.htm

In the future, there will be tighter integration between SSIS and Hyperion. http://www.microsoft.com/presspass/press/2006/apr06/04-25HyperionBIPR.mspx

Donald

Reefernce dimensions

We have designed a schema which has some snowflakes which we implement as Reference dimensions in SSAS. This design seems to be the most logical given that we have a dimension table called Portfolio, which has a number of related dimensions such as Client, Employee and Product. Instead of direcrly joining all these tables to the Positions fact table, we set them up as Reference dimensions via Portfolio.

I have read in a number of places (Lachev, Mundy) that there may be some issues with this approach, particularly on performance. Are there any metrics on what sort of performance degradation there may be? Does it depend on the number of Reference dimensions? Is it generally better if possible to always use the Standard dimensions by denormalising to a star schema - that is making Client, Employee and Product join directly to the fact table? Are there any other advantages or disadvantages, such as in the ease of data maintenance?

I would be grateful for any feedback on this.

I don't know what the performance implications of Reference dimensions are, other than that you should select the "Materialize" option on the Usage dialog, to improve query peformance - could you mention the respective page numbers in the 2 books, since many in this forum may have access to them?

But, regardless of whether you use Reference dimensions, or roll Client, Employee and Product into the Portfolio dimension, there should be no need to denormalize the snowflake to a star. For example, the Product dimension in Adventure Works has 3 tables, but there are other examples of Reference dimensions as well.