Chance Insight – Business Intelligence Blog

Category: SQL

  • An Example of Creating a Sales Report with SQL

    We can create a more interesting sales report using Window functions and additional calculations in the query, including the gross margin %, total profit, and ranks for product price, sales quantity, and sales rank. The query uses a Common Table Expression (CTE) function to group the data first (as we can’t group using Windows functions).

    This is the kind of report decision-makers will be interested in and as more questions come up, additional fields can be added.

    USE [ContosoRetailDW];
-- Note: Qty * UnitPrice - Discount = TotalSalesAmount (small descrepency)
-- CTE Function

WITH sub1 AS
(
SELECT p.ProductName, s.UnitCost, s.UnitPrice, SUM(SalesQuantity) as TotalSalesQuantity, 
(s.UnitCost * SUM(SalesQuantity)) as TotalCost, SUM(DiscountAmount) as TotalDiscount,
SUM(SalesAmount) as TotalSalesAmount
FROM [dbo].[FactOnlineSales] as s
LEFT OUTER JOIN [dbo].[DimProduct] as p ON s.[ProductKey] = p.[ProductKey]
LEFT OUTER JOIN [dbo].[DimDate] d on s.DateKey = d.DateKey
LEFT OUTER JOIN [dbo].[DimStore] st ON s.StoreKey = st.StoreKey
WHERE 
d.CalendarYear = 2007 AND d.CalendarMonthLabel = 'January'
AND
st.StoreName = 'Contoso Europe Online Store'
GROUP BY  p.ProductName, s.UnitCost, s.UnitPrice
)

--Main Query referencing the CTE Function

SELECT ProductName, UnitCost, UnitPrice, (UnitPrice-UnitCost)/UnitCost as Margin, 
TotalSalesQuantity, format(TotalCost,'$0,,.0M') as TotalCost, format(TotalSalesAmount, '$0,,.0M') as TotalSales, 
format(TotalSalesAmount-TotalCost, '$0,,.0M')  as TotalProfit,
TotalDiscount,
RANK() OVER(ORDER BY UnitPrice DESC) as PriceRank,
RANK() OVER(ORDER BY TotalSalesQuantity DESC) as QtyRank,
RANK() OVER(ORDER BY TotalSalesAmount DESC) as SalesRank
FROM sub1
ORDER BY SalesRank
ORDER BY SalesRank

  • Examples of working with Dates in Google BigQuery

    References:

    Date Functions
    https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions
    Format Elements
    https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_time

    Current Date, Last_Date, Construction Add and Subtract

    CURRENT_DATE() as Current_Date, -- returns todays date

DATE(2024,01,24) as Constructed_Date, -- constructs the date from Year, Month, Day

LAST_DAY('2008-12-25') AS Last_Date, -- returns 2008-12-25 the last date

DATE_ADD(DATE "2024-01-24", INTERVAL 1 DAY), -- 1 day later

DATE_SUB(DATE "2024-01-24", INTERVAL 1 DAY), -- 1 day before


    FORMAT_DATE and PARSE_DATE

    SELECT

CURRENT_DATE() as today, -- returns today

FORMAT_DATE('%d/%m/%Y', CURRENT_DATE()) AS formatteday, -- UK format

PARSE_DATE('%d-%m-%Y', '01-05-2024') as passstringdate, -- convert string to date object

FORMAT_DATE('%d/%m/%Y', PARSE_DATE('%d-%m-%Y', '01-05-2024')) AS formatedpassstring -- convert string to date and then format



    EXTRACT

    --CURRENT_DATE() is 15/05/2024

SELECT

EXTRACT(DAY from CURRENT_DATE()) as day, -- 15/05/2024

EXTRACT(DAYOFWEEK from CURRENT_DATE()) as DayOfWeek, -- 4

EXTRACT(WEEK from CURRENT_DATE()) as Week, --19

EXTRACT(WEEK(MONDAY) from CURRENT_DATE()) as WeekStartingMonday, -- 20

EXTRACT(ISOWEEK from CURRENT_DATE()) as WeekStartingMonday2, -- 20

EXTRACT(MONTH from CURRENT_DATE()) as  Mth, -- 5

EXTRACT(QUARTER from CURRENT_DATE()) as Qtr, -- 2

