Setting Up a Bi Development Environment
By Dani
This post describes how I set up the environment I use for Power BI development. My environment is zero-cost as to software and includes data similar to what you will encounter in a corporate environment. These instructions are intended for someone who has familiarity with these tools in a professional capacity already and is just looking to get their own environment set up. That said, none of this is complicated. If you’re willing to give it a go without step-by-step instructions, you’ll probably figure it out.
Step 1: Install SQL Server
I downloaded this here, but if you don’t find it there, search for “SQL Server Developer Edition Download.” I take all the defaults when installing. You’ll also want Sql Server Management Studio for connecting to your database.
I installed SQL Server 2022, and it was automatically configured to use integrated authentication, meaning the local user under which I installed it automatically gets logged on when connecting. No need to use default passwords or set up security to connect the first time. One thing to note if you do set up some SQL Server logins to connect from other tools, all other forms of authentication are disabled by default. To enable SQL Server logins, from the SSMS Object Explorer, right-click server > Properties > Security and select SQL Server and Windows Authentication mode in the Server Authentication section.
Step 2: Install Contoso Retail Data Warehouse
I downloaded this here, but if you don’t find it there, search for “Contoso Retail Data Warehouse.” This is a self-extracting executable. It will unzip a .bak file which is a SQL Server backup file you need to restore to your SQL server to get the Contoso data. I recommend selecting a location other than the default in the unzip tool. You will navigate to this location with one of those old style file navigators that’s not too friendly, so I like to stash mine in a folder that’s very simple to get to from the root of the c drive. It’s just temporary, and you can delete it shortly.
To restore the backup, connect to SQL Server with SSMS. In the Object Explorer panel, right-click Databases > Restore Database. Select Device and navigate to the .bak file you unzipped and hit OK. After it restores, you’ll have a new database called ContosoRetailDW.
Step 3: (Optional) Modify Contoso DWH
The dates in the Contoso data range from 2009 to 2011. I prefer the content I’m sharing to have more current dates. I wrote three simple stored procedures to achieve this. After running them, all relevant dates in the Contoso data will be updated to span from prior year to next year, relative to the year in which the procedures are run.
This is achieved by calculating the number of days between the latest date in the Contoso data and the end of next year, then adding that number of days to each appropriate date in the database. All dates between the last date in the DimDate, the Contoso date dimension, and the end of next year are also added to DimDate. Some columns like LoadDate and anything in DimDate itself are not updated.
This treatment could be more sophisticated by trying to keep the day of week and month of year the same, but for now, I’m going for simple. The procedures are straightforward, and you can tweak them to the behavior you’d prefer.
Procedure #1: forward_contoso
This procedure calculates the number of days by which dates will be forwarded, then calls the next two procedures using that information. This is the only procedure you need to call as it executes the other two.
create procedure forward_contoso
as
DECLARE @forward_days int;
DECLARE @start_date date;
DECLARE @latest_date date = datefromparts(year(current_timestamp)+1,12,31); --last day of next year
SELECT @start_date = (select dateadd(day, 1, max(datekey)) as max_date from FactSales);
SET @forward_days = datediff(day,@start_date,@latest_date);
exec expand_contoso_dimdate @forward_days; --add dates through @start_date to + @forward_days DimDates
exec forward_contoso_dates @forward_days; --adjust fact and dimension dates by @forward_days
;
Procedure #2: expand_contoso_dimdate
This procedure adds all the rows required for the new dates to DimDate. Because many of the dates being updated are foreign keys to DimDate.DateKey, the next procedure will fail if these dates are not found in the DimDate.
create procedure expand_contoso_dimdate(@forward_days int)
as
drop table if exists #daterows;
DECLARE @start_date date;
SELECT @start_date = (select dateadd(day, 1, max(datekey)) as max_date from DimDate);
; WITH InfiniteRows (RowNumber) AS (
-- Anchor member definition
SELECT 1 AS RowNumber
UNION ALL
-- Recursive member definition
SELECT a.RowNumber + 1 AS RowNumber
FROM InfiniteRows a
WHERE a.RowNumber <= @forward_days
)
SELECT
dateadd(dd,RowNumber-1,@start_date) as datekey
INTO #daterows
FROM InfiniteRows
OPTION (MAXRECURSION 0) --careful, but def ok for 50k rows
;
insert into ContosoRetailDW.dbo.DimDate
select
datekey
, format(datekey, 'yyyy-MM-dd') as FullDateLabel
, format(datekey, 'yyyy/MM/dd') as DateDescription
, datepart(year,DateKey) as CalendarYear
, 'Year ' + format(DateKey, 'yyyy') as CalendarYearLabel
, DATEPART(year,DateKey) * 10 + case when MONTH(datekey) < 7 then 1 else 2 end as CalendarHalfYear
, 'H' + format(case when MONTH(datekey) < 7 then 1 else 2 end, '0') as CalendarHalfYearLabel
, DATEPART(year,DateKey) * 10 + DatePart(quarter, DateKey) as CalendarQuarter
, 'Q' + format(DATEPART(quarter,DateKey), '0') as CalendarQuarterLabel
, DATEPART(year, DateKey) * 100 + Datepart(month, DateKey) CalendarMonth
, format(DateKey, 'MMMM') as CalendarMonthLabel
, datepart(year, datekey) * 100 + datepart(week,datekey) as CalendarWeek
, 'Week ' + format(datepart(week,datekey),'#') as CalendarWeekLabel
, (datepart(year,datekey) * 100 + datepart(week, datekey)) * 10 + datepart(dw, datekey) CalendarDayOfWeek
, format(DateKey, 'dddd') as CalendarDayOfWeekLabel
, datepart(year, datekey) as FiscalYear
, 'FiscalYear ' + format(datepart(year, datekey),'0000') as FiscalYearLabel
, DATEPART(year,DateKey) * 10 + case when MONTH(datekey) < 7 then 1 else 2 end as FiscalHalfYear
, 'H' + format(case when MONTH(datekey) < 7 then 1 else 2 end, '0') as FiscalHalfYearLabel
, DATEPART(year,DateKey) * 10 + DatePart(quarter, DateKey) as FiscalQuarter
, 'Q' + format(DATEPART(quarter,DateKey), '0') as FiscalQuarterLabel
, DATEPART(year, DateKey) * 100 + Datepart(month, DateKey) FiscalMonth
, 'Month ' + format(datepart(month,datekey), '##') as FiscalMonthLabel
, case when datepart(wk,datekey) in (1,7) then 'WeekEnd' else 'WorkDay' end as isWorkDay
, 0 as IsHoliday
, 'None' as HolidayName
, 'None' as EuropeSeason
, 'None' as NorthAmericaSeason
, 'None' as AsiaSeason
from #daterows
order by datekey desc
;
Procedure #3: update_contoso_dates
This procedure updates the appropriate columns in each table by adding the number of days calculated by forward_contoso to the current value.
create procedure forward_contoso_dates(@forward_days as int) as
UPDATE DimMachine SET
ServiceStartDate = dateadd(day, @forward_days, DimMachine.ServiceStartDate)
, DecommissionDate = dateadd(day, @forward_days, DimMachine.DecommissionDate)
, LastModifiedDate = dateadd(day, @forward_days, DimMachine.LastModifiedDate)
, UpdateDate = CURRENT_TIMESTAMP
;
UPDATE DimProduct SET
AvailableForSaleDate = dateadd(day, @forward_days, DimProduct.AvailableForSaleDate)
, StopSaleDate = dateadd(day, @forward_days, DimProduct.StopSaleDate)
, UpdateDate = CURRENT_TIMESTAMP
;
UPDATE DimEntity SET
StartDate = dateadd(day, @forward_days, DimEntity.StartDate)
, EndDate = dateadd(day, @forward_days, DimEntity.EndDate)
, UpdateDate = CURRENT_TIMESTAMP
;
UPDATE DimSalesTerritory SET
StartDate = dateadd(day, @forward_days, DimSalesTerritory.StartDate)
, EndDate = dateadd(day, @forward_days, DimSalesTerritory.EndDate)
, UpdateDate = CURRENT_TIMESTAMP
;
UPDATE DimPromotion SET
StartDate = dateadd(day, @forward_days, DimPromotion.StartDate)
, EndDate = dateadd(day, @forward_days, DimPromotion.EndDate)
, UpdateDate = CURRENT_TIMESTAMP
;
UPDATE DimCustomer SET
BirthDate = dateadd(day, @forward_days, DimCustomer.BirthDate)
, DateFirstPurchase = dateadd(day, @forward_days, DimCustomer.DateFirstPurchase)
, UpdateDate = CURRENT_TIMESTAMP
;
UPDATE DimEmployee SET
HireDate = dateadd(day, @forward_days, DimEmployee.HireDate)
, BirthDate = dateadd(day, @forward_days, DimEmployee.BirthDate)
, StartDate = dateadd(day, @forward_days, DimEmployee.StartDate)
, EndDate = dateadd(day, @forward_days, DimEmployee.EndDate)
, UpdateDate = CURRENT_TIMESTAMP
;
UPDATE FactExchangeRate SET
DateKey = dateadd(day, @forward_days, FactExchangeRate.DateKey)
, UpdateDate = CURRENT_TIMESTAMP
;
UPDATE FactITMachine SET
FactITMachine.Datekey = dateadd(day, @forward_days, FactITMachine.Datekey)
, FactITMachine.UpdateDate = CURRENT_TIMESTAMP
;
UPDATE FactITSLA SET
DateKey = dateadd(day, @forward_days, FactITSLA.DateKey)
, OutageStartTime = dateadd(day, @forward_days, FactITSLA.OutageStartTime)
, OutageEndTime = dateadd(day, @forward_days, FactITSLA.OutageEndTime)
, UpdateDate = CURRENT_TIMESTAMP
;
UPDATE FactOnlineSales SET
FactOnlineSales.DateKey = dateadd(day, @forward_days, FactOnlineSales.DateKey)
, FactOnlineSales.UpdateDate = CURRENT_TIMESTAMP
;
UPDATE FactStrategyPlan SET
FactStrategyPlan.Datekey = dateadd(day, @forward_days, FactStrategyPlan.Datekey)
, FactStrategyPlan.UpdateDate = CURRENT_TIMESTAMP
;
UPDATE FactSales SET
FactSales.DateKey = dateadd(day, @forward_days, FactSales.DateKey)
, FactSales.UpdateDate = CURRENT_TIMESTAMP
;
UPDATE FactInventory SET
FactInventory.DateKey = dateadd(day, @forward_days, FactInventory.DateKey)
, FactInventory.UpdateDate = CURRENT_TIMESTAMP
;
UPDATE FactSalesQuota SET
FactSalesQuota.DateKey = dateadd(day, @forward_days, FactSalesQuota.DateKey)
, FactSalesQuota.UpdateDate = CURRENT_TIMESTAMP
;
UPDATE DimStore SET
DimStore.OpenDate = dateadd(day, @forward_days, DimStore.OpenDate)
, DimStore.CloseDate = dateadd(day, @forward_days, DimStore.CloseDate)
, DimStore.LastRemodelDate = dateadd(day, @forward_days, DimStore.LastRemodelDate)
, DimStore.UpdateDate = CURRENT_TIMESTAMP
;