Database Architecture Question - Dimension Creation for Non-Atomic Data

Question

I am looking at an Excel file that will be imported into Power BI. I am not allowed to have access to the database itself due to employment reasons, so they gave me an Excel file to work with that I will then upload into Power BI.

On one of the fact "tables", they have data that looks like this

s-ID     success%     late%     on-time%     schedule
1        10%          2%        5%           calculus-1;algebra-2
1        5%           10%       27%          Calculus-1
1        5%           3%        80%          algebra-2
2        33%          50%       3%           null
5        5%           34%       8%           English-1;English-10;theatre;art

I realize the numbers do not make any sense, but that's basically how the data is structure wise. There are also roughly 100,000 records in this fact "table".

I have a dimension for courses, but I'm not sure how to handle this schedule column. If I split the column vertically, the measure columns will be double counted.

How can I model this and put the schedule into a dimension intelligently in Power-BI?

My goal is as model the data as follows:

• Be able to split the schedule into separate rows, but simultaneously not double count all of the values.

• I also want to show that the s-ID records have the student taking a class that has both the calculus-1 and algebra together.

Sometimes the professors schedule 2 classes together into 1 class whenever they are talking about topics that apply to both. There could be 2 classes together, there could be as many as 8 classes together or anything in between.

Is this a scenario where a bridge table would be appropriate?

Answer 1

You can use a bridge table. In a classic dimensional schema, each dimension attached to a fact table has a single value consistent with the fact table’s grain. But there are a number of situations in which a dimension is legitimately multivalued. Like in your example, a student can enroll many courses :