Modelling Question

[cognostechie]

So if you had 25 dimensions and 3 facts, you would have multiple conformed dimensions (all 25 of them) or would you look into each one of them and then resolve it as you 'think' it might pose a problem?

I would rather have only one conformed dimension and use the concept of 'alias shortcut'. Yes, most busineses do have the need of more than one dimension, hence the concept of 'alias shortcut'. Not sure if everybody knows about it ! That might be leading to the belief that all the dimensions have to be linked to all the facts !

I would rather have a model that has a consistant modelling technique instead of having a few dimensions as common and others as seperate for the facts.

I understand the stitched-query generation but that is limited to the scenario when you have all the dimensions and facts on the same schema in the DB ! In the case of having dimensions in seperate schemas and Fact on only one schema, Cognos did produce wrong results and it was only resolved using alias shortcuts and having only one conformed dimension !

I would still use alias shortcuts instead of having multiple conformed dimensions. That way, I would also have a true Star Schema design in the Business Layer !

[MFGF]

I am an expert in everything and 100% correct 100% of the time  :o Anyone who believes this should contact me so I can tell you all about the Brooklyn bridge that is for sale ........

Ummmmmmm...... Yes. Absolutely. Ahem. Moving swiftly on... ;) :D

So if you had 25 dimensions and 3 facts, you would have multiple conformed dimensions (all 25 of them) or would you look into each one of them and then resolve it as you 'think' it might pose a problem?

I would rather have only one conformed dimension and use the concept of 'alias shortcut'. Yes, most busineses do have the need of more than one dimension, hence the concept of 'alias shortcut'. Not sure if everybody knows about it ! That might be leading to the belief that all the dimensions have to be linked to all the facts !

I would rather have a model that has a consistant modelling technique instead of having a few dimensions as common and others as seperate for the facts.

I understand the stitched-query generation but that is limited to the scenario when you have all the dimensions and facts on the same schema in the DB ! In the case of having dimensions in seperate schemas and Fact on only one schema, Cognos did produce wrong results and it was only resolved using alias shortcuts and having only one conformed dimension !

I would still use alias shortcuts instead of having multiple conformed dimensions. That way, I would also have a true Star Schema design in the Business Layer !

Creating alias shortcuts to a single, amassed dimension is certainly not in the best practice guidelines provided by IBM (or even going back in time to Cognos and the days of ReportNet).  It's quite a radical modelling approach, and not one I'd necessarily suggest as a normal way of modelling in Framework Manager.

I'm sure it could be made to work, but the query engine hasn't been specifically optimised for it, and it may cause big challenges to other modellers later on that have been taught the best practice approach if they have to pick up your work then figure out what has been done in this model.

A collection of 25 conformed dimensions isn't unheard of, although it's unlikely a single fact table would link to all of these. The number you end up with is driven by the business requirements for reporting and the way in which the business categorises things.

Again, these are just my own thoughs, and not intended as a guide to how things should or must be done. Just the humble opinion of an old muppet :-)

MF.

[Lynn]

I'm confused on a few points.

I understand the stitched-query generation but that is limited to the scenario when you have all the dimensions and facts on the same schema in the DB

Same schema? As in Oracle schema? Whenever I've had multiple Oracle schemas to contend with I'd have synonyms set up such that I'm working with a single data source definition and no need to query with schema name prefixing, so perhaps I haven't faced what you are describing.

Otherwise, my understanding is that you'll get a stitch query whenever you have a multi-fact query with conformed dimensions and it will be processed locally on the Cognos server, unless the database supports full outer joins, in which case it could be generated as a full outer join SQL statement and executed on the database.

I would still use alias shortcuts instead of having multiple conformed dimensions. That way, I would also have a true Star Schema design in the Business Layer !

I use alias shortcuts often, generally for role playing dimensions. In the case of a multi-fact modeling scenario, let's say you'd have time as the one dimension conformed to three different fact tables. Then let's say you have product as an alias shortcut, therefore three different product dimensions (one per fact table). Is that what you are describing?

If so, how do you present a product model query subject to your users? It seems instead of having a single model query subject to house the product attributes you'd need to have three of them, each one identical except referencing a different data source query subject (e.g., Product Sales, Product Returns, Product Forecast). Or else you'd need all the various product attributes baked into each of the fact model query subjects. Either way, any modeling change would need to be done three times in three places. More importantly, as a business user creating a report that ought to include product name, which one do I use and when? If I did have 25 conformed dimensions, imagine how quickly the number of attributes would multiply.

Maybe I'm misunderstanding the alternative you suggest....been known to happen! clearly I fibbed on the 100% correct comment  :-[

