![]() ![]() Each specific stage can be assigned a foreign date key to help. i.e recording the process of signing on a client. This type of fact table is often used to summarize events over a period of time, in well defined time intervals.i.e if we were to record sales person monthly revenue by month instead of individual sales orders. The periodic table offers a “periodic” view of a specific measure. i.e each row represents a moment in time such as a sale or purchase A transaction table records a measurable event at the row level. What are the measures that we want to record?.When designing a fact table, we must take into consideration a few things So designing the fact table which will hold our sales data is crucial to determining how successful our B.I project will be. In a Star Schema, each star is meant to represent a process in the business. I will discuss how I created this model in the section below. The center of the model is where sales are recorded which tells us how much was sold and the branches tell us particular information about the sales themselves. We are modeling the sales process of the Adventure Works data. Branching outwards are the dimension tables that provide descriptive data (i.e Customer Name, Product Model, Address Type, essentially anything that is not a quantifiable measure). In this case, that is the SalesOrderDetails portion of the data which contains quantitative measures (facts) such as Quantity, Price, Amount. The center of the star is the process in which we want to model (i.e the Fact Table). Taking the Adventure Works data, I created a star schema consisting of fact and dimension tables that relate to sales. I will address the peculiarities of each schema further in another post, but for this series we will be using a Star Schema. There are two main types of Dimensional Models that are often used today: You will know that we want to create a dimensional model that is purposely de-normalized for reporting purposes. If you remember from my introduction post The first issues we must address is how we design the model for our data warehouse. Our goal will be to build out a data warehouse with sales data that will feed Power B.I Dashboards to gain further insight on the nuances of sales within the fictional company. The database has 5 distinct schemas that are explained hereįor the purposes of this series, we will focus mainly on the sales portion of the database. That is, we will build our data warehouse based on the data supplied in the sample database. This will be our Primary Data Source for our B.I Project. Once it is loaded, you should be able to see the AdventureWorks2017 database in your Object Explorer in SSMS See here for a variety of methods to do so Once you have that up and running, you will need to load a copy of the Adventure Works Database into your newly created SQL Server Instance. Before we begin, we will need a local instance of MS SQL Server 2017 running, along with SQL Server Management Studio. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |