Welcome to my first blog post!
I've promised to give back to the SQL and Power BI community for almost six years now and have never been quite ready. The community has done so much for me, heroes like the people at London Power BI User Group - James Dale (my first dashboard in a day!) & Prathy, Mark Wilcox (London Business Analysis Group), Rishi Sapra (Power Platform for Finance), Samit Sani (Heathrow legend), the crew over at Redgate, Adam and Patrick from Guy-in-a-cube, the Italians Marco and Alberto (SQL BI), Alex (both of them), the incredible Leila and Raza over at Radacad, Matt Allington (Excelerator) , Imke Feldmann (Power Query extraordinaire at Biccountant), Chris Webb (Cross Join), Melissa Coates (SQL Chick), the team at Bluegranite, David Eldersveld (Dataveld)…. and the Power BI team themselves, especially Christian Wade, Will Thompson and Lukasz Pawlowski. So many people have been so generous with their time.
I'm really looking forward to contributing to this awesome community. It might take me a while to find my voice and get the hang of this - so please bear with me. I am no data warehousing or enterprise BI expert. I am an experienced Power BI practitioner aiming to get the job done for the business whilst trying to continually develop best practise - on a platform that changes and grows on a weekly basis!!
I'm also going to try to document some of the mistakes and wrong pathways along the way in the hope that it'll save you time here and there.
So here goes... 🤞
Having signed up to present at the awesome New Stars of Data, I wanted a meaty sample dataset to work with in SQL, something that anyone could download and play with themselves without having to configure a local SQL instance. I love SQL and love Power BI, they are an amazing match up - but configuring a local SQL instance is horrible business.
All I needed was a Microsoft demo database, .bacpac file to restore to Azure SQL, hook it up to a Power BI report and let's go!
So over to the Azure portal for my first attempt, create a new Resource Group (free), a blank SQL Server (free) (instructions here) and then click to install the Sample AdventureWorksLT sample database - for about £4.60 a month. Simple.
Then open it up in Power BI desktop and … slump…. The database only contains one day of sales data and only a meagre handful of table. So, delete the database (or you'll be charged) and back to Google.
The only AdventureWorks versions I could find made available by Microsoft seem to be .bak files which you cannot restore to Azure SQL (for which you need a .bacpac file format). None of the more recent sample databases like WorldWideImporters are available in .bacpac either.
Finally I found this, and my goodness am I grateful for the generosity of the community. They've found exactly the same problem and solved it for us all. To be fair, the AdventureWorks database is pretty old contains a bunch of artifacts that are not compatible with Azure SQL. We only really want the tables and views for building a Power BI report over, so it''s great that these helpful people have stripped out all the incompatible parts and the final .bacpac file can be downloaded here.
https://www.businessintelligence.info/osbi/adventure-works-2014-full-for-azure.html
Now the steps are easy.
Download the AdventureWorks2014.bacpac file to your local drive.
Open up SQL Server Management Studio (tip: you want at least v18 or you'll get a compatibility error while trying to import the database).
Connect to your Azure SQL instance xx-myserver-xx.database.windows.net in SSMS
Right-click on Databases and choose Import Data-tier Application
Follow the wizard instructions to Import from local disk
Choose the new database name (e.g. AdventureWorks2014), select the pricing tier 'Basic' for cheap and cheerful and about £4.60 / month, or Standard - S0 for a bit more grunt at about £14 / month (I'll think I'll use the latter during my demos to be on the safe side)
Click >>Next and then >>Finish. After a few minutes, your AdventureWorks2014 database should be restored on your Azure SQL server. Right-click on the Databases in SSMS and refresh and you should see the database - you're ready to start exploring!
The folks at dataedo.com have documented the AdventureWorks tables for us:
https://dataedo.com/samples/html/AdventureWorks/doc/AdventureWorks_2/tables.html
And there already a few views in the database that you can play with to understand some of the joins between the tables:
https://dataedo.com/samples/html/AdventureWorks/doc/AdventureWorks_2/views.html
If you are up for a really in-depth look at how to build a complete business intelligence solution from the full AdventureWorks database - look no further than the Italians!
https://www.sqlbi.com/wp-content/uploads/SQLBI-Methodology-At-Work-draft-1.0.pdf
I used those three resources above to start to understand the AdventureWorks tables and to focus in on a narrow slice of the model - Sales (fact) broken down by dimensions of Resellers (stores), Sales Person and Product.
To see the first Power BI desktop model and the SQL views I created, you can look forward to the next instalment here. There's so much to learn about the pros and cons of using SQL vs Power Query vs DAX in preparing your data model.
Hope that helps 😊.