Sounds like you have a plan then.
A quick tactical DM where you bring in your existing views into a DM, and do some complicated stuff in reporting. To remind you, reporting has these common sql concepts.
This gives you a quick win.
The roadmap of where you want to be once the quick win is out of the way. A data module focussed around the full user community and suitable for all tools.
Original Message:
Sent: Mon October 28, 2024 10:12 AM
From: Greg Wilburn
Subject: How many tables in a Data Module
That is exactly what I need to do. Right now I need a win.
I am able to replicate many of our client-facing reports by accessing the DM directly - maybe I'm trying to hard to get it perfect the first time?
------------------------------
Greg Wilburn
Director of IT
Total Biz Fulfillment
Grantsville MD
Original Message:
Sent: Mon October 28, 2024 09:40 AM
From: Marc Reed
Subject: How many tables in a Data Module
Within Cognos Reporting you can do complicated SQL concepts, like joining, unioning, sub queries etc to alleviate a lot of pain - probably a bit like your old tool. But the best Cognos solutions have a metadata model that is:
- Is requirement driven , not data driven design
- Suitable for Professional and Business Authors
- Is suitable for using in all tools - Reporting, Dashboards, Stories, Explorarations
- Gives predictable results
A Data Module that requires advances reporting authoring concepts doesn't really fit all of the above.
That should be your end goal. In order to get there you may have to rethink your approach. Your wrote views in your database for your old solution. These may be reusable in a Cognos solution - but be open to the idea that the may not be the best to give the 4 points above, so you may have to start again.
You may want to build an initial tactical quick win project, that simply replicates your existing solution. A data module that is only really suitable for the advanced professionals. That requires report authors to join data together in the report.
With a more strategic project being to go back to basics and look at where you want to go with Cognos - delivering a solution that offers all of the initial points I listed.
These are the exciting parts of a project - defining the vision and the roadmap. It's the bit I really like.
------------------------------
Marc Reed
Original Message:
Sent: Mon October 28, 2024 09:25 AM
From: Greg Wilburn
Subject: How many tables in a Data Module
Thanks Marc.
I think my biggest issue is that I'm not really looking at my ERP tables. Instead, I'm using data views that have already combined most of the dimensions and facts.
My real struggle is when creating a single report containing data from different fact tables. In the example of Orders and Purchase Orders. Our previous tool allowed us to join within the individual report. So these were separate data sources. The join was specific to the report.
There's just something with this Cognos that is not clicking for me. Classic example of "I don't know what I don't know".
------------------------------
Greg Wilburn
Director of IT
Total Biz Fulfillment
Grantsville MD
Original Message:
Sent: Mon October 28, 2024 05:13 AM
From: Marc Reed
Subject: How many tables in a Data Module
Any advise your going to be given on here is generic stuff. It's impossible to give accurate advise without knowing the ins/outs for your particular data and requirements. With that caveat said, to answer a few points.
Removing repeating columns on alias tables isn't something you would want to do. A typical alias use case could be turning a single calendar into multiple instances such as a Shipped Calendar, Order Calendar, Invoiced Calendar. The reporting user would want access to all the columns in all the alias. For example, the report may need both shipped date and ordered date. So both dates must be visible. You don't want to hide any of them.
My point about orders and purchase orders. In your metadata model anything that wanted to query purchase orders must go through an order. In a typical star schema, we don't join facts together.
This is all explained in the metadata modelling links i provided earlier - the bit about stitch queries is usually how we prefer to join fact tables together. It's generic advise, and there are exceptions, but that is the normal default position.
------------------------------
Marc Reed
Original Message:
Sent: Fri October 25, 2024 08:58 AM
From: Greg Wilburn
Subject: How many tables in a Data Module
Thanks Marc, that helps a lot!
I really appreciate your patience and feedback - I'm trying to get confirmation that I'm on the right track.
The first diagram is what was originally created by the consultant (maybe by a tool?). It does contain some duplicate tables that we removed... Also the Invoice Header and Detail are already joined into "Invoices" - so they were removed as well. "Orders" and Purchase Orders were the same way. I have a much better looking DM now.
What do you meant by: On the second diagram my alarm bells would ringing due to the join you have between Orders and Purchase orders. On the whole it looks like orders is a fact table, but then there is a join to the purchase orders that models it like a dimension. And the cardinality says that not every order has a purchase order. I would investigate that, as my default position would be a model more like this, treat the two as two fact tables and let the cognos query engine stitch the data together.
In my previous BI tool, these two were separate pieces of metadata. They were joined together in a specific report that showed the user individual lines of an order (list) adjacent to a pivot (crosstab) of purchase order quantities and expected receipt dates. Not every item number has quantities on purchase order. I am not sure to replicate that capability within Cognos vs the other tool, so I joined it in the same fashion in the DM.
Secondly, the consultant recommended sticking with a single DM, possibly using Aliases & Views to present the data. I was good with that idea, but was unable to "hide" repeated columns (keys) from the user when using Aliases.
Thankfully, not many authors have to open the DM and see the internal workings of it. They will just see the view you present to them using folders to help structure it.
This is what I currently have:
------------------------------
Greg Wilburn
Director of IT
Total Biz Fulfillment
Grantsville MD
Original Message:
Sent: Fri October 25, 2024 03:14 AM
From: Marc Reed
Subject: How many tables in a Data Module
@Greg Wilburn you are pondering the exact same questions every developer has at the start of a reporting project.
The Cognos solutions I build contain the full spectrum of reporting - from operational list like reports that you're discussing, to high level corporate dashboards. And i build all the deliverables from the same data module.
I will agree with you that a data module with many tables in becomes 'messy'. This is something I have mentioned many times to IBM with feedback I have given on data modules (and for anyone else reading let's not start the DM vs FM debate again). There is a focus mode that can help modellers a bit.
Thankfully, not many authors have to open the DM and see the internal workings of it. They will just see the view you present to them using folders to help structure it.
@HENK CAZEMIER - see, it's not just me who gets upset with this data module relationships window!
Back to your question on single or multiple data modules. Remember that its down to the tools that use the data modules.
- Reports can consume multiple data modules at the same time.
For example you may have one data module show a list, and another for a chart.
Advanced authors can easily make common filters apply to all objects in the report, regardless of what data source was used to create them.
For example, a single calendar filter to apply to the list and chart.
If you need to combine data from different data modules into a single report object, reports can do this because of they expose the full query engine to authors.
For example I have a sales data module, and a budget data module. I want to create a single chart that shows the sales and budget together.
- Dashboards / Stories.
Can also consume multiple data modules at the same time. But dashboards don'tt have the advanced features of reporting.
Using the same examples I discussed in reporting.
I could create a visualisation of sales, I could create a visualisation of budget. But...
I could not combine the two into a single chart.
I could not create a common filter.
Whilst today you only want reports, remember where you want to get to.
In my projects, the limitations of dashboards means that I always end up with a single data module. The point to remember is this: think about the data you want to combine with reports/dashboards. That data needs to be in the same data module. For example, if I have a corporate data warehouse for sales, and another for stock control, and a load of sales reporting requirements, and a load of stock control reporting requirements - should I build a single data module or two? Well if I know the two live in isolation, and I never need to build a report that contains both bits of data then two data modules might be the way to go.
However, just because you may desire a single data module for reports/dashboards - doesn't mean you have to get there with a single data module.
Look at that blog post I linked to earlier. This shows how you do the majority of modelling in in smaller data modules, and then bring those simpler modules together into an 'uber' data module that contains all the components.
This is all very high level advice. Every person who reads this web site will have a differing opinion.
You mentioned that you are working with a partner who has Cognos experience. If they are an experienced solution architect, which is what you need when you're starting off, then they should know all of this and have done this before. I am not disrespecting them, just surprised that have left you pondering these questions yourself on a forum.
Looking at the picture you supplied - if you called me on site to review these, this is what I would immediately pick up on.
On the second diagram my alarm bells would ringing due to the join you have between Orders and Purchase orders. On the whole it looks like orders is a fact table, but then there is a join to the purchase orders that models it like a dimension. And the cardinality says that not every order has a purchase order. I would investigate that, as my default position would be a model more like this, treat the two as two fact tables and let the cognos query engine stitch the data together.
On the first diagram, I would probably combine things like Invoice header and Invoice detail into a single invoice table. And there seems to be repetition of a few tables.
I'm not saying you're wrong - its just those things would be my immediate things to look at.
Every solution has slightly different modelling requirements, but these are the rules of thumb I have used in the past.
------------------------------
Marc Reed
Original Message:
Sent: Thu October 24, 2024 10:39 AM
From: Greg Wilburn
Subject: How many tables in a Data Module
Thanks again Marc. Just by the comments here, I'm feeling that your reporting environment is much more complex that what I'm initially trying to build.
Almost all of my reports are producing detailed lists (excel worksheet) about a transaction level information. Current users want to access information at the individual Order or Invoice level. Some reports even down to the line item of each invoice or or order. So I almost have to create a DM that replicates the database information.
The users (which are actually our clients) take that detailed transaction information and do their analysis (in Excel) from that data.
This is what I initially have to replicate.
In the future, I would like to use Cognos how it was meant to be used - as a Business Intelligence tool. But for now, I need to give them the simple report output that they are accustomed to.
To me, this doesn't look like an environment that a typical report author could navigate
I'm just trying to determine if a typical environment might look like the above, or if I would be better server (for these reports) to create an environment with multiple data modules, containing only the specific tables need to product reports for say "current open orders" (below)?
------------------------------
Greg Wilburn
Director of IT
Total Biz Fulfillment
Grantsville MD
Original Message:
Sent: Thu October 24, 2024 02:46 AM
From: Marc Reed
Subject: How many tables in a Data Module
Cardinality is one area that can have a big impact on the SQL the cognos query engine creates.
Would recommend you read this topic: Cardinality in Cognos
The follow on topics also shows why Cognos needs to identify dimensions and facts. You may find all the help topics in that part of the documentation useful. You'll see such topics as stitch queries, remove ambiguous relationships and so on. Just remember - build the model that supports the reporting requirements, not model the physical database. It's a mindset switch that makes a big difference.
If you're looking at the SQL Cognos is creating, and you're seeing outer joins when you're not expecting them (in your metadata model everything's an inner join) then its a sign that Cognos is performing stitch queries as it believes you are using multiple fact tables in your report.
If you are working with a Cognos Partner then they should be very familiar with these concepts as they are fundamental to building a good Cognos reporting solution that gives expected results to professional report authors, but more importantly is understandable and usable by business authors who aren't sql experts. It's achievable, honest!
------------------------------
Marc Reed
Original Message:
Sent: Wed October 23, 2024 03:18 PM
From: Greg Wilburn
Subject: How many tables in a Data Module
Thanks... that helps a lot.
I'm working with a legacy DB2 relational database on an IBM i... I don't know how you would classify it - but neither of those terms are immediately familiar to me.
I created the SQL views to flatten and simplify the underlying tables. I'm going to work on the relationships from that perspective. The cardinality could be my issue.
------------------------------
Greg Wilburn
Director of IT
Total Biz Fulfillment
Grantsville MD
Original Message:
Sent: Wed October 23, 2024 11:35 AM
From: Marc Reed
Subject: How many tables in a Data Module
It sounds like you have a SQL background which is helpful for anyone doing data modelling. Are you used to Third Normal Form databases?
Apologise if you know all this already...
Facts / Dimensions aren't Cognos terms - they are industry standard terms used for star schema data warehouse databases. A search of Google will show how these modelling concepts are different to TNF databases.
It may be that the source relational database is either a TNF transaction database or a star schema reporting data warehouse. Regardless of which you will typically get the best results if you model the source database in terms of dimensions and facts.
A typical sample used in Cognos is the product dimension. This consists of three physical normalises underlying database tables ( Prod Line, Prod Type, Prod). Within the Cognos data model we would model these three physical tables as a single Product dimension. A bit like you talked about creating views earlier. Its a very similar concept. We are creating views in the data module instead.
By modelling things in terms of Dimensions and Facts, and appropriate use of aliasing/shortcuting dimensions we can remove loop / ambiguous join paths.
The best piece of advice I can give anyone in modelling data is that you are trying to create a model for the report authors. Often I see people try and reproduce the physical database model in DM/FM which is the wrong approach. Model for the results you want - a logical reporting model. It sounds obvious, but I have seen DBAs try and use the tools and they reproduce the physical model which is of no use to anyone.
Backing this up - its important to use the cardinality on relationships that helps Cognos identify what is a fact and what is a dimension. Again these need to be the logical cardinality, rather than the underlying physical cardinality.
One place where data modules will fall down is in factless queries. For example I have dimensions for Calendar and Product and I have fact tables for sales, and another table for returns.
Product / Calendar and Sales join together, and so does Product / Calendar and Returns.
If I create a report with just Product and calendar on it I have a factless query. Which join path between Product and Calendar should I use?
The answer lies in the question - are you asking for which product were returned in each month, or which product you sold in each month.
Without a fact or shortcuts cognos would use the alphabetically first fact.
I mentions using cardinality to help cognos identify facts and dimensions. Using the above tables Product, Calendar, Sales, Returns - what should be the result if I add all four tables into a report? The novice would just inner join all these tables together. But what would happen if I had sales in Jan but no returns? Cognos recognises what are facts and dimensions and does separate facts for sale and returns queries and stitches them together, usually in the same SQL statement, ensuring no loss of data.
As I said - most modelling problems can be solved.
Modelling big DWs all comes down to the simple step of modelling dimensions, modelling facts and then just joining facts to dimensions.
------------------------------
Marc Reed
Original Message:
Sent: Wed October 23, 2024 11:08 AM
From: Greg Wilburn
Subject: How many tables in a Data Module
Marc,
Thanks once again for your reply. Let me back up a bit. I am an application programmer - 27 years of writing applications and reports from the same (legacy) database. Ten years ago I built SQL views over our database (with IBM's help) to use with a different BI tool. We created very basic reports that created HTML/XLS output that closely resembles Cognos lists and crosstab (the crosstab was MUCH easier in the other tool). Much of the Cognos terminology is foreign to me. Terms like "fact" and "dimension" tables.
The SQL views (i.e. tables) were described in this tool as metadata (data source for reports). We had about 6-10 core pieces of metadata. The metadata was comprised of several tables joined. Additionally, users could join metadata when building reports. Each "piece" of metadata was mutually exclusive of the other pieces. However, the same tables may be used in multiple metadata.
The problem I'm having is with developing a single data module to replicate the previous metadata, without creating loops that confuse the Cognos query engine. And, creating an intuitive data source for report authors. The same column names may be in a dozen data sources (so the "Find" is less than ideal).
What I've observed is the Cognos query engine taking the "wrong" path to access the data (the generated SQL is definitely not what I would create)
At this point we are just trying to replicate the basic list/crosstab type reports we currently have available in the old BI tool (without painting ourselves into a corner)
My issue is that I don't know what I don't know.
------------------------------
Greg Wilburn
Director of IT
Total Biz Fulfillment
Grantsville MD
Original Message:
Sent: Wed October 23, 2024 02:57 AM
From: Marc Reed
Subject: How many tables in a Data Module
Hi Greg - welcome to using Cognos. It's a great enterprise reporting tool. Learning a new toolset can be frustrating but we have a great online community that can help you! I have built many many enterprise reporting solutions using Cognos across large corporate data warehouses using hundreds of tables and haven't come across a data modelling problem I couldn't solve (yet!). So stick at it!
In answer to your questions. And I'll be honest!
Data Modules is Cognos latest newest next generation data modelling tool. It does the same thing as Framework Manager. The recommendation is that any new projects should start using data modules but may want to revert back to FM if there is an FM feature not yet in Data Modules. DM is slowly coming up to feature parity of FM but is still a long way off. One thing that DM has that FM will never has is navigation paths. So if you want drill down in dashboards/reports, without the complexity if FMs dimensional modelling, DMs are the way to go.
My opinion is that FM was built with the enterprise data modeller professional in mind so has multideveloper, large projects baked in, whereas DMs are very much a business user focussed tool that can lead to frustration for advanced data modellers. There was recently a blog post on using DMs with multidevelopers - look in the blog posts part of this website.
The question about multiple data modules - its not a question about modelling, but about the consuming tools such as reporting and dashboards. Whilst both can consume multiple data modules, dashboards certainly doesn't recognise common dimensions within multiple DMs. For example you have a common calendar in multiple data modules. You bring in multiple data modules containing the calendar. If you filter one calendar, you only filter that calendar. A bit of a downside. This can be overcome in reporting.
But in my projects we want DMs to be used in both dashboards and reporting. So whilst data modules are called modules - all my projects end up with one massive DM due to how they are used in dashboards. And yes this does lead to messy hard to follow DMs, which FM didn't have an issue with. Again look at the data module blog post referenced earlier and see if that overcomes these issues for you.
The question about modelling - loop joins and so on - regardless of the tool (FM/DM) both use the same query engine. So switching from DM to FM wouldn't magically fix these issues.
The thing to remember is that the cognos query engine works best when tables are modelled in terms of fact and dimension tables. This allows the full power of the query engine to resolve multi-fact queries.
On the web you may see FM documentation on data modelling - such as resolving loop joins and ambiguous join paths. Don't ignore this just because its about FM - the exact same concepts are used to resolve these issues in DM. DM has the same concepts of shortcuts and alias tables. The only thing it doesnt have is FMs namespaces (but we live in hope!).
Back to performance - that could be a million things.
If you're reporting against a database, then at the end of the day the reports are just writing sql and running sql against a database. You need to ensure the data model is correct so that the tool generates the kind of sql you would expect to see. After that there comes all the performance topics of caching, local processing etc. But none of that is of any use until you get the kind of sql you expect.
------------------------------
Marc Reed
Original Message:
Sent: Tue October 22, 2024 12:07 PM
From: Greg Wilburn
Subject: How many tables in a Data Module
We are new to Cognos. Our version is 12.x
Much of the resources online is for prior versions, mentioning things we don't have (or have been renamed) so it's very confusing (Framework Manager is just one).
I am trying to get off on the right foot... but we started by bringing all of our "reporting" tables into a single Data Module, then began "re-creating" our reports. I quickly discovered that
- It was overwhelming for a report author to find various columns that are repeated in multiple tables.
- Many of the reports were slow and inaccurate because of the join loops - customer address is joined to customers, orders, and invoices
So I'm not sure how to begin to simplify things. The current data module "relationships" look like an "asterisk" with "stars" mixed in overtop
So my questions are (best practices)
- Should I break things up into separate Data Modules (instead of one large one)?
- How many tables are "too many" for a single data module (i.e. when does it get too complicated for a report author)?
- Do I need to organize multiple data modules in multiple folders (in other words, if I decide to move a data module to a subfolder, would it break the reports already created)
Any general recommendations would be appreciated.
------------------------------
Greg Wilburn
------------------------------