Another Framework Modeling Question

[UcfMike]

I was just handed a new Cognos project. Despite my best efforts to explain to my boss that I'm a report writer, and not a modeling person, I have the task of developing a model and packages in FM.

I'm looking more for some ideas on how to use the database given to me. It is essentially a Data Warehouse, and the design is already in Star Schema form.

Given this, I was considering importing the tables and establishing the relationships in a database layer. Next I was going to create a presentation layer, make model query subjects out of the imported tables, remake the joins, and give the query subjects more meaningful names.


Anybody see anything wrong with this or have a better solution.

One of the problems that will happen quickly is that tables that act like dimensions will eventualy have to be duplicated because there will be more than one fact table.

These fact tables don't join together, so we are hoping to use subreports, which I'm not sure Cog 8 has, to make meaningful reports.

 

[Nuffsaid]

Hey UcfMike

Sounds like my situation. Nothing I can see wrong with your approach. Couple of observations though.

I was considering importing the tables and establishing the relationships in a database layer. Next I was going to create a presentation layer, make model query subjects out of the imported tables, remake the joins, and give the query subjects more meaningful names.

All sounds good except for remaking the joins in the presentation layer. Don’t think this is necessary. I have the exact same structure as you, database already star schema. I just created the presentation layer with more meaningful names.

One of the problems that will happen quickly is that tables that act like dimensions will eventualy have to be duplicated because there will be more than one fact table.

Just join all your fact tables to the same dimension tables, keeping “fact grain” in mind.
If this won’t work for ya have a look at using shortcuts.

These fact tables don't join together, so we are hoping to use subreports, which I'm not sure Cog 8 has, to make meaningful reports.

If all your fact tables are at the same grain the joins happen through the dimensions as mentioned above. BTW C8 doesn’t use subreports as such (Crystal Report guy?)…

Let us know how you make out.......





Nuffsaid.

 

[UcfMike]

Thanks for the tip on the joins/relationships. You're right, that is redundant.

The fact tables won't be at the same grain. In Cognos 7, that's where I get my sub-reports experience, the previous developer used sub-reports and just lined up the data in the report so it looked correct. He's wanting to do that again.

Using Shortcuts is a great idea, the problem will be on how to properly join the fact tables since the granularity is so different.

Thanks for the advice.

 

[Nuffsaid]

Hey again…..

A few more thoughts based on your last post;

The fact tables won't be at the same grain.

Within your FrameWork model you could set up multiple namespaces. One for each level of grain. Problem here is you can’t mix Namespaces in the same Report Studio query, might not be a concern for you though. If it is, you still might be able to union or join your queries within Report Studio.

the previous developer used sub-reports and just lined up the data in the report so it looked correct. He's wanting to do that again.

If there’s no need to do calculations between the 2 sets of data you could use multiple queries within the report. This would be similar to using subreports. If calculations would be required perhaps doing a join or union within Report Studio would work for you.

It’s your requirements that will probably determine the best approach. FM & RS are pretty flexible and I’ve learned a lot through trial and (ahem…) error.

My approach has always been if you “think it’s possible”, “give it a shot”. What’s the worst that can happen? Blow up in your face? (said while wiping the soot of mine… )




Nuffsaid.

 

[yphilogene]

Hello,

In order to manage correctly "conformed" dimension table, you need to have:

- A dimension table which is flattened
- "Determinants" correctly set

Example:

Let's say we have 4 tables:
- Fact table A, with a year granularity,
- Fact table B, with a month granularity,
- Dimension table YEARS,
- Dimension table MONTHS.

First, you need to create a "flattened" dimension table TIME, which contains years and months. Therefore, there will be several rows containing the same year in this table.

Fact table A is joined on the YEAR column, Fact table B is joined on the MONTH column.

=> Everything will be ok with table B indicators, as the lowest level of granularity in TIME table is MONTH.

=> There will be double counting with table A if no "determinant" is defined on the TIME table. But with a determinant correctly defined, double counting will be avoided.

You can refer to the Framework Manager User Guide which explains with examples how to define "determinant" so as to avoid double couting.

 

[blom0344]

If there’s no need to do calculations between the 2 sets of data you could use multiple queries within the report.

You mean that showing data as 1 resultset would be possible, but no defining of a new calculation that uses facts from both queries would be possible?

Ties Blom

 

[Nuffsaid]

Hey Ties,

Not quite sure I got the meaning of your question, however what I meant was, you can use multiple lists on a single page, remove all the default formatting such as borders, background colors, etc. Then play around with the layout and to the end user it looks like one resultset, even though there are mutiple unrelated queries at work behind the scene. You just can't do calcs across queries.



Nuffsaid.

 

[blom0344]

I actually build a demo-report in RS that involves 2 seperate queries. I then defined a third query as a full outer join between Query1 and Query2. Using Coalesce function I can then show data from Query3. Calculations between objects from Q1 and Q2 is also possible.

With BO (Business Objects full client) this is a known feat called 'synchronizing dataproviders'. I was curious whether it is possible to do the same with RS. Looks like this is possible, though you need to perform quite a number of actions to make it work..

Ties Blom