EXTRACT(YEAR from CURRENT_DATE()) as Yr, -- 2024

EXTRACT(ISOYEAR from CURRENT_DATE()) as YrWkStartMon -- 2024


    DATE_DIFF

    SELECT DATE_DIFF(DATE '2024-01-25', DATE '2024-01-02', DAY) AS days_diff -- returns 23 (note format end_date, start_date)

    DATE() Constructor

    SELECT *
FROM `bigquery-public-data.google_trends.top_terms`
WHERE rank = 1
--Date Constructor
AND refresh_date >= DATE(2024, 01, 01)
LIMIT 10


    DATE TRUNC()

    SELECT *

FROM `bigquery-public-data.google_trends.top_terms`

WHERE rank = 1
-- DATE_SUB subtracts from given date

AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK)

-- Limit result to 10 (works like TOP)

LIMIT 10






  • SQL Code for Lifetime Value Model

    The first part of building the code to pull the data for the lifetime value model is to create an SQL script that will get the historical data we need to train our model on.

    The SQL Code select 6 fields from the Contoso Retail Datawarehouse:
    The full SQL code is at the bottom of the page

    First Section of Code (WITH)

    USE ContosoRetailDW;

WITH FirstTransactionDates AS (
    SELECT customerkey,MIN([DateKey]) AS first_transaction_date
    FROM [dbo].[FactOnlineSales]
    GROUP BY customerkey)
	,
	Orders AS (SELECT 
		  [CustomerKey] as CustomerKey, 
		  [SalesOrderNumber] as SalesOrderNumber,
		  ROW_NUMBER() OVER (PARTITION BY [CustomerKey] ORDER BY [SalesOrderNumber] ASC) AS OrderNumber,
		  SUM([SalesAmount]) as SalesAmount
		  FROM
		  [dbo].[FactOnlineSales]
		  GROUP BY [CustomerKey], [SalesOrderNumber] 
)


    The code starts off with a subquery after the database is selected in the USE statement.

    2 Sub-queries are then created in the WITH clause. This makes the code easier to manage rather than using sub-queries on sub-queries in the main code body.

    The first sub-query: FirstTransactionDates creates a temporary table of customers and their first transaction date from the FactOnlineSales table

    The second-subquery: Orders creates a temporary table of customers and their orders with the sales ordernumber field ranking the orders from 1 upwards with 1 being the first order. I will create a separate post explaining the line:
    ROW_NUMBER() OVER (PARTITION BY [CustomerKey] ORDER BY [SalesOrderNumber] ASC) AS OrderNumber,


    Second Section of Code

    SELECT 
--selection of CustomerKey from DimCustomer
c.CustomerKey as CustomerKey, 

-- Calculate Age as difference between [BirthDate] selected from DimCustomer and today (using the GETDATE() function), then divide by 365
DATEDIFF(DAY, [BirthDate], GETDATE())/365 as Age, 

--Gender selected as the [gender] field from DimCustomer and converted to 0 and 1 for m and f respectively
CASE 
WHEN [gender] = 'm' THEN 0
WHEN [gender] = 'f' THEN 1
END as Gender,

--YearlyIncome selected from DimCustomer table
c.YearlyIncome as YearlyIncome, 

--FirstPurchaseAmount from sub-query a
a.FirstPurchaseAmount as FirstPurchaseAmount,

LifetimeSales as sub-query b
b.first12months_salesamount as LifetimeSales

