Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
pradnya_amol
Creator
Creator

Help in Data Modelling, App Optimization

Hello All,

Sometimes a small, tiny thing doesn't get solve; please help me in data modelling; don't know what am I missing. Check the script and let me know what else I can do to make my Data Model perfect. I have a few conditions on UI which is making my App very slow on Access Point.

Attached is the screenshot for Data Model.

BVAFactId in BVA Table:- Information Density: 100%        Subset Ratio: 1%

BVAFactId in Key Table: Information Density: 100%        Subset Ratio: 31%

 

M14FactId in M14Table:- Information Density: 100%        Subset Ratio: 99%

M14FactId in Key Table: Information Density: 100%        Subset Ratio: 31%


%DATE_KEY in Master_Calendar: Information Density: 100%        Subset Ratio: 75%

%DATE_KEY in Key Table: Information Density: 100%                        Subset Ratio: 100%

 

Script:

Master_Calender:
LOAD
CalendarMonthName,
Num(CalendarMonthName) as CalendarMonthNumber,
CalendarQuarter,
CalendarYear,
CalendarMonthAndYear as %DATE_KEY,
date(CalendarMonthAndYear,'MM.YY') as CalendarMonthAndYearNum,
CalendarMonthAndYear,
CalendarQuarter & '-' & Right(CalendarYear,2) as CalendarQuarterYear,
If(CalendarYear <= Year(today()) and CalendarYear>=$(vPreviousYear),CalendarYear) as FilterCalendarYear

FROM
\\deawiwqlvt041.syngentaaws.org\QlikView\QlikViewStorage\SourceDocuments\Shared Folder\Master Calendar\MasterCalendar.QVD
(qvd)
Where CalendarYear>=($(vPreviousYear)-1) and CalendarYear<=$(vNextYear);

Key:
LOAD FactId as M14FactID,
FactId as BVAFactID,
date("Calendar Date",'MMM YYYY') as %DATE_KEY
FROM
[\\deawiwqlvp041.syngentaaws.org\QlikView\QlikViewStorage\SourceDocuments\Transformation Layer\Global\MRH\Qlikmart\Sales\QVD\T_G_MRH_QM_MRH1_Consolidated_Sales_Key.QVD]
(qvd) where Year("Calendar Date") >=($(vPreviousYear)-1) and Year("Calendar Date") <=$(vNextYear);

BVA:
LOAD FactId as BVAFactID,
[BVA.ACT Value]
FROM
[\\deawiwqlvp041.syngentaaws.org\QlikView\QlikViewStorage\SourceDocuments\Transformation Layer\Global\MRH\Qlikmart\Sales\QVD\T_G_MRH_QM_MRH1_BVA_Fact.QVD]
(qvd) where Exists(BVAFactID,FactId);

M14:
LOAD FactId as M14FactID,
[Sales_PC.VAR BUD PY STD Value]
FROM
[\\deawiwqlvp041.syngentaaws.org\QlikView\QlikViewStorage\SourceDocuments\Transformation Layer\Global\MRH\Qlikmart\Sales\QVD\T_G_MRH_QM_MRH1_Sales_Product_Costing_Fact.QVD]
(qvd) where Exists(M14FactID,FactId);

Labels (2)
1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

For me it looked that your table Key is a link-table between two fact-tables. From a UI performance point of view is this usually the slowest type of datamodel. Therefore I suggest to consider a complete change of the datamodel in the direction of a star-scheme by merging (joining/mapping(concatenating) the two fact-tables into a single one.

- Marcus

View solution in original post

5 Replies
jyothish8807
Master II
Master II

Hi Pradnya,

Subset ratio of 1% is definitely not good. May be try like this:

 

Key:
LOAD FactId as %Key, 
date("Calendar Date",'MMM YYYY') as %DATE_KEY
FROM
[\\deawiwqlvp041.syngentaaws.org\QlikView\QlikViewStorage\SourceDocuments\Transformation Layer\Global\MRH\Qlikmart\Sales\QVD\T_G_MRH_QM_MRH1_Consolidated_Sales_Key.QVD]
(qvd) where Year("Calendar Date") >=($(vPreviousYear)-1) and Year("Calendar Date") <=$(vNextYear);

BVA:
LOAD

FactId as %Key,
[BVA.ACT Value]
FROM
[\\deawiwqlvp041.syngentaaws.org\QlikView\QlikViewStorage\SourceDocuments\Transformation Layer\Global\MRH\Qlikmart\Sales\QVD\T_G_MRH_QM_MRH1_BVA_Fact.QVD]
(qvd) where Exists(%Key,FactId );

M14:
LOAD FactId as %Key,
[Sales_PC.VAR BUD PY STD Value]
FROM
[\\deawiwqlvp041.syngentaaws.org\QlikView\QlikViewStorage\SourceDocuments\Transformation Layer\Global\MRH\Qlikmart\Sales\QVD\T_G_MRH_QM_MRH1_Sales_Product_Costing_Fact.QVD]
(qvd) where Exists(%Key,FactId );

Best Regards,
KC
pradnya_amol
Creator
Creator
Author

Its not helping Dear. I tried it. It might be because FactId is associated to BVA and M14. Same Key has numerous values where 99% is for M14, 1% to BVA.

marcus_sommer
MVP & Luminary
MVP & Luminary

For me it looked that your table Key is a link-table between two fact-tables. From a UI performance point of view is this usually the slowest type of datamodel. Therefore I suggest to consider a complete change of the datamodel in the direction of a star-scheme by merging (joining/mapping(concatenating) the two fact-tables into a single one.

- Marcus

View solution in original post

pradnya_amol
Creator
Creator
Author

Yes, you are correct. We are having Fact Constellation Schema wherein we have 3 Fact Tbale and 1 Main table which is further mapped with 8 Dimension Tables. Thank for your input, was trying the same what U have suggested.

qliksus
Specialist II
Specialist II

Qlikview makes joins on the fly when users make some selection . So lesser the number of tables it has to search quicker the result will be . Lets take the case where you have 2 fact tables and 1 dimension which needs to be connected to just one fact table , in such a scenario why is joining the 2 fact tables a bad thing ?