Dear Experts, Need advice and solution on following:
I have a fact table with pre computed total and summary level data on SQL for KPI.
KPIs consist of various UoM like Metric Tonne, %s, etc. The data is shared via excel template by users on 'Total' level and some granular drilldown level. On Qv it is used to display as it is without any sum/ aggr functions. I have been able to achieve that on Qv
The structure of the table is attached in a Qvd file (Fact_Kpi.qvd)
There is one more subject area where finanicials are been stored on COA granularity. Various calculations/ aggr and COA hierarchy is built to present the financials on dashboard. (Fact_Financials.qvd) attached for structure
Both the subject area i.e. KPI and Financials have no physical join apart from entity and time dimension and are independent.
The Dim_Entity stores child parent relation too where a hierarchy is built from Entity to Region to Plant, etc at various levels
Drilldown in the Fact_Kpi table are dynamic.. For instance today if Plant A is in Drilldown1Value for ABC Entity then Plant B may be in Drilldown3Value for XYZ Entity.
Question is: I have to plot Kpi Name and Coa Desc as a common dimension in a chat
Will I be able to plot KPI Name from Fact_KPI into a dimension along with a Coa Descp from Fact_Financial ? As of now both are separate table with only entity and time dimension in common. Also the COA codes rolls up in the hierarchy whereas KPI codes are directly fetched from source excel templates and displayed as it is.
If I will be able to plot 2 different dimensions from 2 separate fact tables without any relations then what will be the pros and cons.
What will happen to the Values from both the tables? Will it be able to create some logical and physical joins in such a condition? Can I use concatenation method for both tables?
How to do I store the dynamic Drilldowns then?
Coa gets build on Hierarchy and as KPI runs on no aggregation mode will I be able to use any Sum, Avg, etc and still make sure that KPI does not aggregate to any levels.
I will be obliged if experts can advise me whether I should keep both the tables separate as doing it today and still have some work around.
I had a challenging time handling the KPIs before and had posted this earlier:
However I could handle this using SQL query.
