topic Re: Help in Data Modelling, App Optimization in QlikView

Help in Data Modelling, App Optimization

pradnya_amol — Sat, 16 Nov 2024 03:20:41 GMT

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);

Re: Help in Data Modelling, App Optimization

jyothish8807 — Tue, 11 Jun 2019 06:10:04 GMT

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 );

Re: Help in Data Modelling, App Optimization

pradnya_amol — Tue, 11 Jun 2019 06:46:42 GMT

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.

Re: Help in Data Modelling, App Optimization

marcus_sommer — Tue, 11 Jun 2019 08:51:02 GMT

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

Re: Help in Data Modelling, App Optimization

pradnya_amol — Tue, 11 Jun 2019 09:06:03 GMT

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.

Re: Help in Data Modelling, App Optimization

qliksus — Wed, 17 Jul 2019 11:19:44 GMT

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 ?