Dimensional DB to completely replace Relational DB

Hi there,

This is my first post so hope everyone is well.

I have inherited a part finished dimensional database.

To give you a little background:
I work for a company that integrates it's software in SharePoint lists. At the moment the lists are extract to tables within a SQL database (the lists, are just like flat files and the database tables have the same structure as the lists; in this case they are partly denormalised as each month updates to values with SharePoint List items create new rows for some tables, duplicating a lot of data.

There is a need for more analysis and therefore a decision was made at some point to replace the present structure for a Dimensional DB. This DB would be the backbone of all reports (SSRS) and cube(s). The Present structure would be used as a staging area.

My question around this is how difficult is it to replace a 'relational' Db with a Dimensional DB and to use the latter for all reports/cubes (rewriting all existing reports is necessary)?

Can a Dimensional DB - if designed correctly - be a complete replacement for a relational db and with the ability to extract the same info as the relational? What are the caveats and is it worth it for one software data source (other external systems don't apply... yet).

Perhaps it could be worth adjusting the current schema and creating Star schema views for cubes!?

Any help for some of these questions would be completely valuable. Thanks

Hi cidr

SSAS cubes (and other flavors also) can be very fussy about how they get their data. It is always better to resolve issues of data relationships, validation, null handling, etc using a dimensional database. There you have the opportunity to use more powerful tools and techniques to control the data.

The "Star schema views" approach may look attractive as a shortcut but usually falls down on the complexities of the real world - the views become complex, convoluted and difficult to test and debug.

I guess what I am saying is that you will find huge advantages in your cube (and report) lifecycle from doing the hard yards upfront to build a proper dimensional database. Failing or shirking this task pushes a huge challenge downstream to the cube and report designers and testers who have far inferior tools to deal with it.

Good luck!
Mike

It's not hard at all. Kimball's laid out exactly how you do this in his books. Most of the dimensional model data in this world originated from a relational database. The big gains are well known, ease of querying, better performance, etc.

Thanks folks,

There is some of the original developers design that I'm not sure about. However, I do think it could be a short cut creating views for the cube to process the data. Especially if there will be millions of rows.