asked
How populate a table fact
Hello all, I'm new to B.I and trying to create and populate three dimensions and a fact table with data. The dimensions work just fine but i'm unable to get any data into my fact table. If anyone will be so kind to point out what i'm doing wrong i will be grateful. This is my code create table Dim_Customer ( sk_Customer integer identity(1,1), Name varchar (25), Country varchar(100), Telephone varchar(20) ) go create table Dim_Product ( sk_Product identity(1,1), ProductName varchar(50), CategoryName varchar(50), Price money ) go create table Dim_Data ( sk_data integer identity(1,1), day int, Week varchar (25), Month varchar (25), Year int ) go Create Table Fato_Sales ( sk_Fato_Sales integer identity(1,1), sk_Customer integer, sk_Product integer, sk_data integer, Total_Sale money, Quantity int ) insert into Fato_Sales ( sk_Fato_Sales, sk_Customer, sk_Product, sk_data, Total_Sale, Quantity ) ------ My problem is here, I do not know how to do the joins to insert in the Fact Table! SELECT sk_Customer, sk_Product, sk_data, Total_Sale, Quantity FROM ?? join ?? Dim_Customer, Dim_Product, Dim_Data Thank You...... _KenyM
@kenymaciel Sure, I will be happy to help, but please provide the table/column names of the (staging) table (or some other source) where the sales data is stored at this time. The dimension tables are not going to help you to make up the sales data out of thin air, right? So, there has to be a table (or some other source) where the sales data is stored at this time. Please clarify. As far the calendar table is concerned, please bear in mind that it is the single, most important table in the data warehouse/mart, the cube without properly designed calendar dimension is nothing more than a useless waste, consisting of the rows of data which is impossible to properly analyze. So, this table has to be designed with utmost care and consideration. Unlike the other dimensions' attributes, every attribute (based on the table column) of this dimension will have a specific purpose, so you can wheel out all calculation which (trust me) will be required, such as year-to-date, year-to-go, year-over-year, etc, etc.
@kenymaciel You have to have some sales history data already stored somewhere (database table, Excel, CSV, etc.). Joining to the 3 dimension tables will help you insert records into the fact table with correct references, that is all. These tables cannot possibly provide you info about, say, quantity. Also, it looks like you are planning to use Dim\_Data table as the date dimension. If this is the case then the design of this table is incorrect as it does not have the column of date (or datetime) data type without which you will never be able to use any of the time intelligence capabilities of the OLAP system. Worse still, all your dimension tables are heaps, you should change their design to include clustered index for each table. It looks like you might benefit from studying the [Multidimensional Modeling Tutorial][1] if you are planning to implement the multidimensional OLAP or the [Tabular Modeling Tutorial][2]. [1]:
https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-modeling-adventure-works-tutorial [2]:
https://docs.microsoft.com/en-us/sql/analysis-services/tabular-modeling-adventure-works-tutorial
Oleg, Thanks for the feedback, This model is just to show what I want, I know there are many things wrong. What I really need to know is how to load the fact table from dimensions. I need some examples so that I can understand.
Oleg, I have attached the file of creation of dimensions and fact. thanks, _Keny
Oleg, I have attached the file of creation of dimensions and fact. thanks, _Keny
Hello Oleg, I'm planning to get the [SalesTotalCost] and [ProductSalesCost] from DimProduct table, the Quantity values I do not know how to get. See my SELECT, what am I doing wrong and what is missing? SELECT dt.[DateKey] ,st.[StoreID] ,cu.[CustomerID] ,pt.[ProductKey] ,sp.[SalesPersonID] ,[Quantity] ----------------------------------------------i dont now how to load ,SUM(pt.ProductSalesCost) as SalesTotalCost ,pt.ProductActualCost as ProductActualCost FROM [Sales_DW].[dbo].[FactProductSales]fp LEFT JOIN [dbo].[DimDate] dt ON fp.[SalesDateKey] = dt.[DateKey] LEFT JOIN [dbo].[DimCustomer] cu ON fp.[CustomerID] = cu.[CustomerID] LEFT JOIN [dbo].[DimProduct] pt ON fp.[ProductID] = pt.ProductKey LEFT JOIN [dbo].[DimSalesPerson] sp ON fp.[SalesPersonID] = sp.[SalesPersonID] LEFT JOIN [dbo].[DimStores] st ON fp.[StoreID] = st.[StoreID] GROUP BY dt.[DateKey] ,st.[StoreID] ,cu.[CustomerID] ,pt.[ProductKey] ,sp.[SalesPersonID] ,[Quantity] ,pt.ProductSalesCost ,pt.ProductActualCost Thanks, _Keny
@kenymaciel There are few things which are wrong: - What causes you to use left joins? There should be no orphan data in the properly designed and populated dimension tables, so it is impossible to understand the need for left joins. - Why do you need to group the data? - Say, you do, but then why do you include the ProductSalesCost in the group by clause and also aggregate it in the select list? - Most importantly, where does the Quantity come from? At this point, I am not actually sure I understand what you are trying to accomplish. It looks like you are working on building the data mart / warehouse, but where does your sales data come from? This is the main question. The dimension tables are not going to make it up. Please let me know. Thank you.
For simplicity I got this model on the internet. The load in the FACT table is performed without consulting the dimensions, is it wrong? take a look. --
www.codeproject.com/Articles/652108/Create-First-Data-WareHouse
18 People are following this question.
