topic Re: Help - Data Modelling in QlikView

Help - Data Modelling

manojkulkarni — Wed, 20 Jan 2016 04:15:20 GMT

Good Morning Friends,

I need experts suggestion on data modeling for one of my requirement with multiple facts. What is the best practice & optimized solution for below requirement.

I have 5 facts table, four of tables have common key clientID & one doesn't have client ID but that data has to consider from date.

All 4 facts have different measures and has its own transaction dates. Dimension will the client master along with geographic details etc.

What is the best approach to handle this kind of data & how should we make master calendar map it to Fact so that every question from different fact should get answered.

Any suggestion is much appreciated..

Re: Help - Data Modelling

HirisH_V7 — Wed, 20 Jan 2016 04:26:04 GMT

Hi,

If possible post some sample data of one fact and dimension table and the model will be based on your requirement what you want to show at the UI level .so we should think that way to create data model.

-Hirish

Re: Help - Data Modelling

manojkulkarni — Wed, 20 Jan 2016 04:30:40 GMT

Fact tables will log information for every day. UI level will be presenting count of log in each facts with different category, severity etc. All data needs to be drilldown from year - qtr - month-week & day.

We will be doing YoY, MoM compares, trends for rolling 12 months etc.

Re: Help - Data Modelling

HirisH_V7 — Wed, 20 Jan 2016 04:42:04 GMT

Then may be like this,

Data:
LOAD * INLINE [
ID,Date
1,19/01/2016
2,20/01/2016
];


Fact1:
LOAD *,
'Flag1' as Flag


 INLINE [
ID,Sales
1,23
2,24
];


Fact2:
LOAD * ,
'Flag2' as Flag
INLINE [
ID,Sales
1,23
2,254
];


Fact3:
LOAD * ,
'Flag3' as Flag
INLINE [
ID,Sales
1,233
2,254
];

Here the flags from each table can be used at front end .Attach your calendar with the Data.

PFA,

Hope this helps

Hirish

Re: Help - Data Modelling

manojkulkarni — Wed, 20 Jan 2016 04:50:17 GMT

Below are sample data format. If you look at data all client IDs are not present in both the tables & also logs are not present everyday.

Fact1
ID	LogID	LogDt	Type
1	1	1/1/2016	H
1	2	1/2/2016	L
2	3	1/1/2016	H
2	4	1/2/2016	H
3	5	1/1/2016	H
3	6	1/1/2016	H
4	7	1/1/2016	H
4	8	1/3/2016	H

Fact2
ID	LogID	LogDt
3	1	1/11/2016
5	2	1/1/2016
7	3	1/3/2016

Client
ID	Cname
1	A
2	B
3	C
4	D
5	E
6	F
7	G

Re: Help - Data Modelling

HirisH_V7 — Wed, 20 Jan 2016 05:14:10 GMT

Hi,

Check this,

Data:

LOAD * ,

LogDt as Date

INLINE [

ID, LogID, LogDt, Type

1, 1, 1/1/2016, H

1, 2, 1/2/2016, L

2, 3, 1/1/2016, H

2, 4, 1/2/2016, H

3, 5, 1/1/2016, H

3, 6, 1/1/2016, H

4, 7, 1/1/2016, H

4, 8, 1/3/2016, H

];

Concatenate

Fact1:

LOAD *,

LogDt as Date

INLINE [

ID, LogID, LogDt, Type

1, 1, 1/1/2016, H

1, 2, 1/2/2016, L

2, 3, 1/1/2016, H

2, 4, 1/2/2016, H

3, 5, 1/1/2016, H

3, 6, 1/1/2016, H

4, 7, 1/1/2016, H

4, 8, 1/3/2016, H

];

Concatenate

Fact2:

LOAD *,

LogDt as Date

INLINE [

ID, LogID, LogDt

3, 1, 1/11/2016

5, 2, 1/1/2016

7, 3, 1/3/2016

];

Client :

LOAD * INLINE [

ID, Cname

1, A

2, B

3, C

4, D

5, E

6, F

];

Hope this helps,

PFA,

Hirish

Re: Help - Data Modelling

manojkulkarni — Wed, 20 Jan 2016 05:45:41 GMT

Thanks for your suggestion. Is it better option to concatenate when we have more than 60 fields in each fact tables & hardly 10 columns are in common or would you suggest other option ?

Re: Help - Data Modelling

HirisH_V7 — Wed, 20 Jan 2016 05:49:48 GMT

Hi,

Yes i hope so,

If we go with left join with data means Some info corresponding to ID's will not Acquire.

HTH,

Hirish

Re: Help - Data Modelling

manojkulkarni — Wed, 20 Jan 2016 06:03:50 GMT

didn't get you harish. Tables will have million records

Re: Help - Data Modelling

HirisH_V7 — Wed, 20 Jan 2016 06:55:50 GMT

Hi ,

If we do left join means only the first table and remaining tables matched records will be fetched right .so some info will be missed. so we are going with concatenation .

HTH,

Hirish.