data model suggestion

[surajap123]

Hi All,

I have 2 fact table shown below. Both have same structure.

The problem is, I will be placing both the reports in separate sheets, so the dimensions- ProductName and Location should have the appropriate values of that Report, so users don't see additional values in these dimensions.

Please suggest, the best way to do the data modelling for my requirement.

Report1:

LOAD *

     INLINE [

    ProductName, MonthYear, Location,Sales

    AA, Jan-17, US,100

    AA, Feb-17, UK,200

    BB, Jan-17, DE,200

];

Report2:

LOAD *

     INLINE [

    ProductName, MonthYear, Location,Sales

    AA, Mar-17, US,100

    KK, Feb-17, JP,200

    MM, Jan-17, DE,300

];

Please note that the real table has lot many fields and 2nd table is a new one i am going to add to existing model. So i am worried that concatenation and data flag approach will force me to change all the expression in the existing objects.

Thanks!

[arvind_patil]

Dear Suraj,

Please find the attachment:

Code:

Report1:

LOAD *

     INLINE [

    ProductName, MonthYear, Location,Sales

    AA, Jan-17, US,100

    AA, Feb-17, UK,200

    BB, Jan-17, DE,200

];

NoConcatenate

Report2:

LOAD *

     INLINE [

    ProductName, MonthYear, Location,Sales

    AA, Mar-17, US,100

    KK, Feb-17, JP,200

    MM, Jan-17, DE,300

];

FinalReport1:

Load

ProductName, MonthYear, Location,Sales,

'Report 1'as Flag

  Resident Report1;

DROP Table Report1;

Concatenate

FinalReport2:

Load

ProductName, MonthYear, Location,Sales,

'Report 2'as Flag

  Resident Report2;

DROP Table Report2;

Thanks,

Arvind Patil

[jagan]

Hi Suraj,

make two tables as island in your datamodel by using Qualify.  And use different alternate states in both the sheets, this way you can easily implement this.

Regards,

Jagan.

[qliksus]

If you don't want to change expression then have a flag created in both the tables and have the flag selected in the respective sheets i.e Sheet1 will have the value Flag = 1 selected and sheet2 would have Flag = 2 selected

Report1:

LOAD * ,'1' as Flag    INLINE [

    ProductName, MonthYear, Location,Sales

    AA, Jan-17, US,100

    AA, Feb-17, UK,200

    BB, Jan-17, DE,200

];

Report2:

LOAD * ,'2' as Flag    INLINE [

    ProductName, MonthYear, Location,Sales

    AA, Mar-17, US,100

    KK, Feb-17, JP,200

    MM, Jan-17, DE,300

];

[surajap123]

Thanks for your time. I have not interested in the flag approach, as it require me to do the changes in all existing objects in the dashboard.

[surajap123]

Thanks Jagan. Users still want to filter the data based on the dimensions that are common. Suppose if user select country- JP, then switch to other sheet to see what happened in Report-1(which should break the chart, which is ok as no data available).

The major issue is, i don't want dimensions(country,location) in the listbox to contain all the values belonging to both reports.

[jagan]

This will add more complexity you have to derive multiple fields for country and location and you have to use triggers for this.

Regards,

Jagan.

[surajap123]

ok. Can i go with link table.

[jagan · Accepted solution]

I think it is the same whether you concatenate or use link table, if you want to carry the selections you have to concatenate two data sources, if you concatenate you will get additional values..

Regards,

jagan.

[surajap123]

I get it. Thanks for the clarification.