Hi gurus,
I created FM model from relational data source using brand, date,plan group, actual fact table, target fact table.
I separated logic layer into two namespaces: relational and dimensional
Relational has all relational query subjects from database layer.
Dimensional has regular dimension created from plan group table from relational layer, which forms hierarchy and a measure dimension that has all measures from two fact tables in relational layer.
Then i created shortcuts of query subjects to be placed in presentation layer, that has a relational table of brand, hirrarchy of plan and measure dimension
Now when, i create report from package, and use facts from mesure dimension with brand ... it displays same data for all the brands.moreover takes tooo much time to run
How to rectify this problem?
attached is the screenshot for clarification
it is generating a big query instad of minimal one as below:
Expected Query
select "Fact_Actual"."DailyChurnCount" AS "Daily_Churn",
"Brand"."OperatingBrand" AS "Brand" from "PyxisDatamart"."dbo"."FactNetAddsDetail" "Fact_Actual",
"PyxisDatamart"."dbo"."DimCompanyEntities" "Brand" where "Brand"."CompanyEntityKey" = "Fact_Actual"."CompanyEntityKey"
Real Query:
select "T0"."C0" AS "Daily_Churn", "T1"."C0" AS "Brand" from ( select sum("Fact_Actual"."DailyChurnCount") AS "C0" from "PyxisDatamart"."dbo"."FactNetAddsDetail" "Fact_Actual", "PyxisDatamart"."dbo"."DimCompanyEntities" "Brand" where "Brand"."CompanyEntityKey" = "Fact_Actual"."CompanyEntityKey") "T0",
( select "Brand"."OperatingBrand" AS "C0" from "PyxisDatamart"."dbo"."FactNetAddsDetail" "Fact_Actual",
"PyxisDatamart"."dbo"."DimCompanyEntities" "Brand" where "Brand"."CompanyEntityKey" = "Fact_Actual"."CompanyEntityKey") "T1"
The measure dimension should store measure definitions from a facttable. Check the model I recently send you
Yes blom. I created a relational and dimensional namespace under logical layer. But brand doesnt have any hierarchy thus i just created 2 hierarchies for plan group and date and 1 measure dimension from 2 fact tables in relational namespace. It seems that since brand table is lying in the relational namespace, abpnd when i select a measure from measure dimension abd beand from relational namespace there is no relanship between them thus 1 result.does that mean that i have to create single level regular dimension for brand in dimensional namespace to inherit the relation?