Anyway, this has been a very interesting discussion and I enjoy hearing everyone's thoughts! I am always interested to find out different ways to approach problems!

[cognostechie]

Both of you are speaking about a straight-simple model.

Best practices and normal way of modelling is a term you are probably using depending on how you define these things yourself rather than the practices suggested by IBM. I didn't find any documentation from Cognos/IBM saying that alias shortcuts are not a good approach! Can you share that document MFGF?

Creating 3 different shortcuts won't need to make the change in 3 query subjects. You just have to make the change in the original Product dimension and it will be reflected in all 3 shortcuts. They will just have different join strategy.

The Presentation Layer will look like this:

Sales Fact
    Product Dim
    Customer Dim
   

Returns Fact
    Product Dim
    Customer Dim
   

Forecast Fact
    Product Dim
    Customer Dim
   

Common Dimension
    Time Dim   


In this case, you have to first make the Product Dimension by merging all attributes of Product which might
be in 3/4 different tables.
Again, I am talking about modelling from highly normalised schemas like an ERP or CRM. Modelling from Star Schemas is a no-brainer as most of the stuff is already taken care of by the DW guys.   

[RobsWalker68]

Hi Cognostechie,

I will admit I have been schooled in the Kimball method and that is how I approach all my data warehouse designs, although I am happy to accept there will be other ways of handling the modelling of an enterprise wide data warehouse. However the Kimball methodology, as adopted and implemented in BI query engines and database optimisers does give consistent results and consistent access paths.

If you adopt the Kimball triage approach to designing your data mart using the BI Bus to create an integrated enterprise wide data warehouse then you will have a fact table with measures and a series of surrogate keys that will link to your conformed dimensions.  The conformed dimension will enable you to tie the individual datamarts together so you can report across the enterprise and not create stovepipe (isolated) datamarts.

Below is a link of what to avoid in creating a datawarehouse

http://www.kimballgroup.com/html/articles_search/articles2001/0110IE.html?TrkID=IE200110

The biggest mistake being fail to conform your dimensions -

Mistake 1: Fail to conform dimensions across separate fact tables. The single most important design technique in the dimensional modeling arsenal is conforming your dimensions. If two or more fact tables have the same dimension then you must be a fanatic about making these dimensions identical or carefully chosen subsets of each other. When you conform your dimensions across fact tables, you will be able to drill across separate data sources because the constraints and row headers will mean the same thing and will match at the data level. Conformed dimensions are the secret sauce needed for building distributed data warehouses, adding unexpected new data sources to an existing warehouse, and making multiple incompatible technologies function together harmoniously.

I have used alias shortcuts or "role playing dimensions" as called in the Kimball methodology. However, they are used for a very specific modelling scenario where you wish to create a copy of an existing dimension but have it interpreted by the quert engine as a different dimension.  For instance your fact record has two date fields "shipped from date" and "shipped to date".  You can't link both fields to the same date dimension and therefore you use a role playing or alias shortcut to create two copies of your calendar that are intepreted seperately by the query engine.  It is not an approach you would implement generally to handle loop joins.

Like I say I may be old school Kimball and if your approach works for you then carry on I'm just concerned you may be creating a lot of extra modelling work that is not entirely necessary.

Kind Regards

Rob

[Lynn]

Both of you are speaking about a straight-simple model.

I WISH!!! Currently I'm modeling for an operational source and have multi-fact queries working properly. It is most certainly not limited to a simple star schema where the DW guys have already done the heavy lifting.

In my case I have relationships in the physical layer and need only a single presentation layer query subject for product that works with all facts it conforms to. Not sure if your shortcuts are for the base database tables or the Product Dim in your business layer. But I would find three different representations of the same thing a point of confusion for many users.

What is very interesting to me is how a multi-fact query might be generated in your scenario. If a query in your model used sales amount, forecast amount, Month from the Time dimension and Product Name from, say, the Sales Product Dim, then wouldn't the Month be the only one to get a coalesce?

The Product Name (no matter which of the two you picked) would be viewed as non-conformed I think. Therefore it wouldn't get a coalesce and would only be queried for the fact it relates to (sales in this example).

I'm about ready to head home for the day and my brain is addled by now, but it seems to me you'd get SQL generated that will give you one query from sales with month and product, and then another query from forecast with just month. They get full outer joined and the sum of all product forecasts for the month gets joined to the sales by product for the month. This would be wrong. I just pulled up my trusty training guide and reviewed the section "Identify non-conformed dimensions in Generated SQL".

If someone got confused and picked Product Name from Return Fact Product Dim instead of either of the other two where you've selected facts then heaven only knows what SQL would turn up!

