Star Schema in Framwork or SQL SERVER 2008?

[Jlloyd666]

Hi,

Thanks for any replies much appreciated.

Were looking at implimneting COGNOS 10.2 in our buisness for internal reporting on a ticket management system. I am currently in discussion with a developer (SQL) in what is the best approach for our reporting sollution. The issue is around where should the Star Schemas sit?

Basically were reporting  on a Ticket management tool and are implimenting a data vault solution. This is just an ETL process to scrub the data clean and get it into a relatable table structure and normalize the strucutre. The actual backend is a mess and this stage is definutly needed. No argmuments here.

The next stage is debatable. Obviously a star schema sollution is required. However where this will sit has become a hot topic. Should the star Schemas sit within the framework or should the star schemas is within SQL Server.

I am not debating what is "best practise" I know that it is always a good idea that the star shema should be processed as much in the "back end" as possible. However what i am arguing is that the size of data were talking about doesnt warrent this sollution and that an intelligantly designed framework will function just fine. What are the thoughts on this? Is there such a thing as a "threshold" of which the star schema sollution on the back end is needed?

I am of the opinion that when reporting starts tp under perform (takes a while to run) and we are not able to throw anymore infactructure at it (memory in dispatachers) then the star schema should be designed/implimneted in the back end.

Any advice would be appeciated.

This is a relational-database BTW

[MFGF]

Hi,

Thanks for any replies much appreciated.

Were looking at implimneting COGNOS 10.2 in our buisness for internal reporting on a ticket management system. I am currently in discussion with a developer (SQL) in what is the best approach for our reporting sollution. The issue is around where should the Star Schemas sit?

Basically were reporting  on a Ticket management tool and are implimenting a data vault solution. This is just an ETL process to scrub the data clean and get it into a relatable table structure and normalize the strucutre. The actual backend is a mess and this stage is definutly needed. No argmuments here.

The next stage is debatable. Obviously a star schema sollution is required. However where this will sit has become a hot topic. Should the star Schemas sit within the framework or should the star schemas is within SQL Server.

I am not debating what is "best practise" I know that it is always a good idea that the star shema should be processed as much in the "back end" as possible. However what i am arguing is that the size of data were talking about doesnt warrent this sollution and that an intelligantly designed framework will function just fine. What are the thoughts on this? Is there such a thing as a "threshold" of which the star schema sollution on the back end is needed?

I am of the opinion that when reporting starts tp under perform (takes a while to run) and we are not able to throw anymore infactructure at it (memory in dispatachers) then the star schema should be designed/implimneted in the back end.

Any advice would be appeciated.

This is a relational-database BTW

Hi,

The approach you use initially will effectively take you down an avenue it's very hard to reverse out of later on. If you start off by defining stars in your Cognos 10 metadata only but with a transactionally-modelled table structure underneath, it will be a massive task to re-engineer later on if/when you find your performance isn't acceptable.

My advice is to build physical star schemas in your database, populated by robust ETL processes, and build a simple, elegant, efficient Cognos metadata layer on top of this. The added benefit in doing things this way is that the ETL processes can generally also be used to validate and clean the data as it it loaded, plus you can accommodate preserving historical changes to the data in the star schema dimensions, which would not be feasible using the metadata-only approach. your data warehouse will be scalable and re-usable if other parts of the business need to be added. It can be optimised for fast, easy reporting, with optimal indexing for best report performance. A transactional database would be the exact opposite - optimised for fast inserts and fast updates, not reporting.

Go the database route - that's my earnest advice.

Cheers!

MF.

[Lynn]

Hi,

The approach you use initially will effectively take you down an avenue it's very hard to reverse out of later on. If you start off by defining stars in your Cognos 10 metadata only but with a transactionally-modelled table structure underneath, it will be a massive task to re-engineer later on if/when you find your performance isn't acceptable.

My advice is to build physical star schemas in your database, populated by robust ETL processes, and build a simple, elegant, efficient Cognos metadata layer on top of this. The added benefit in doing things this way is that the ETL processes can generally also be used to validate and clean the data as it it loaded, plus you can accommodate preserving historical changes to the data in the star schema dimensions, which would not be feasible using the metadata-only approach. your data warehouse will be scalable and re-usable if other parts of the business need to be added. It can be optimised for fast, easy reporting, with optimal indexing for best report performance. A transactional database would be the exact opposite - optimised for fast inserts and fast updates, not reporting.

Go the database route - that's my earnest advice.

Cheers!

MF.

I don't think it is possible for me to find the right words to express how much I agree with the muppet!
DATABASE!!!!
DATABASE!!!!
DATABASE!!!!

[Jlloyd666]

Hi,

The approach you use initially will effectively take you down an avenue it's very hard to reverse out of later on. If you start off by defining stars in your Cognos 10 metadata only but with a transactionally-modelled table structure underneath, it will be a massive task to re-engineer later on if/when you find your performance isn't acceptable.

My advice is to build physical star schemas in your database, populated by robust ETL processes, and build a simple, elegant, efficient Cognos metadata layer on top of this. The added benefit in doing things this way is that the ETL processes can generally also be used to validate and clean the data as it it loaded, plus you can accommodate preserving historical changes to the data in the star schema dimensions, which would not be feasible using the metadata-only approach. your data warehouse will be scalable and re-usable if other parts of the business need to be added. It can be optimised for fast, easy reporting, with optimal indexing for best report performance. A transactional database would be the exact opposite - optimised for fast inserts and fast updates, not reporting.

