If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

First Framework Model - Help

Started by pravin.cognos, 06 Mar 2012 07:48:59 AM

Previous topic - Next topic

pravin.cognos

Dear Gurus,

This is the one question I always wanted not to ask but now I give up after a week's effort with out bearing any fruit !!

I need to create a new Framework Model  ::) and author reports on that.

Technical Details :

I have to create a model on the Relational, SQL Server2008, OLTP database (Performance can take the back seat, client is ok if the report runs for 10 mins). The tables will be populated by end users using an application. There are approximately 30 tables, one of which is used to store all the facts. (i.e 29 dimentional tables and one fact table). All these are additive facts but reports need to show semiadditive facts out of these like %s, totals, avgs etc. These facts get recorded daily but most of the reports need to show data for either one year or one quarter. There is no table for storing time separately but there are dates in most of the tables.

For a week I have been reading a lot of stuff to create a "Good model" and could not figure out much. I have below basic questions to ask experts here.

1. Do I need to go for Relational model or DMR, I assume that DMR is tough one to deal with ?
2. What should be my first step after importing all the tables?
3. How can I create a table for time? Is it necessary ?
4. I want to put least effort on the report authoring. What is the best way to do it?
5. Is create new model query subjects, query items, Star schema grouping mandatory? I am not sure when this should be created..
6. Is it a good idea to set the relationships as shown in my E-R diagram?

Thanks in advance to all..

Regards
Pravin



blom0344

1. Do I need to go for Relational model or DMR, I assume that DMR is tough one to deal with ?

Start with relational (can always add a dimensional layer)
2. What should be my first step after importing all the tables?

a. Reference namespace with all imported tables for reference (no joins)
b. Create modelling namespace for joins, SQL subjects etc.
c. Create presentation layer
3. How can I create a table for time? Is it necessary ?

Depends, not for simple attributes cause these can be calculated using the extract function against dates, but less trivial time attributes might benefit from a real calender table
4. I want to put least effort on the report authoring. What is the best way to do it?

Out-sourcing to report developers
5. Is create new model query subjects, query items, Star schema grouping mandatory? I am not sure when this should be created..

Start schema grouping is just a wizard. Defining SQL subjects , items would depend on the completeness of the database
6. Is it a good idea to set the relationships as shown in my E-R diagram?

In case the database is modeled as a near perfect starschema, then you may import relationships, but it depends on your reporting needs, nullable FK's (defining outer joins etc)


pravin.cognos

Gud mng Blom..

Thanks for your reply.. That is very helpful information to kick start the Modeling. Cleared most of my doubts. Will post my questions as I progress ;)

Thanks again :)

Regards
Pravin