-- select from DimCustomer with left join on.... tables to follow...
FROM dbo.DimCustomer c LEFT OUTER JOIN


    As you can see from the above, there are 6 field selections:
    1. The CustomerKey from the DimCustomer table (alias c)
    2. Age calculated as the difference in days between the customers birthdate ([BirthDate] and todays date (GETDATE()). This is then divided by 365 to get the age in years.
    3. The Gender which uses the [gender] filed from the DimCustomer table and swaps ‘m’ for 0 and ‘f’ for 1, so we have numeric data for the model.
    4. The YearlyIncome field from the DimCustomer table.

    Then the last 2 fields FirstPurchaseAmount and first12months_salesamount which are calculated using sub-queries.

    Third Section of Code (Sub-query a)

    (
SELECT CustomerKey, SalesAmount as FirstPurchaseAmount
FROM ORDERS
WHERE OrderNumber = 1
) a
ON c.CustomerKey = a.CustomerKey



    First Sub-query a, which is the DimCustomer table joins to on ON c.CustomerKey = a.CustomerKey
    The SELECT query select, CustomerKey and SalesAmount from the ORDERS table.
    The ORDERS table is a temporary table created with a sub-query in the WITH clause at the start of the code. The ORDERS table is filtered by OrderNumber = 1 to return the sum of sales for each customer on their first order. This gives us the FirstPurchaseAmount field.

    The Fourth Section of code is sub-query B

    LEFT OUTER JOIN

-- Build First 12 Month Sales Table b
(
SELECT 
    f.customerkey,
    SUM(CASE WHEN DATEDIFF(month, ft.first_transaction_date, f.DateKey) < 12 THEN f.salesamount 
	ELSE 0 
	END) AS first12months_salesamount

FROM [dbo].[FactOnlineSales] f
LEFT OUTER JOIN FirstTransactionDates ft ON 
f.customerkey = ft.customerkey
GROUP BY f.CustomerKey
) as b
ON c.CustomerKey = b.CustomerKey

WHERE c.CustomerType = 'Person'


    The fourth section of code shows the LEFT OUTER JOIN of the DimCustomer table to sub-query b.
    Sub-query b gets us the first 12 month sales amount of customers, which will be our dependent variable in our final Model.

    The SELECT code starts by selecting the CustomerKey from the FactOnlineSales table.

    The second line selects the first_transaction_date from the FirstTransactionDates sub-query created in the WITH clause and the f.DateKey from the FactOnlineSales table and looks at the difference in months between the 2. If the DateKey (transaction date is less than 12 months), then the sales amount is summed as first12months_salesamount.
    b.first12months_salesamount as LifetimeSales

    SUM(CASE WHEN DATEDIFF(month, ft.first_transaction_date, f.DateKey) < 12 THEN f.salesamount
    ELSE 0
    END) AS first12months_salesamount

    This is the field used in the main SELECT query in code section 2.
    b.first12months_salesamount as LifetimeSales

    Full SQL Code

    USE ContosoRetailDW;

WITH FirstTransactionDates AS (
    SELECT customerkey,MIN([DateKey]) AS first_transaction_date
    FROM [dbo].[FactOnlineSales]
    GROUP BY customerkey)
	,
	Orders AS (SELECT 
		  [CustomerKey] as CustomerKey, 
		  [SalesOrderNumber] as SalesOrderNumber,
		  ROW_NUMBER() OVER (PARTITION BY [CustomerKey] ORDER BY [SalesOrderNumber] ASC) AS OrderNumber,
		  SUM([SalesAmount]) as SalesAmount
		  FROM
		  [dbo].[FactOnlineSales]
		  GROUP BY [CustomerKey], [SalesOrderNumber] 
)

SELECT 
c.CustomerKey as CustomerKey, 
DATEDIFF(DAY, [BirthDate], GETDATE())/365 as Age, 
CASE 
WHEN [gender] = 'm' THEN 0
WHEN [gender] = 'f' THEN 1
END as Gender,
c.YearlyIncome as YearlyIncome, 
a.FirstPurchaseAmount as FirstPurchaseAmount,
b.first12months_salesamount as LifetimeSales

FROM 
dbo.DimCustomer c

--Build FirstPurchaseAmount table a
LEFT OUTER JOIN
(
SELECT CustomerKey, SalesAmount as FirstPurchaseAmount
FROM ORDERS
WHERE OrderNumber = 1
) a
ON c.CustomerKey = a.CustomerKey

LEFT OUTER JOIN

-- Build First 12 Month Sales Table b
(
SELECT 
    f.customerkey,
    SUM(CASE WHEN DATEDIFF(month, ft.first_transaction_date, f.DateKey) < 12 THEN f.salesamount 
	ELSE 0 
	END) AS first12months_salesamount

FROM [dbo].[FactOnlineSales] f
LEFT OUTER JOIN FirstTransactionDates ft ON 
f.customerkey = ft.customerkey
GROUP BY f.CustomerKey
) as b
ON c.CustomerKey = b.CustomerKey

WHERE c.CustomerType = 'Person'

  • How to install the Contoso Retail Database on SQL Server

    There are three steps to get up and running with a Contoso Retail Datawarehouse that you can use for experimenting with Power BI.

    Step 1. If you don’t already have access to an installation of Microsoft SQL server you can install one on your local machine.

    There are 2 free non-production versions available:

    Microsoft SQL Server Express – This includes just the database engine required to get up and run with the database.

    Microsoft SQL Server Developer – In addition to the database engine, this also includes SQL Server Analysis services, reporting services, integration services, and Machine Learning services. It’s over a GB, so a larger installation than the Express version. Of note, Analysis services is included in the Developer version, so it’s useful to install this version.

    Microsoft SQL Server Express and Developer versions are available with instructions here.
    Installation is very straightforward, just follow the instructions.

    Step 2. Install SQL Server Management Studio (SSMS). You will use this tool to connect to the SQL Server installation and run queries on the database.
    You can also download this from Microsoft – here.

    This is the connection information for connecting to the SQL Server developer on my local machine. Note for me if I use localhost\MSSQLSERVER for the server name I get an error. It works on localhost only

    For further troubleshooting, if you open the services app, you can check that services are running and start as required (Run > Services.exe)

    If you connect ok, you should see a screen like this one. You only need to worry about the Databases folder, so don’t worry about all the other stuff for now. Note: This is the Developer version, the Express version, will be slightly different.


    Step 3: The Contoso Retail Datawarehouse database can be downloaded from Microsoft here.
    There are two files: ContosoBIdemoABF.exe and ContosoBIdemoBAK.exe .

    The ContosoRetailDW.bak file is the raw database, which you can get started with and only requires the database engine (which is included in SQL Server Express).

    The ContosoBIdemoABF.exe is the OLAP version of the database, which requires SQL Server Analysis Services. For this, you will need either a full version or the free developer version.

    To install the first file. Connect to the SQL Server Database Engine (Open SSMS and select database engine). Right-click on Databases and select ‘Restore Database’.

    Select Device from the next screen.

    You can then select ‘Add’ to add the file. I placed the ContosoRetailDW.bak file in my C: drive to make it easier to find:

    Then just select the file and click OK, OK, and if all goes well, the database will be restored.


    Now to check it is really there, just return to the main screen, spin open the ContosoRetailDW database, and check the table is there.


    Now you can start running some queries against it and you can connect the Power BI desktop to it.


  • An Example investigation of the Contoso Data Warehouse Tables


    For installing and getting started with the Contoso Retail Database, please see this article.

    A standard starting point for creating customer insight reports is to combine data from sales with your customer table. In the Contoso Retail DW, we have the FactSales and DimCustomer tables that we can use. The DimCustomer Table has a field called Customerkey, but if we look in the main FactSales table we cannot see a Customerkey.

    So what we can do is search all the tables for tables with a field name like ‘CustomerKey’.

    Here is the code: 

    SELECT c.name AS 'FieldName'
,(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE 'CustomerKey%'
ORDER BY FieldName, TableName

    The output is below:


    We can see FactOnlineSales table does have a CustomerKey.

    There is a CustomerType field in the DimCustomer table which segments the data into 2 different types: Person and Company. 

    SELECT customerType
FROM DimCustomer
GROUP BY CustomerType


    Going back to the FactSales table, we can see there is a StoreKey field, which looks useful and there is a table called DimStore.

    A basic join across these tables (see below) returns 306 store names, so we know that we can at least work at the store level. Whether online sales data includes customer-level data is still to be investigated. If so, this will help us structure the reports, so we will potentially have Store vs. Online (with online presumably being a single website for the Contoso company). At this stage, we don’t know yet.

    SELECT st.StoreName, SUM(s.SalesAmount) as SalesAmount
FROM
[ContosoRetailDW].[dbo].[DimStore] st LEFT OUTER JOIN dbo.FactSales s
ON s.[StoreKey] = st.[StoreKey]
GROUP BY st.StoreName



    With this new information, we can import the DimStore Table as we know we are going to use that in the report. In this example, we are importing the table directly into the report, but with the Power BI service, we would want to set up the table imports into the Power BU dataflows, so they become available to all reports.

    On exploration of the DimStore table, find there is a StoreType field that has 4 rows:
    Catalog,
    Online,
    Reseller,
    Store.

    We can now have a look at sales under these new segments, to see how relevant they are:

    We can use the following query:

    SELECT st.StoreType, format(SUM(s.SalesAmount),'$0,,M') as SalesAmount
FROM
[ContosoRetailDW].[dbo].[DimStore] st LEFT OUTER JOIN dbo.FactSales s
ON s.[StoreKey] = st.[StoreKey]
GROUP BY st.StoreType


    This gives us a really good new understanding of how the business is organized.
    We can see that there as well as sales via Catalog, Online, and Reseller, there are also Store sales, which may indicate that the database includes sales by store as opposed to sales by customer.

    Now, let’s go back to that question about individual customer-level data.
    The following query will return sales from the FactOnlineSales table by Customer Type

    Select [CustomerType], format(SUM(o.SalesAmount),'$0,,M') as SalesAmount
from
[ContosoRetailDW].[dbo].[DimCustomer] c
LEFT OUTER JOIN [ContosoRetailDW].[dbo].[FactOnlineSales] o
ON c.CustomerKey =o.CustomerKey
GROUP BY [CustomerType]


    So we can see that sales in the FactOnlineSales table by company are over 3 times that of person.

    Let’s see if the FactSales Table contains data in the FactOnlineSales table
    When we try to join the FactSales table to the FactOnlineSales table, we find there is no relationship between the SalesKey and OnlineSalesKey, which we might expect.

    SELECT
[SalesKey]
FROM
[ContosoRetailDW].[dbo].[FactSales] s
INNER JOIN [dbo].[FactOnlineSales] o
ON s.[SalesKey] = o.[OnlineSalesKey]


    The output is blank so there is no relationship here.


    So, let’s take all the online sales from the FactSales table by Date and see how closely this matches the sales by date of the FactOnlineSales Table:
    We have 2 queries:


    FactSales

    SELECT
[SalesKey]
FROM
[ContosoRetailDW].[dbo].[FactSales] s
INNER JOIN [dbo].[FactOnlineSales] o
ON s.[SalesKey] = o.[OnlineSalesKey]

    FactOnlineSales

    SELECT [DateKey], SUM(SalesAmount) as SalesAmount
FROM
[ContosoRetailDW].[dbo].[FactOnlineSales]
group by [DateKey]


    If we grab the output of these queries and graph them in Excel, we can see the sales are similar, but with differences in the 2 tables.



    So now, we just need to figure out what the differences are. Again, if we could reach out to someone in the company, that would be great, but we can’t so we need to figure it out ourselves.

    The next step is to get a bit more granular, so select a specific date from the 2 tables and see if we can figure out the differences.

    Here is the code. We’re going to see if filtering both tables by the Store name= ‘Contoso Europe Online Store’. This will give us matching sales across the 2 tables.

    SELECT d.StoreName, SUM(SalesAmount) as SalesAmount
FROM
[ContosoRetailDW].[dbo].[FactOnlineSales] s
LEFT OUTER JOIN [ContosoRetailDW].[dbo].[DimCustomer] c
ON s.CustomerKey=c.CustomerKey
LEFT OUTER JOIN [ContosoRetailDW].[dbo].[DimStore] d
ON s.StoreKey = d.StoreKey
WHERE DateKey = '2009-01-01' AND d.StoreName = 'Contoso Europe Online Store'
group by d.StoreName

    SELECT s.DateKey, SUM(s.SalesAmount) as SalesAmount
FROM
[ContosoRetailDW].[dbo].[FactSales] s LEFT OUTER JOIN [ContosoRetailDW].[dbo].[DimStore] st
ON s.StoreKey = st.StoreKey
WHERE StoreType = 'Online' AND s.DateKey = '2009-01-01' AND st.StoreName = 'Contoso Europe Online Store'
GROUP BY s.DateKey


    The first query output is as follows:

    StoreName SalesAmount
    Contoso Europe Online Store 432284.288


    The second query output is:

    DateKey SalesAmount
    2009-01-01 00:00:00.000 521675.102

    So that’s a shame there is still a big difference between the 2 there. The next step seems to be go down to the product level, so we try the following two queries:

    Both tables also include two fields of interest: ReturnAmount and Discount. It would be good to check they are similar across tables. We can run both queries together.

    SELECT SUM(s.SalesAmount) as SalesAmount, SUM(s.[ReturnAmount]) as ReturnAmount, SUM(s.[DiscountAmount])
 as DiscountAmount
 FROM
[ContosoRetailDW].[dbo].[FactSales] s LEFT OUTER JOIN [ContosoRetailDW].[dbo].[DimStore] st
ON s.StoreKey = st.StoreKey
WHERE StoreType = 'Online' AND s.DateKey = '2009-01-01' AND st.StoreName = 'Contoso Europe Online Store'
GROUP BY s.DateKey

SELECT SUM(s.SalesAmount) as SalesAmount, SUM(s.[ReturnAmount]) as ReturnAmount, SUM(s.[DiscountAmount])
 as DiscountAmount
 FROM
[ContosoRetailDW].[dbo].[FactOnlineSales] s LEFT OUTER JOIN [ContosoRetailDW].[dbo].[DimStore] st
ON s.StoreKey = st.StoreKey
WHERE StoreType = 'Online' AND s.DateKey = '2009-01-01' AND st.StoreName = 'Contoso Europe Online Store'
GROUP BY s.DateKey


    What we see here is that the return amounts are similar but the discount amounts on the FactOnlineSales table are over 3x that of the FactSales table.



    The first thing I do is add the ReturnAmount and the DiscountAmount to both queries.
    The discount amount from the first two products ordered by sales amount is much higher in the FactOnlineSales table than in the FactSales table. If I add the discount amount and return amount to the sales amount in each table, we it gets much closer.


    That’s it for now, I’m pretty convinced all the sales in FactOnlineSales are in the FactSales table.
    As the FactOnlineSales key includes a CustomerKey, we can use this table for customer-level analysis, but bearing in mind it is Online sales data only.



  • An Overview of the Contoso Retail Datawarehouse Tables

    The learn power bi we need data to work with. The Contoso Retail DW database from Microsoft is freely available, so we’ll use that one. You could just install a copy to a local desktop version of SQL Express, but instructions on deploying to Azure SQL Server for free can be found here

    A description of the dataset from Microsoft is as follows:

    ‘The Contoso BI Demo dataset is used to demonstrate DW/BI functionalities across the entire Microsoft Office product family. This dataset includes C-level, sales/marketing, IT, and common finance scenarios for the retail industry and support map integration. In addition, this dataset offers large volumes of transactions from OLTP and well-structured aggregations from OLAP, along with reference and dimension data’.

    So although, when you work in business, the data warehouse will be different. In a Retail business, it will include various similarities that that of the Contoso data warehouse. Indeed there will still be similarities across other types of organisations, such as Business to Business (B2B) and Charitable organisations. They will all for example have a customer table and a sales/revenue table. Customers will have similar characteristics, such as their first order date, and last order date, and demographics, such as age, gender, and location. Field names might be different, but there will be similarities. Some organizations will have additional datasets bespoke to their industry. Online gambling companies, for example, will have tables for bonus amounts, although this might be compared to rewards given by retail companies.
    Another example is VIP/loyalty levels which are also used in many industries. Supermarkets have loyalty programs, Charities, and B2B organizations will identify high-level donors or high-value customers. Gambling Companies will have high-value losing customers.

    So let’s have a look at the Contoso Retail Datawarehouse and see what’s in it:

    Dimension Tables


    dbo.DimAccount
    SELECT TOP 100 * FROM dbo.DimAccount
    Inspecting the information we can see a list of accounts. This will likely have been pulled from an accounting (Finance Department).

    dbo.DimChannel
    SELECT TOP 100 * FROM dbo.DimChannel
    This table is just a list of 4 channels: Store, Online, Catalog, and Reseller. We would expect to use this information when querying transaction information to add what channel the transactions are from. We would expect to join using the ChannelKey field.

    dbo.DimCurrency
    SELECT TOP 100 * FROM dbo.DimCurrency
    Again a similar table to DimChannel but for currency. We would expect a sales table to include the currency key to which we can join on to add currency to the query.

    dbo.DimCustomer
    SELECT TOP 100 * FROM dbo.DimCustomer
    The most important table (in addition to sales) in the data warehouse. The big list of businesses. customers. We would expect to join this to a sales table, to report on what customers are buying.
    Fields in this table include gender, dob, email address, address, phone numbers, and first purchase date. All sensitive personal information, so you need to think about how you work with this data and who has access to it.

    dbo.DimDate
    SELECT TOP 100 * FROM dbo.DimDate
    An extremely useful and important table, that you would expect to be working with a lot in your reporting. By joining the table to sales orders, you can then not just the relevant Calendar Year/Quarter/Month, but the Financial Year, quarter, month, and week, which are often specific to the organization.

    dbo.DimEmployee
    SELECT TOP 100 * FROM dbo.DimEmployee
    A useful table of employee information likely used for a variety of report requirements, not just related to pay, but maybe related to sales.

    dbo.DimEntity
    SELECT TOP 100 * FROM dbo.DimEntity
    A table of global region and country sites, which are relevant to multinational organizations, used for Global group reporting.

    dbo. DimGeography
    SELECT TOP 100 * FROM dbo.DimGeography
    A table with countries and regions, is probably used to classify countries by Global region in some way.


    dbo.DimMachine
    SELECT TOP 100 * FROM dbo.DimMachine
    Appears to relate to certain types of equipment in stores.

    dbo.DimOutage
    SELECT TOP 100 * FROM dbo.DimOutage
    A table of computer outages, possibly related to DimMachine

    dbo.DimProduct
    SELECT TOP 100 * FROM dbo.DimProduct
    A key table, listing all the products presumably for sales, with a product key to join it to the sales table.

    dbo.DimProductCategory
    SELECT TOP 100 * FROM dbo.DimProductCategory
    A table one would expect to join to DimProduct to allow for category level reporting.

    dbo.DimProductSubCategory
    SELECT TOP 100 * FROM dbo.DimProductSubCategory
    Similar to dbo.DimProductCategory but presumably the subcategory level

    dbo.DimPromotion
    SELECT TOP 100 * FROM dbo.DimPromotion
    A table of promotions, we can join it to the sales table and add marketing information to sales

    dbo.DimSalesTerritory
    SELECT TOP 100 * FROM dbo.DimSalesTerritory
    A table of territories, presumably relevant to the sales team, which may also join to dbo.DimEmployee

    dbo.DimScenario
    SELECT TOP 100 * FROM dbo.DimScenario
    A Finance table with keys for Actual, Budget, and Forecast. There should be sales information we can join to, to determine what type of data it is (Actual, Budget, and forecast). Top-level reporting for example will want to show actual revenue against budget and forecast.

    dbo.DimStore
    SELECT TOP 100 * FROM dbo.DimStore
    A table of store names and addresses.

    Fact Tables

    dbo.FactExchangeRate
    SELECT TOP 100 * FROM dbo.FactExchangeRate
    A table of exchange rates. With a multi-national organization, the business will want to be able to report in Global sales (perhaps in dollars), so this table will be key. One would expect this to be updated on a regular basis, but it might be just once a quarter.

    dbo.FactInventory
    SELECT TOP 100 * FROM dbo.FactInventory
    An essential table keeping a count of all products in stock.
    A table relating to the costs of machines (presumably check-outs)

    dbo.FactITSLA
    SELECT TOP 100 * FROM dbo.FactITSLA
    A table with information about the uptime of machines

    dbo.FactOnlineSales
    SELECT TOP 100 * FROM dbo.FactOnlineSales
    A transaction table of online sales

    dbo.FactSales
    SELECT TOP 100 * FROM dbo.FactSales
    The most important table of all (along with the customers). The transaction table, possibly including the Online sales data. More investigation is needed.

    FactSalesQuota
    SELECT TOP 100 * FROM dbo.FactSalesQuota
    The sales quotas by store, with scenarios set next to them (Actual, Budget, Forecast), are presumably used for reporting on what stores are hitting their targets.

    dbo.FactStrategyPlan
    SELECT TOP 100 * FROM dbo.FactStrategyPlan
    An empty table

    So that’s a brief look at all the tables. As you can see we’ve covered data relevant to C-Suite (targets, store performance, global/regional sales), Sales (transaction/region/customer, employee/quotas), Marketing (Promotions), Finance (Accounts, Sales Quotas, exchange rates, costs).