Help - Data Modelling

[manojkulkarni]

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..

[HirisH_V7]

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

[manojkulkarni]

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.

[HirisH_V7]

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

[manojkulkarni]

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

[HirisH_V7]

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

[manojkulkarni]

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 ?

[HirisH_V7]

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

[manojkulkarni]

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

[HirisH_V7]

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.