The conversation spurred by my What are Cognos Data Modules blog post continues with an excellent question by Heather, who asks what to do about maintaining a single source of truth in Cognos Analytics while still using data modules. This does pose a challenge. I’ll discuss some options below and rate them using a scale of 1 – 5 Analysis Studio cubes.
Option 1: Don’t Use Data Modules
One common way to solve this problem – sadly – is to not use data modules in any capacity. Turn them off for end users and IT and stick with tried and true Framework Manager. If it aint’ broke, don’t fix it. This approach will preserve single source of the truth but at significant cost:
- No access to new features… ever (FM is not receiving feature updates)
- FM development cycles are very long
- End users will have no ability to model data in Cognos…
- Which means they won’t be able to do anything complex in Cognos…
- Which means they’ll use Power BI instead!
Odds are if you’re reading this blog you’d prefer your users work in Cognos vs Power BI or Tableau. Choosing option 1 will definitely preserve single source of the truth but at the cost of relevance.
Final Verdict
Option 2: Treat Data Modules Exactly Like Framework Manager
Contrary to popular belief – and IBM’s marketing – data modules are not just for self-service users. The feature set of data modules has reached near parity to Framework Manager (with some notable exceptions) and has surpassed it in some very significant ways – relative time, for example. It’s also important to understand that Cognos security applies to a data module in just the same manner as a Framework Manager package.
There is in principle no reason you can’t migrate your IT modeling work to data modules wherever possible while restricting their use entirely to the IT team. In this way you can future proof your development and take advantage of a growing list of very cool features while leaving your traditional BI workflow and single source of truth intact.
However you foreclose the opportunity to take Cognos to the next level and enable real self-service.
Final Verdict
Option 3: Use Linked Tables
Linked tables allow to you use tables from one data module as the source for tables in another data module. These tables inherit all changes from the source, including field name changes, field additions/subtractions, calculation changes, SQL changes, etc…
Using these links, it’s possible for IT to build and maintain a high quality, tested and accurate data model while still providing a significant degree of end user flexibility. In the image above, an end user has imported three linked tables over which IT has 100% control and joined them to an excel spreadsheet. To do this follow these steps:
- Create one or more data servers for your source databases
- Restrict access to create new models using those servers to IT only
- Build a data module that contains the tables you wish to govern
- Save the data module in a folder where users can access it but not overwrite it
- Remove the ‘break link’ capability from users. This means they cannot edit these tables in any way
- Teach them how to use link tables and let them go wild!
By following these steps you can ensure that all self-service data modeling for governed sources uses your logic, your calculations and even your joins – the joins you define are imported alongside your linked tables. Your users can still model to their heart’s content with their departmental or personal data but when it comes to your governed enterprise data, your rules come first.
Final Verdict
Option 4: Stop, collaborate and listen
For better or worse, speed and flexibility have decisively crushed accuracy and security in the battle for BI market share. End users routinely show up to meetings with three competing Power BI/Tableau workbooks that disagree on all relevant metrics but were produced in four days and look incredible. And people seem absolutely thrilled a this state of affairs based on recent events!
Therefore Option 4 isn’t so much a technical solution as it is a cultural one. Yes, you adopt the strategy outlined in Option 3 – but you do so cheerfully with the knowledge that your end users absolutely will colossally screw up the data in Cognos, and that it’s your job to identify and fix it when this happens. This is by far the hardest thing for long time Cognos pros to do, and it was extremely hard for me to come around to this way of thinking. The final piece of the puzzle was the following realization:
- Users are incredible innovators in the art of producing bad data – they truly cannot be stopped
- Trying to enforce 100% data accuracy in Cognos just drives them to other tools
- Errors produced in those other tools are completely outside of my ability to monitor, influence or correct – as are those users
- This cycle absolutely destroys ‘single version of truth’ for an organization as a whole, even if it is maintained for Cognos
Switching to a more open, collaborative approach in Cognos has the benefit increasing user satisfaction, organizational perception and eventually investment in Cognos. I have seen this work at my clients who have embraced it.
Final Verdict
That’s right, the final verdict for Option 4 is five PowerPlay Studio cubes of approval – the highest approval I can give.
PowerPlay was way better than Analysis Studio anyway.
It’s an excellent article which re-enforced my perception for using Data Modules in an organization.
Thank you!
Thank you for writing about this Ryan! This is very helpful, we are going to try out 3 and 4 to see how it goes.
I haven’t raised the white flag and given in to #4 yet. #3 seems like the perfect solution combing governance with exploration and is something Power BI cannot do. With that said, perhaps #4 is inevitable… Does 11.1 have a mechanism for users to rate data modules? This perhaps would be a way to move toward “truth” democratically…
Thanks this article is really useful as we start to move our customers towards data modules. One question though – in Option 3 you say to “Restrict access to create new models using those servers to IT only”
I might be missing something, but how can I do that? Whenever I adjust permissions against a data server, it results in users not being able to run any reports against the data module I’ve created?
Great article. This is definitely the way to go with using Cognos.
I am also experimenting with Data modules and Data servers, linked tables and autorization.
But I also would like to know how to ‘Restrict access to create new models using those servers to IT only’.
Otherwise it would be possible for users to create a new datamodule and add tables from a data server
which they would not have access to thru the data module.
And I don’t wat to specify autorisation on every table in the data server or have multiple data servers.
This is a great question – one that deserves its own article. I’ve added it to my content calendar.
I recently discovered your blog and it’s always a pleasure to read your articles.
What about modelling traps in Data Modules (vs Framework Manager)?
Multiple paths, loops, Facts behaving as dimensions depending on the table joined, etc
Do we adress them in the same way as we did with FM?
Thanks.
Looking forward to your next postings
Generally speaking you handle of all these situations the same way you handle them in FM, which has become possible thanks to features like custom tables. This is a great idea though, let me put something together for an update.
Great Post! I may be a little late to the party here but can you offer some advice on how to “Remove the ‘break link’ capability from users”? I see you mention in another blog post that this can be handled in security but I’m having a hard time finding it. Can you offer some advice on where and how this functionality can be disabled so that linked modules will work as described? Thanks for the great info!