Creating Data Model best practices

[stancho]

Hi all,

In most of the cases when I use Cognos to create reports I use the following techniques:
I use custom created datamodel in MS SQL database as a datasource.
The data is being loaded in the MS SQL database using ETL which comes with MS SQL.
The data is loaded every night so the latest reports which can be viewed are for yesterday.

Are these techniques good?

More important question is where can I find something like best practices for creating this custom data model. I need an optimized speed in Cognos when working with the database.
I met some issues working with MS SQL DB. For example if a table becomes too large (about 5 million lines) executing reports is very slowly because the SQL engine does not optimize the queries with indexes.
If I have a main table and then a lot of 'dimension' tables connected with foreign keys it also could become slow if the relations are too much.
In few words I need some best practices for creating my data model to work with.

Is there something like 'the best' type of database for Cognos reports with large data (mssql, db2, oracle..)?

Thanks and Regards,
Stancho

[RobsWalker68]

Hi Stancho,

Most probably need to know a bit more information about your database design.  5 millions rows isn't a particularly large record set for any of the db's mentioned to handle and the dimensional relationships shouldn't if correctly modelled have any adverse impact. 

As you mention a main table with dimensions is it a star schema design?

Rgds

Rob   

[blom0344]

Whether index acces is used does not only depend on the database size. The type of performance tuning you are after comes with testing access paths, server loads and such. This is typically DBA stuff.  Most modern databases have very sophisticated query optimizers and are very aware of starschema designs.

When datasizes grow there a typical set of things to think about:

1. Partitioning data (for instance using separate partitions for years)
2. Defining a proper clustering index
2. Creating indexed views (AST / MQT) to pre-aggregate data so the optimizer can switch from detail tables to stored aggregates.

But, with 5 million facts records you may not have those issues yet  ;)

[stancho]

Hi guys,

Thanks for your quick replies and good advices.

In fact we use star schema design and we created the suitable indexes in the fact table and foreign keys to the 'dimension' tables.

When executing the reports we notice that the sql query optimizer does not succeed to choose the right indexes in the right table and the result is that a full-table scan is performed. And the perforamance is too bad.

We would like to avoid setting explicitly the indexes that MS SQL should use (from tabel1 with (index (ix_by_date))).

BR,
Stancho

[RobsWalker68]

Hi Stancho,

Something doesn't sound quite right with the design.  Like all the points Blom made these shouldn't need to come into play until you are hitting the multiple hundred of millions of row mark and certainly not 5 million.

With a star schema you always have equal access paths i.e. a dimension to the fact table and thats it.  If you are using a dimension and a measure from the fact there shouldn't be any index choice not on the join anyway.

For example do you only have a fact table that only contains measure values and key values, preferably surrogate keys.  Each individual surrogate key on the fact table e.g. the date key only relates to the corresponding key on the date dimension table and nothing else?

Cheers

Rob

[ykud]

I've summed up basic MS SQL database configurations in this post, maybe it'll help you.

Again, 5mln isn't a volume where any problems should start, if you're not using char(250) as column types.

[macklinj]

We are experiencing a very similar situation with a standard star schema.  Did you figure out what the problem was?