Sample Azure Database for Power BI

When working with Power BI, a great way to test the functionality is to attach your desktop to a sample SQL database hosted in Azure. The dataset that we will be using is called AdventureWorks. This is familiar to many programmers as Microsoft has reused this example many times for different programming techniques. This article is the continuation of a series on Power BI and the capabilities of the product. The purpose of this exercise is go into Azure, spin up the database, configure the access, and attach the database to Power BI. Then, we will use the data to quickly model a sample page that can be published to PowerBi.com. The assumption of this article is that you already have an Azure Active Directory account that is tied to your Power BI account. In addition, it assumes that you have already downloaded the local desktop version of Power BI.

Set up an Azure Subscription

The first step is to log in to Azure with your credentials. For this example, we are using the new Azure portal, which is different from the Azure classic environment.

The database structure in Azure requires a payment method, which you select when you set up a subscription. If you have a Visual Studio license or MCT professional certification, these will provide you with a monthly budget of $50 for testing.

To log in to Azure and set up a subscription:

1. Open your browser and navigate to https://portal.azure.com.
2. Log in to Azure using your AAD credentials for PowerBI.
3. Click Billing.
4. Click Subscriptions.
5. Click New subscription.
6. Choose the subscription that best fits your requirements. For our purposes you can use Pay-As-You-Go or use your Visual Studio Professional license. Fill in the required billing information and complete the subscription process.
7. Once you have a subscription, exit billing.

Set up an Azure Sample Database

The next step is to set up the database using the wizard. This guides you through the choices that are possible. It is important to name the database with something that you can both remember and find again. Using the date of creation and your initials will help ensure that no two databases have similar names.

To set up the database:

1. In Azure, click SQL Databases.
2. Click Add.
3. Enter a name for the database. For our demonstration, we will use the name rsmSampleAdventure08312017spm.
4. Select the subscription you created earlier.
5. Select Create new under Resource Group.
6. In the Name field, enter MyResourceGroup.
7. Select Sample (AdventureWorksLT) in the Select source
8. Click Server.
9. Click Create a new server.
10. Enter the following information:
    • Server name: sys08312017-demo-spm
    • Server admin login: administrator08312017
    • Password: For this field, we suggest pa$$w0rd. Ensure you write down the password.
11. Select a location.
12. Click Select.
13. Select Not now in the Want to use SQL elastic pool
14. Click Pricing Tier.
15. Select Basic.

Note: The default pricing tier is Standard S2; however, this option will consume more resources and incur more costs than needed for the demo database.

16. Click Apply.
17. Click Create.
18. There will be a notification that the setup is completed. Once complete, you will receive notification of deployment.
19. Click Refresh on your SQL databases
20. Select the database that you created.
21. Click Database.
22. Click Properties.
23. Under Server Name, click Copy to clipboard.
24. Leave the Azure page open.

Running Power BI and Connecting the Database

For this part of the process, you will need to launch the Power BI desktop. If you have not, simply go to powerbi.microsoft.com, log in, and download Power BI before you follow the steps in this section.

To attach the data source in PowerBI:

1. Open the Power BI desktop.
2. Click Get Data.
3. Choose Database.
4. Select SQL Server database 
   
5. Click Connect
6. Enter your server name in the Server field 
   
7. Click OK.
8. Click Database.
9. In the Azure website, copy the server admin login user name.
10. In Power BI, paste the user name into the User name field
    
11. Enter the password you created earlier.
12. Click Connect.
13. Select the database name.
14. Click Product.
15. Click Load.

Rendering in Power BI

Finally, we are ready to test our connected tables.

To create sample visualizations:

1. Click the stacked column chart visualization.
2. Drag Name to Axis.
3. Drag StandardCost to Value.
4. Click the slicer visualization.
5. Drag Size to Field.
6. Click the slicer visualization.
7. Click the funnel visualization.
8. Drag Color to Group.
9. Drag Color to Values.
10. Now click into the visualization to see the interaction.

Conclusion

This article explored the process of creating a sample Azure database. First, we set up the Azure subscription. Then, we created the database, connected to the database, and rendered visualizations based on a table. The Adventureworks data gives you the ability to test theories, provide proof of concept, and to model with a simple dataset before taking on other pieces.

For more information, I would recommend that you attend our Power BI training in person or on-demand online to learn more.

Plus keep an eye out for more articles that dive in to the technical aspects of PowerBI DAX language and X++ coding.  Visit academy.rsmus.com for more information and training materials that will cover this and hundreds of other topics for PowerBI, AX2012, and MsDyn365.

by Shaun McMikle for RSM

Shaun McMikle