Help with Fabric certification question

[amaaiia]

A colleague took the Fabric Certification exam and came up with a question that he found confusing to understand. I put it to you:

You have a CSV with the following data:
- SalesTransactionID
- SalesDate
- CustomerCode
- CustomerName
- CustomerAddress
- ProductCode
- ProductName
- Quantity
- UnitPrice

A star schema must be implemented with this data on a Warehouse. The dimension tables will be slow variation tables (SCD) type 2.
You need to design the tables that will be used for sales transaction analysis and load the source data.
What type of destination table should you specify for the CustomerName, CustomerCode and SaleDate fields?

- CustomerCode: Dimension/Fact/Factless fact/Junk dimension
- CustomerName: Dimension/Fact/Factless fact/Junk dimension
- SaleDate: Dimension/Fact/Factless fact/Junk dimension

I'd implement 3 tables:

Customers (dimension table SCD 2):

- CustomerCode

- CustomerName

- CustomerAddress

- StartDate

- EndDate

- RecordID

Products (dimension table SCD2):

- ProductCode

- ProductName

- UnitPrice

- StartDate

- EndDate

- RecordID

Sales (fact table):

- SalesTransactionID

- SalesDate

- CustomerCode

- ProductCode

- Quantity

I don't know if this is the best approach. Anyway, I don't event know which are the correct answers for the question itself.

Any ideas?

[v-tsaipranay · Accepted solution]

Hi @amaaiia,

Thank you for reaching out to the Microsoft fabric community forum regarding your implementation of a star schema for sales transaction analysis. 

Based on the data provided in your CSV, here’s an overview of how we can categorize each of the fields:

• CustomerCode uniquely identifies each customer and links related data in your star schema. Store this in your Customers dimension table.
• The CustomerName field provides descriptive information about customers. Although it does not uniquely identify them like the CustomerCode, it offers valuable context. This field will also be included in your Customers dimension table.
• The date of each sales transaction is vital for time-based analysis, allowing you to track sales trends over various time periods. In a well-structured data warehouse, it's common to create a dedicated date dimension; however, it can also be part of the fact table for some basic analyses.

Your approach to implementing the schema seems correct, and the answers for the question align with best practices for star schema design.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[v-tsaipranay]

Hi @amaaiia ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay]

Hello @amaaiia ,

I wanted to follow up on our previous suggestions regarding Fabric certification question. We would love to hear back from you to ensure we can assist you further.

If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.