The first thing is that you need to ensure that you have your column created in your Fact Table.Ī. Updating our Fact Table with our Distinct Customer calculationġ. With our example explained above if we had to look at our calculation for our Distinct Customers on the 03 March 2008 it would be the following: As you can see above we are also grouping the calculation by OrderDateKey so that we can use this to insert the calculation for multiple days.Ĥ. Staging_tb_FactInterNetSales_DistinctCustomerKeyĬonvert ( float, Count ( Distinct ( CustomerKey ))) / Count ( 1 ) as DistinctCustomerKeyĪ. Staging_tb_FactInterNetSales_DistinctCustomerKey For the simplicity to understand below is the complete TSQL Syntax where we will be truncating and inserting our data into our Staging table in our AdventureWorksDW2012 database NOTE : The reason that we are converting this to a float is so that later when we sum the row details it will sum back up to the correct number.ģ. We also have the OrderDateKey, because this is our lowest level of granularity, we need to group by this so that the number of rows is grouped per day to work out the calculation correctly. This is so that we can then get the correct calculation. The final part of the calculation is highlighted in BLUE, where we are dividing our distinct count by the number of rows.Ģ. convert(float, Count( Distinct(CustomerKey) ) ) The next section highlighted in GREEN is where we are converting our values to a Floatġ. The next section highlighted in PURPLE is where we are doing a count of the Distinct CustomerKey The part highlighted in RED is where we first are selecting the Distinct CustomerKey We will start the explanation from the inside out, due to this being the logical way that I built this up. The only part that we are going to concentrate on is the actual calculation.ī. Next below is the TSQL syntax that we used to create our distinct number of Customers Calculation, with an explanation afterwardsĬonvert(float, Count( Distinct(CustomerKey) ) ) / Count(1) as DistinctCustomerKeyįROM. We also insert the data into a Staging table so that we can use it as part of our Update Statement.Ģ. But with our example we are going to update our Fact Table with our calculations. NOTE : What you could do is to use your staging table later as part of the loading into your Fact Table. The first thing is we will be creating our distinct calculation and then insert this into a staging table.Ī. O The distinct number of Customers on 03 March 2008 is 51 Customers.Ĭreating the Distinct Number of Customers Calculationġ.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |