Datamodeling-challenge: add company-dimension in relational datamodel
Hi QlikCommunity,
A question:
For a client , I need to add a new dimension "company" to a large set of qlikview-reports.
For some reports this is no issue and for some it is quite a challenge, and I'm looking for your advise to analyze the impact and right solutions.
Background-info
In the ETL (outside QV) we have already made sure that "company-ID", which is coming from multiple source-systems, is transformed to a "generic_company_ID" (so when we integrate multiple sources, we can use that ID for all source-systems
In all .qvd files that we get from source-systems, that this "generic_company_id" is available as a column (so for every record it is clear from which company it is
We want to avoid splitting the reports per company and need to have company as a dimension in all reports, so if needed, we can apply section-access and dynamic data-reduction when data should not be shared per company
Requirement is to have a listbox, named company, on which all data in the report will be filtered
[Scenario 1]: "easy reports"
The reports that are easy to change, are reports with a star-model: 1 fact-table and multiple dimensions. Here we have a new column in the fact-tabe (the "generic_company_ID"), we have a new dimension (company), and the keys/relations to the dimensions need to be updated to include the "generic_company_ID"
[Scenario 2]: "Difficult reports"
More difficult reports are reports with a relational datamodel (for e.g. 25 or 30 entities in the QV-datamodel, not being a star-model but relational). If I just load the new field generic_company_id into all 25 entities and associate the company-dimension with t his, I will for sure have loops/circular references in my datamodel.
Main-question:
How can I add this company-dimension in such a way that data can be separated per company?
These difficult reports have a "split": when there already is fact-table (or a "main table") where the "generic_company_id" can be added, and I'm 100% sure that the rest of the datamodel is filtered when I select company 1 or 2, the problem is solved.
Sub-question 1:
In this scenario (lets say [scenario 2a]) I need to know how can be 100% sure that the rest of the datamodel is filtered when I select company 1 or company 2. How can I check this?
Then there is the most difficult scenario: [scenario 2b]: that in my relational model, there are multiple tables where I'm not 100% sure (or have serious doubt) which is the "main table", and I'm not sure if all data of the other tables will be filtered correctly when I select company 1 or 2.
An example where it can go wrong, is that when I associate the company-dimension to fact-table 1 (lets say payments), which has a relation to fact-table 2 (lets say customers) (probably via a few in-between-tables), where fact-table 2 also has data that is not associated to fact-table 1 (so there are customers without payments (or if you associate the company-dimension to customers: it will go wrong when there are payments other than only from customers).
Sub-question 2:
Is the only approach to "split those reports"? so you can filter all .qvd's loaded based on company_id? Or is there another approach?
I have already figured out that sometimes a link-table can help (for e.g. I have a report with only 2 fact-tables that had a relation). Here I can add a link-table. What I like to know is if it should always be possible to solve this via link-tables or if there is some limit (and if so, where)? I have the feeling that when you have many entities, you will soon get circular references... So I need to know where link-tables are needed and where not...
I hope this large post is clear. If not, let me know.
