How populate a table fact

SSAS and SSRS
1

Hello all,

I have 3 dimensions and would like to know how to load the Fact table.

Thank you.........

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)

)
go

create table Dim_Data
(
sk_data integer identity(1,1),
Date datetime,
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
)
go

INSERT INTO Fato_Sales
(
sk_Fato_Sales,
sk_Customer,
sk_Product,
sk_data,
Total_Sale,
Quantity
)
SELECT
sk_Customer,
sk_Product,
sk_data,
Total_Sale,
Quantity
)
------ My problem is here, What am I doing wrong?

FROM Dim_Customer c
JOIN Dim_Product p
ON c.sk_Customer = p.sk_Product
JOIN Dim_Data d
ON c.sk_Customer = d.sk_data

I know it's wrong, but it would be something like that.

_Keny

2

Syntax would fail at the last parenthesis (just after Quantity, before FROM)

3

I know.

the only thing I want is a script with the step by step to load fact table.

4

you cant, there is no column that establishes a relationship between customer and product.
you are doing all kinds of crazy joins here on unrelated columns

ON c.sk_Customer = p.sk_Product
JOIN Dim_Data d
ON c.sk_Customer = d.sk_data
5

ok...And how do I do it then?

6

you first need to tell us what is the relationship between customer and products table.
imagine the following scenario:

There are 4 people in your family lets call them Dimcustomers, mom, dad, you, your sister. You are married and your sister is also married. That makes total 6 people, 6 customer. now you all live in your own apartment, lets call these DimApartments.

We will go with not normalized data for the sake of explaining.
Sample DDL DML

DimCustomers(sk_CustomerId int, FirstName nvarchar(30), LastName nvarchar(30))
insert into DimCustomers
Select 'Dad', 'Dad' union
Select 'Mom', 'Love You' union
Select 'Keny', 'NeedsHomework' union
Select 'Sister', 'Sis' 



DimApartments(sk_apartmentId int, ApartmentNumber varchar(10))
insert into DimApartments
select 'A1' union
select 'A2' union
select 'A3'

So question for you. From this data, which is like the data you have given us, how do we know where your family lives?

7

I understand now yosiasz,

I'm going to put my full case study here, see if you can help me load the [FactProductSales] Fact table now.

-- Fact table

CREATE TABLE [dbo].[FactProductSales]
(
[TransactionId] [bigint] IDENTITY(1,1) NOT NULL, -- Surrogate Key
[SalesDateKey] [int] NULL, -- Pk [DimDate]
[StoreID] [int] NOT NULL, -- Pk [DimStores]
[CustomerID] [int] NOT NULL,-- Pk [DimCustomer]
[ProductID] [int] NOT NULL, -- Pk [DimProduct]
[SalesPersonID] [int] NOT NULL,-- Pk [DimSalesPerson]
[Quantity] [float] NULL,
[SalesTotalCost] [money] NULL,
[ProductActualCost] [money] NULL
) ON [PRIMARY]

====================================================================================

Go

CREATE TABLE [dbo].[DimCustomer]
(
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerAltID] varchar NOT NULL,
[CustomerName] varchar NULL,
[Gender] varchar NULL,
CONSTRAINT [pk_DimCustomer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)
) ON [PRIMARY]

=====================================================================================

go

CREATE TABLE [dbo].[DimDate]
(
[DateKey] [int] NOT NULL,
[Date] [datetime] NULL,
[FullDate] char NULL,
[DayOfMonth] varchar NULL,
[DayName] varchar NULL,
[DayOfWeek] char NULL,
[DayOfYear] varchar NULL,
[Month] varchar NULL,
[MonthName] varchar NULL,
[Year] char NULL,
[IsWeekday] [bit] NULL,
CONSTRAINT [pk_DateKey] PRIMARY KEY CLUSTERED
(
[DateKey] ASC
)
) ON [PRIMARY]

===================================================================================

Go

CREATE TABLE [dbo].[DimProduct]
(
[ProductKey] [int] IDENTITY(1,1) NOT NULL,
[ProductAltKey] varchar NOT NULL,
[ProductName] varchar NULL,
[ProductActualCost] [money] NULL,
[ProductSalesCost] [money] NULL,
CONSTRAINT [pk_DimProduct] PRIMARY KEY CLUSTERED
(
[ProductKey] ASC
)
) ON [PRIMARY]

go

===========================================================================

CREATE TABLE [dbo].[DimSalesPerson]
(
[SalesPersonID] [int] IDENTITY(1,1) NOT NULL,
[SalesPersonAltID] varchar NOT NULL,
[SalesPersonName] varchar NULL,
[StoreID] [int] NULL,
[City] varchar NULL,
[State] varchar NULL,
[Country] varchar NULL,
CONSTRAINT [pk_DimSalesPerson] PRIMARY KEY CLUSTERED
(
[SalesPersonID] ASC
)
) ON [PRIMARY]

go

======================================================================================

CREATE TABLE [dbo].[DimStores]
(
[StoreID] [int] IDENTITY(1,1) NOT NULL,
[StoreAltID] varchar NOT NULL,
[StoreName] varchar NULL,
[StoreLocation] varchar NULL,
[City] varchar NULL,
[State] varchar NULL,
[Country] varchar NULL,
CONSTRAINT [pk_DimStores] PRIMARY KEY CLUSTERED
(
[StoreID] ASC
)
) ON [PRIMARY]

=====================================================================================

_Keny

8

I don't think you understand yet young Jedi :slight_smile: I will help once you connect the dot yourself. in these tables you will see nicely laid out column names. Connect those in your select statement StoreID connects to DimStore etc

9

I really did not understand.

What exactly did you mean by "connect the dot yourself"?

My example is not clear?

10

look at the tables and the columns. see which column from one table matches same column from another table and figure out how they are related.

11

Hi @keny,
Dimension tables are similar to lookup tables. To load a fact table you query the source joined to dimensions to get the foreign keys and measures to insert.

12

Hello @jotorre_riversidedpss.org,

Did you mean it like that?

SELECT
[TransactionId]
,[SalesDateKey]
,fp.[StoreID]
,fp.[CustomerID]
,[ProductID]
,fp.[SalesPersonID]
,[Quantity]
,([SalesTotalCost])
,pt.[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]

The SELECT did not return any records, what am I doing wrong?

I appreciate your help.
_Keny

13

Is your data source [Sales_DW].[dbo].[FactProductSales]?

14

Yes, this is my data source.

15

We can't see your data and without that we can only guess as to why you get no rows returned by your query.

16

Can be
Would you have your own DW example with a fact table and a dimension?
That includes a time dimension too.

17

what do you get when you do this

select count(1) FROM [Sales_DW].[dbo].[FactProductSales]fp

select count(1)  [dbo].[DimDate] dt

select count(1)  from [dbo].[DimCustomer] cu

select count(1)  from [dbo].[DimProduct] pt

select count(1)  from [dbo].[DimSalesPerson] sp

select count(1)  from from [dbo].[DimStores] st

Please post back the count of rows in these tables

18

This...

select count(1) FROM [Sales_DW].[dbo].[FactProductSales]fp

0 row

select count(1) [dbo].[DimDate] dt

1826 rows

select count(1) from [dbo].[DimCustomer] cu

5 rows

select count(1) from [dbo].[DimProduct] pt

5 rows

select count(1) from [dbo].[DimSalesPerson] sp

6 rows

select count(1) from from [dbo].[DimStores] st

3 rows

19

so looking at that why do you think you are not getting any rows?

20

I do not get any row when I do SELECT and JOIN to load FACT because it is wrong.