Building a star schema

[actcognosuser]

Hi All,
            I need to build a star schema.I do not know where to start from.I have normalized data in my database.like Organization has an outer join to two other tables ,levels and type.

I have the following concerns before i build my schema:

->How do i merge organization,type and levels tables into one query subject named organization.In the business layer if i join the three tables like in the database and then add the columns i need in a new query subject,will it not serve as a view.What is the significance of building a model then???

->I need to build my fact.My question is should the fact table be defined in the database model itself or should i model it using framework manager??

What is the best practise ?should there be a star schema in the database itself???and we import the fact & dimension tables??

We are not using any ETL tool  yet.

Please advise.

[MFGF]

Hi,

One of the fundamental questions to consider is whether you want to build real star schemas in a database, or simply model your existing data in Framework Manager so it resembles star schemas.

For the former option, you will need to use some kind of ETL tool to read in descriptive and numeric data from your normalized tables and write out dimension and fact tables into a database.  You will also probably need to address data quality and consistency issues as part of this process, particularly if the source data is coming from multiple systems.  Additionally you may have to put processes in place to accommodate preservation of history where your dimensional values change over time.  The benefits of creating real star schemas are that the data is physically stored in an optimal form for fast, easy reporting, and there will be little modelling of the data required in Framework Manager to achieve accurate, consistent results from the reporting packages.  The drawback is that building and maintaining the ETL process can be costly and time-consuming, particularly for smaller organisations.

For the latter option, you will simply be creating model query subjects in Framework Manager which emulate dimension and fact tables (and yes, there is a direct parallel here to creating views in a database, as you suggested - the benefits here though are that it is in an environment over which you have total control, whereas a database may be under the strict control of a DBA, and the data may be coming from several different databases, whereas with database views this could prove difficult).  The benefits of creating these "virtual" star schamas in Framework Manager are that it bypasses the costly and time-consuming ETL process, and provides accurate, consistent results from the reporting packages.  The drawbacks are that the modelling process in FM becomes more complex, and the data is still physically stored in normalized tables (typically with few indexes) so performance of reports can be an issue, and the SQL queries generated by reports have to be far more complex.

Hope this helps!

MF.

[actcognosuser]

Thanks so much MFGF!It helped .I got some direction to build my model now.

If i need drill up and drill down capabilities should i define granularity in my FM model or create
a dimensional model in the database.

[MFGF]

Hi,

Drill down/up capabilities are available to you if you create Regular Dimensions and Measure Dimensions (DMR) in Framework Manager, or if you publish the data into an OLAP cube and use this as a reporting source.

If you choose the DMR option, at runtime the data is being read from your database, and is being summarized in real-time as the report runs.  If your data is in normalized tables, this can give some performance headaches for anything other than small data sets.  If, however, you have built physical star schemas in your database, performance may be acceptable.  If not (and if your database is aggregate-aware), you could build pre-aggregated fact tables into your database, and rely on the database's query optimizer to address these rather than the detail fact tables where appropriate.

If you choose the OLAP cube option, the data is aggregated as it is being written to the OLAP cube, and thus provides less scope for poor performance at runtime.  Depending on the structure and volume of data, OLAP cubes may or may not be suitable, though.

Regards,

MF.