Data Modeling Question - Projects & Tickets

[Anonymous]

Hey everyone,

I'm working through a scenario that should be pretty common but so far has been throwing me off a little bit.

I'm getting some data from our development team that is organized in the following tables:

Initiatives

Projects

Tickets

Resources

• Initiatives can have one or more Projects.
• Projects can have one or more Tickets.
• Tickets have owners who are Resources.

For Initiatives, Projects, and Tickets, each of the provided tables contains one unique record for each item. For the same tables, each contain a Start & End Goal Date as well as Start & End Actual Date. 

I'm struggling with how to break these into a standard star schema format since I don't see a clear distinction between what the dimension tables should be and what the fact table should be. For example, as a ticket is updated, the unique record for that ticket is refreshed. There is no transaction table tracking changes.

Here is a screenshot of how I currently have things laid out:

The Date_Dimension table is related to Initiatives on DateKey->Start Date Actual. Initiatives is related to Projects by the InitiativeID. Projects is linked to Tickets by the ProjectID. Resources is linked to Tickets by the ResourceID.

Any ideas on how could I properly break these out into a traditional dimension/fact format where I can report on the status of items at each layer?

Thank you!

[ImkeF]

One way would be to consider every table that you have so far as dimension table and additionally create fact tables from each dim-table that has start- and end dates. You can split out "Goal" dates and "Actual" dates into 2 different fact tables to make easy comarisons. With regards to the Fact-tables make sure to have just one date column that has one row for each day within the relevant periods. You connect that column to your DimCalendar.

Or create just 2 Fact tables (Goal and Actual) with a column that distinguishes between the source of the values (Initiatives, Projects..) This depends a bit on what you actually want to display/measure at the end.

Of course, then delete all relationships you've already made between your existing tables and replace them by relationships to the key attributes of your fact tables.

Edit: Good point @edhans  to consolidate the hierarchical tables into one. I'd still go for separating fact and dim here.

Question is if the date-fields of the Initiatives and Projects are redundant (in a way that they are alway the Min/Max of the tickets or are not related and requires seperate connections)

[edhans]

Agreed @ImkeF - I'd really have to see the data and start playing with how the reports would ultimately look. As always, this should probably have some considerable time in Power Query setting the model up correctly with half a dozen or more intermediate queries to properly shape it.

[ImkeF]

Agree @edhans 

[edhans]

In Power Query, I would start with the Initiatives table and join the Projects table to it, then the Tickets table to it. This is similar to sales data. You could have customer number, invoice number, and item number, but you would't keep that in 3 separate tables, you'd have one big sales table where the customer number and invoice number got duplicated as you increased your granularity.

Your resource table is a DIM table much like an item table would be in a sales to item relationship.

So you would have 3 tables:

1. Date (Dimension)
2. Resources (Dimension)
3. Tasks (Fact) - not sure what you would call this. Your org probably has some name that references all 3 at once.

And that is a perfect star schema.

Does that help? If not, what breaks down to your way of thinking as to how it would need to work?

EDIT: One other thing. You indicated each of the three tables has dates. You can relate all of those dates to the Date table, but only one is active. The others are inactive. You activate them on a per-measure basis with USERELATIONSHIP(). See below pseudo-formula.

Ticket Hours =
CALCULATE(
     SUM(Tasks[TicketHours]),
     USERELATIONSHIP(Tasks[TicketStartDate],Date[Date])
)