DATA MODEL

[CJ_96601]

Hello, 

I have 4 facts table and have this model as follows:

  Table 1                                     

  Key Index

     - Emp No

     - Hire Date

     - Termination Date

Table 2

Key Index

   - Emp No

   - Date

Table 3

Key Index

   - Emp No

   - Date

Table 3

Key Index

   - Emp No

   - Date

Calendar

  - Date

below is the diagram for easy understanding.

My question is how can i link table 2 [date] , table 3 [date]  and table 4 [date]  to calendar[date] keeping star schema model.   

Or is there a way to do measure keeping table 1 related to calendar and not table 2,3 and 4?

Thanks for your help.

Regards, 

[jstorm]

Post a screenshot of your 'Model' tab in Power BI.

[camargos88]

Hi @CJ_96601 ,

Can't you just use 1 Emp table (append them)... 

Also, why do you need those 3 tables, once you have the data on table 1 ?

Ricardo

[CJ_96601]

Hello @camargos88

Thank you for your response.

Tables are from our ERP via ODBC.    I tried append but refresh takes forever.

Is there any other solution, until append is fix.

Regards, 

[camargos88]

@CJ_96601 ,

Are you using import or direct query ?

[CJ_96601]

@camargos88,

can you elaborate, please

[camargos88]

@CJ_96601 ,

Why don't you submit a query to your source like:

TABLE 1 UNION ALL TABLE 2 UNION ALL TABLE 3....

Also, check this link, maybe it's gonna help with your model:

https://radacad.com/directquery-live-connection-or-import-data-tough-decision

Ricardo

[CJ_96601]

@camargos88 

It is not that easy...considering how oracle is .

thanks for the link.  

[CJ_96601]

@camargos88 

On another note, i know this is not related to my previous question, please refer to the screen shot below:

Why bar graph ended up that way , when using below measure?

  CALCULATE(DISTINCTCOUNT(ODBC[EMPNO])+0,

USERELATIONSHIP(ODBC[TERMDATE], 'Calendar'[Date]),

not(ISBLANK(ODBC[TERMDATE])+0),

ODBC[REASON]="TRANSF") - // THIS SUPPOSE TO FILTER

Secondly, can i use "<= CALENDAR[DATE]" with the above measure?

Regards,

[camargos88]

@CJ_96601 ,

It looks like a problem with your model design, check the table connections.

Would help if you post how you are connections those tables. But try to be simple with the model, redesign it with the append ou union.

Ricardo

[camargos88]

@CJ_96601 ,

Once you have the queries, you can go to Advanced Mode e write your custom query:

 

[CJ_96601]

@camargos88 

I will try this once things get back to normal and revert.

I have no VPN at the moment and lockdown is in place...

Thanks