Managing Hierarchies in Dimension Tables

I am currently looking at the capabilities in SSIS from the point of view of an ETL developer who has worked with other products eg. Informatica, Cognos DecisionStream and one of things I note is a lack of support for dimensional hierarchies.

It appears that MS have assumed that SSIS users will automatically use SSAS.

We use Hyperion Essbase. Other sites have Cognos or Business Objects for their OLAP/BI.

I would like to be able build multi-level dimension hierarchies directly from within SSIS.

Has MS considered this for future versions?

We don't have any support for building hierarchies in these products simply becuase we do not connect directly to their metadata. However, it is possible to load hierarchies in SSIS - although again without direct metadata support.

Could you outline some features that you think would be useful? Or perhaps some issues you are currently facing in loading hierarchies?

Thanks

Donald Farmer

|||

Hi Donald,

Many enterprises are moving towards the concept of Master Data Management. This is the function of maintaining the reporting dimensions externally to any DW/BI system. There are specialised tools to do this but it can be managed in spreadsheets and fed into a dimension repository. This may be as simple as a few tables which store the attributes and parent/child relationships. The data in this repository has been validated.

As an ETL developer, when I wish to build dimensional tables, I can use the Master Data to source the hierarchies etc and then build the appropriate dimensional tables for either a star or snowflake schema.

Cognos 8 Data Integration (formerly the DecisionStream ETL tool) allows a developer to define a hierarchy. The GUI allows them to select the parent, child, description, and Top Parent Node of the hierarchy. It then generates a table containing chiild, description, parent, and level. From this, I can build a star schema dimension. One of the best features is that it checks the hierarchy for errors. The Developer can also view the hierarchy tree.

With SSIS, and I am a relative newbie, the only way to do the above is to too build a dimension in SSAS and then call that component from SSIS.