Question about date table management

[josborne]

Hi all.  I'm looking for guidance on a data model I have created.  I believe I have taken the wrong approach and am struggling to understand how to better manage multiple key dates in a data set.  Here's he summary:

Table Structure

Table 1 - main project data  (assignmentdate is the key date)

Table 2 - associated effot data related to Table 1 by project_ID (effortweekdate is the key date)

Table 3 - separate table of another team's projects not directly repated to Table 1 or Table 2 but activity I would like to include along side  Table 1 reporting (date bid team received is the key date)

Table 1a - a referenced table/query I created with a subset of Table 1 data specific to the project outcome (closeddate is the key date)

Table 3a - a referenced table/query I created with a subset of Table 3 data specific to the project outcome (bid_outcome_date is the key date)

Table 4 - a calendar I created which formats the financial year etc. as I need it

Table 5 - a copy of Table 4

My Approach

I have separated the project outcomes into 2 separate tables (1a and 3a) as I have mentioned above.  

I have created 2 sepatare date tables (4 and 5) and split them as per the diagram, the relatioships in the green and red boxesprobably as expected, filtering by financial year (FiscalYr) yields unpredictable results in the visuals as I'm sure I've over-complicated the relationships. 

I'm looking for advice as I can't seem to find detail on how to set this up properly.  Ideally I think I only need Tables 1,2 and 3 and one date table, I just can't work it out and any guidance would be appreciated.

[hnguy71 · Accepted solution]

Hi @josborne 

At high level, I probably would do this:

Table 1:

sw_assignmentdate --> Date [active]

sw_closedate --> Date [inactive]

Table 2: 

sw_effortweekdate --> Date [active]

Table 3: 

date bid team received --> Date [active]

You can get rid of the rest. Rely on measures and calculations to return you the correct values.

I would probably also make some additional changes to your model. Seems you have columns that are not necessary. For example, I would probably move / merge the transactional data from Win Rate Tracker to Pa_Tbl_Main and re-purpose the Win Rate Tracker table as a dimension table.

[krishnakanth240 · Accepted solution]

Hi @josborne 

Can you implement the data model with these relationships and confirm if the calculations are working
Ensure to have only one Date table

Table 1 – Project
Active relation|| Date[Date] to Table1[Assignment Date]

Inactive relation || Date[Date] to Table1[Closed Date]

Table 2 – Effort

Active relation || Date[Date] to Table2[EffortWeek Date]

Table 3 – Bid Tracker
Active relation || Date[Date] to Table3[Date BidTeamReceived]

Inactive relation || Date[Date] → Table3[BidOutcome Date]

You can bring Table 1a columns into Table1 based on ProjectID column. Also Table 3a columns into Table3 based on BidID column. Please ensure to have only required columns for reporting in Table1 and Table3.

[cengizhanarslan · Accepted solution]

1) Stop duplicating the Date table

Delete Date table #5. You only need one calendar (your FinYearCalendar).

If you need multiple date roles, you handle that with inactive relationships and measure using USERELATIONSHIP(), not multiple calendars.

2) Don’t create “subset fact copies” (1a, 3a) just for outcome dates

Again you do no thave to replicate Fact tables as it would increate your memory usage, thus you might have performance issiues as data gets larger. Instead consider using inactive relations & USERELATIONSHIP() for those facts with different date keys.

3) If you need to switch the “date role” dynamically

If your users need a slicer like “Use Assignment Date vs Closed Date”, the best practice is a Calculation Group to apply the right USERELATIONSHIP automatically across measures.

[josborne]

OK thank you that does make sense I'll investigte how to do that properly.

[krishnakanth240 · Accepted solution]

Hi @josborne 

Can you implement the data model with these relationships and confirm if the calculations are working
Ensure to have only one Date table

Table 1 – Project
Active relation|| Date[Date] to Table1[Assignment Date]

Inactive relation || Date[Date] to Table1[Closed Date]

Table 2 – Effort

Active relation || Date[Date] to Table2[EffortWeek Date]

Table 3 – Bid Tracker
Active relation || Date[Date] to Table3[Date BidTeamReceived]

Inactive relation || Date[Date] → Table3[BidOutcome Date]

You can bring Table 1a columns into Table1 based on ProjectID column. Also Table 3a columns into Table3 based on BidID column. Please ensure to have only required columns for reporting in Table1 and Table3.

[josborne]

Thanks very much that really helps.

[hnguy71 · Accepted solution]

Hi @josborne 

At high level, I probably would do this:

Table 1:

sw_assignmentdate --> Date [active]

sw_closedate --> Date [inactive]

Table 2: 

sw_effortweekdate --> Date [active]

Table 3: 

date bid team received --> Date [active]

You can get rid of the rest. Rely on measures and calculations to return you the correct values.

I would probably also make some additional changes to your model. Seems you have columns that are not necessary. For example, I would probably move / merge the transactional data from Win Rate Tracker to Pa_Tbl_Main and re-purpose the Win Rate Tracker table as a dimension table.

[josborne]

Thank you great advice.