Implementing Star Schemas
1. Implementing Star Schemas
In this video, we’ll talk about key aspects to consider when implementing the star schema in Microsoft Fabric.2. Medallion Architecture
Before drilling into star schemas, let's first start by understanding a key architectural concept. The medallion architecture is a design pattern that organizes data in a series of layers that indicate progressive refinement of the data. The layers are called bronze, silver and gold.3. Medallion Architecture
The Bronze Layer stores source data in its original raw format. The purpose of this layer is to quickly ingest data from sources.4. Medallion Architecture
The Silver Layer stores data from the bronze layer that has been cleansed and standardized.5. Medallion Architecture
The Gold Layer stores data from the silver layer that has been refined for analytics. Tables typically conform to star schema design to make it easier for end users.6. Medallion Architecture in Fabric
The medallion architecture can be implemented on Fabric by using Lakehouses for the bronze and silver layers. The gold layer can be implemented using either a lakehouse or a warehouse. A warehouse is a great choice because it supports T-SQL read and write operations, making it easy to implement and manipulate the star schema tables.7. Implementing a star schema
Let's now drill down into the implementation details of a star schema. A star schema is composed of a fact table and several related dimension tables. We can implement these tables in a warehouse, by using the T-SQL CREATE TABLE statement to define the tables.8. Dimension table structure
Dimension tables describe business entities, for example, products. Dimension tables usually have the following columns:9. Dimension table structure
A surrogate key is a single-column unique identifier for dimension items. Surrogate keys are essential to define relationships between the dimension and the fact tables.10. Dimension table structure
A natural key is a single-column unique identifier that comes from source systems. It's also called the business key, because that is the key that business users are likely to recognize from using their systems.11. Dimension table structure
Finally, the dimension attributes are one or more columns that describe the business entity. Attributes are used to filter and group data.12. Building dimension tables
This sample dimension table can be implemented with the T-SQL statement CREATE TABLE, as shown here.13. Fact table structure
Fact tables describe business events, for example the sale of a product. Fact tables usually have the following columns:14. Fact table structure
The dimension keys are references to the surrogate keys of the dimension tables. For example, if the fact table has sales by product, one of the dimension keys would be the product ID. The combination of dimension keys determine the granularity of the data. For example, the granularity of this table is by date, store, product, customer and payment type.15. Fact table structure
The fact table can have one or more columns that provide additional information about the fact but are neither a dimension or a numerical measure. For example, the packaging notes related to a particular sale transaction record.16. Fact table structure
Finally, the measures quantify something about the fact; they are numeric and commonly additive. For example, the sale units.17. Building fact tables
This sample fact table can be implemented with the T-SQL statement CREATE TABLE, as shown here.18. Let's practice!
Now, let's do a couple of exercises to practice implementing a star schema.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.