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.
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.
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.
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.
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.