Go the database route - that's my earnest advice.

Cheers!

MF.

Thanks for the reply, when you say its very difficult to reverse out of how do you mean?

Their will be a data vault ETL sollution which will scrub the data and validate it as well as create historical records for values that have changed. However 90% of these values are already created in the database.

If performance does become an issue can I not just ask the developers to use my star schema models from the framework and impliment it in the back end? If were affectivly talking about 30 tables what would the perforamance implications be? Is this a difference of 10 seconds or so (oviously the complexity of the report will need to be taken into account).

[Lynn]

I suspect you will find that the generated SQL out of your transactional structure may not always be as obvious and straightforward as you would expect. Cognos is going to generate SQL and you may not always like how that looks nor will you have a huge degree of control over it, especially if there are outer joins in the equation (which there often are in the operational world). You may find a lot of processing gets pushed to the Cognos server which is a cause of concern from a performance standpoint.

You don't have all the same tools in your arsenal on the Framework side that you do on the database side so you may start to use all sorts of work arounds like case statements and data type casting or hardcoded SQL based query subjects. Trying to assess options for improving performance become muddled.

You may find reports require a little more fiddling around with multiple queries or other tricks to get what you need in a way that performs well. They may be harder to develop, test, maintain and troubleshoot.

Now simply ask your developers to re-do the back end following what the framework has. Should they just do it exactly or try to figure out what shortcuts you had to take because you didn't have any database options available?

You may also have a much larger re-testing effort on your hands if many reports are written against something that will now potentially generate different SQL.

What is the motivation for skipping the database star schema approach? You mentioned 30 tables which doesn't sound like an overwhelming architecture job to me. What value do you hope to achieve by circumventing what you readily acknowledge is the best practice approach?

[MFGF]

Thanks for the reply, when you say its very difficult to reverse out of how do you mean?

Their will be a data vault ETL sollution which will scrub the data and validate it as well as create historical records for values that have changed. However 90% of these values are already created in the database.

If performance does become an issue can I not just ask the developers to use my star schema models from the framework and impliment it in the back end? If were affectivly talking about 30 tables what would the perforamance implications be? Is this a difference of 10 seconds or so (oviously the complexity of the report will need to be taken into account).

It sounds almost like you have already decided to build the stars in your metadata rather than the database, and you are looking for confirmation of your decision? Two experienced posters on here have recommended in no uncertain terms that you go the database route, yet you're still trying to justify a metadata approach and get someone to validate your decision?

Why develop a flawed solution from the outset, knowing you will probably have to re-engineer it later at more overall cost and time effort than it would take to do things the right way initially? It makes no sense to me. There are myriad benefits to getting your data structures sorted initially - data cleanliness, preservation of history, simplicity of metadata modelling, optimal performance of reports, easy maintenance, simplicity of generated queries - the list could go on and on.

Do it right the from the outset. You will save yourself money and time in the long run, and the project has the absolute best chance of success.

MF.

[cognostechie]

If performance does become an issue can I not just ask the developers to use my star schema models from the framework and impliment it in the back end? If were affectivly talking about 30 tables what would the perforamance implications be? Is this a difference of 10 seconds or so (oviously the complexity of the report will need to be taken into account).

Absolutely not ! There is no way to 'transport' the star schema from FM into the database.

If you don't  want to believe MFGF and Lynn then I can give you my live scenario. I am currently working for a company where
Cognos was implemented about 6 yrs ago. They did not create Star Schema in the DB but rather created ODS type of tables, did the
modeling in FM and started making reports. 6 yrs later, they end with around 45 packages and 1700 reports some of which take more
than a week to develop and more than an hour to run. The mess kept on increasing every day till about 2 months ago when the BI leadership was changed and now I have started working for the 'fix BI' project. The fix is actually making a proper Kimball design schema in the backend so complete ETL will be done again, then a new FM Model (self service BI style) will be made and the reports
will be re-made.

Learn from others or learn the hard way by making mistakes knowing fully well that you are making a mistake..

[bdbits]

Cognos does not perform that well without a database-implemented star schema (or snowflake but I digress). Like cognostechie, I speak from personal experience. I believe it was the very first package developed when the organization was still learning Cognos and BI. It ran against a normalized, transactional database and performed very poorly. It gave Cognos a black eye for parts of the organization that still comes up on occasion, years later.

Go with the database star schema.

[Francis aka khayman]

If your boss is an A-H0le, create the star schema in the FM. Then do the reports. Then look for another job after several months.

[Jlloyd666]

All,

Thanks for your reponses, I have taken your advice on board and made the relivant suggestions. Thanks for your time.

[maxchuie]

So is there no sense in creating a Star Schema in FWM it will not make a difference?..although the business will not go through the undertaking of building it in the DB level.

[cognostechie]

So is there no sense in creating a Star Schema in FWM it will not make a difference?..although the business will not go through the undertaking of building it in the DB level.

If the business will not agree to building a star schema at the DB level, that is because the business was given an option by the IT to build it in FM. They couldn't have decided that by themselves. Time to explain to them why it's not a good idea. Try the approach of explaining the low ROI, high recurring expense and instable BI environment resulting in them not getting the desired results. That should work.

[maxchuie]

Ok thank you cognostechie  I couldn't agree with you more it is just trying to convince the business and an egoistical DBA that this is the best route.