A single fact query in your environment would work perfectly correct as far as I can see, but I'm not quite seeing how multi-fact would work for you.

Of course I don't know all the nuance you probably have going on, but remain interested by the topic :D

[cognostechie]

Yes, you are correct ! The SQL generated is crappy, the data in the report is wrong and I have been fired from the project !!  ;D

Anyway, I guess the OPs post was really a very simple model and hence all this is not required here ! Rob, you were probably correct and I went too far to analyse things.

[jive]

Hi,

Just my experience,
In the projet I work now we have 96  dimensions on those dimensions 68 are conform business wise dimension all the alias (35) are done thru materialized view in the oracle database and the other , 28, are restrict to certain fact table. Most of the 28 are needed more for description-information on operational behavior then real business analysis.

With that model, for the moment, I can resolve all the requirement in small effective and reporting package. 

I can produced one package with everything , all fact and conform dimension related. OK may be it's long to do crosstab with 10 level on each side.. but at least I can do it ;-) and validate my result with an SQL query directly on the database.

With those 68 conforms dimension I can scroll around 10 to 20 fact table by theme , it's around 110-120 measure and explain close to 95% of the business.

I have define at this moment 40 packages including or removing dimension and fact based on the business requirement. To be honnest, the important thing: that's work an it's work really well ,fast and reliable, even SAS , can connect directly to the datawarehouse source table and get the same result as Cognos for deeper statistical analysis.

Sorry to don't know more about loop chain query and stichy query ... But about conform dimension I used then a lot  :P

Regards Jacques   ;D

[RobsWalker68]

Thanks CT for the reply and it has been interesting discussing things with you.  Back to watching series 6 of the West Wing - Told you I was old school  ;D

There are many ways to skin the same cat

All the best

Rob

[Lynn]

Yes, you are correct ! The SQL generated is crappy, the data in the report is wrong and I have been fired from the project !!  ;D

Anyway, I guess the OPs post was really a very simple model and hence all this is not required here ! Rob, you were probably correct and I went too far to analyse things.

I don't mean to be annoying or irritating....it just comes naturally....I don't even have to try  ;)

I only wanted to understand and I hope you didn't take offense! I learn so much from all the very smart people here and constantly challenge my own assumptions and understanding when I hear about different approaches.

There are certainly many ways to skin the cat as Rob says.

That is an awful expression if you think about it and I'm not even all that crazy about cats!  :o

[cognostechie]

Thanks CT for the reply and it has been interesting discussing things with you.  Back to watching series 6 of the West Wing - Told you I was old school  ;D

There are many ways to skin the same cat

All the best

Rob

Yup ! It was definately interesting ! If you ever have to model using an ERP as the backend, just try my method. It will make the model more organised even if it's additional work and most importantly, it will eliminate those stitched queries with full outer joins so you don't have to take a nap till the report comes up. Querying ERP schemas that have so much of normalization and indexing not done for reporting purpose makes full outer joins very slow. 

[cognostechie]

I don't mean to be annoying or irritating....it just comes naturally....I don't even have to try  ;)

I only wanted to understand and I hope you didn't take offense! I learn so much from all the very smart people here and constantly challenge my own assumptions and understanding when I hear about different approaches.

There are certainly many ways to skin the cat as Rob says.

That is an awful expression if you think about it and I'm not even all that crazy about cats!  :o

No worries ! I intentionally started it as I was getting bored  ;)

Just kidding..

[RobsWalker68]

Yup ! It was definately interesting ! If you ever have to model using an ERP as the backend, just try my method. It will make the model more organised even if it's additional work and most importantly, it will eliminate those stitched queries with full outer joins so you don't have to take a nap till the report comes up. Querying ERP schemas that have so much of normalization and indexing not done for reporting purpose makes full outer joins very slow.

Your going to jinx me now and my next contract is going to be working on some damn awlful SAP ERP system  ;)

[MFGF]

This has been a very interesting discussion, and I have really enjoyed reading the different views and seeing things from other perspectives.  It's one of the great things about forums like this that folks can kick ideas about without fear of ridicule and air views based on real experience rather than just what the manual or (ex) class instructor says.  Reading threads like this one reminds me that there are some very, very smart people on Cognoise, and I feel extremely privileged to be a part of this great community!

Lynn, CT, Rob, Jive - you have my complete admiration!

MF.

[PRIT AMRIT]

I am very much agree with MFGF.

In short, this forum has been a knowledge treasury for me and I am very thankful to all of you sharing your ideas and experience without any hesitation.

THANK YOU ALL.

Prit