Deploying AdventureWorksDW to SQL Azure, Filling in Missing Indexes

By Julie Smith

 

I’ve been working with both Power BI and Azure SQL for the past nine months. One advantage to using Azure SQL Databases with Power BI is that there is no need for a gateway, personal or enterprise. The data refreshes every 15 minutes, period. You do not need to schedule a refresh or configure anything beyond the initial connection strings with server, database, user and password.

In planning for future SQL Saturday presentations in Chattanooga, Pensacola, and Atlanta I definitely wanted to show the happy green path that I feel is a viable pure cloud solution now for data warehousing. No problem, I’ll just use AdventureWorksDW, the warehouse sample database of our beloved Adventureworks bicycle shop, right?

Not so fast. There’s not a sample Data Warehouse for Azure SQL like there is for the transactional Adventure Works:

(Go here for full instructions on deploying AdventureWorksLT from the Azure portal.)

Not deterred, I attempt to deploy my on prem copy of AdventureWorksDW2014 to Azure using SSMS, which has a built in  menu option available to deploy an on prem database to Azure:

(For full instructions on deploying to Azure using SSMS, follow this link.)

 

When I use SSMS to deploy my on prem version of AdventureWorksDW2014, I get the following errors during schema validation:

 

This is telling us that four of the tables in the sample do not have clustered indexes. Azure SQL Database insists on a clustered index for every table. So without warranty, here is a script that I used to refactor my on-prem AdventureWorksDW2014 database. After making these fixes, I was able to deploy to Azure SQL DB from SSMS with no errors.

 Use AdventureWorksDW2014
 GO
 
 
 --Change the Nonclustered index to Clustered in dbo.Databaselog table.
 
ALTER TABLE [dbo].[DatabaseLog] DROP CONSTRAINT [PK_DatabaseLog_DatabaseLogID]
GO

 
ALTER TABLE [dbo].[DatabaseLog] ADD  CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY CLUSTERED 
(
  [DatabaseLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

--Add an identity column to [dbo].[AdventureWorksDWBuildVersion] Make it the primary key & clustered index 

ALTER TABLE [dbo].[AdventureWorksDWBuildVersion]
ADD BuildVersionKey INT Identity(1,1) NOT NULL 
,
 CONSTRAINT [PK_AdventureWorksDWBuildVersion] PRIMARY KEY CLUSTERED 
(
  [BuildVersionKey] ASC
)
GO

--Make the FinanceKey the primary key and clustered index in FactFinance table
ALTER TABLE [dbo].[FactFinance]
ADD
 CONSTRAINT [PK_FactFinance] PRIMARY KEY CLUSTERED 
(
  [FinanceKey] ASC
)
GO


--In the NewFactCurrencyRate table, add an identity column and make it the primary key and clustered index
ALTER TABLE [dbo].[NewFactCurrencyRate]
ADD NewCurrencyKey INT Identity(1,1) NOT NULL 
,
 CONSTRAINT [PK_NewFactCurrencyRate] PRIMARY KEY CLUSTERED 
(
  [NewCurrencyKey] ASC
)
GO

 

 

Make sure that if you are using SQL Server 2014 version of SSMS that you are on CU 5 plus, otherwise you get another error when you attempt to use the Azure version:

Thanks to Grant Fritchey for his blog post here which helped me identity and fix this issue!

 

 

 

One thought on “Deploying AdventureWorksDW to SQL Azure, Filling in Missing Indexes”

Leave a Reply