How To Install SQL Server For Power BI

by | Power BI

In this tutorial, you’ll learn how to install SQL server for Power BI. This includes a step-by-step demonstration on how to download and install the SQL server management tool and the Contoso database.

Installing SQL server comes with a variety of advantages. You won’t need to delete a part of your column. You’ll also have the means to easily go back to the original state without having to restart Power BI. Having a SQL server helps reduce development time.

Download SQL Server

To download SQL server, look for its download page using Google. Search for SQL server and click on the first link you see.

install SQL Server

Then, download the Developer edition.

Click the downloaded file and the SQL server download window will appear. You’ll then be provided with three options: Basic, Custom, and Download Media.

install SQL Server

Custom allows us to make any changes in the server name and install analysis services. But if you want to download and install SQL server on multiple systems without having to always use an internet connection, it’s recommended to choose Download Media. But for this demonstration, Custom will be chosen.

Next, choose the download path you want. Once done, click Install.

Install SQL Server

After the installation is done, a new pop-up window will appear. Click Installation found on the left pane and then select the New SQL Server stand-alone installation option.

install SQL Server

When another window appears, click Next.

This will run and check for any update on the software.

install SQL Server

Continue clicking on next until you reach the Installation Type setting. Choose Perform a new installation and then click Next.

install SQL Server

Once you get to the Product Key setting, choose Developer in the free edition options. Then, click Next.

install SQL Server

Read and then accept the License Terms.

The next step will allow you to choose options that you can include in the SQL server version you’re installing. In this case, you only need to include Database Engine Services, Analysis Services, and Client Tools Connectivity. Once done, click Next.

install SQL Server

You now need to provide a name for the Instance ID. Since this demonstration will use the demo version, DemoDax is used.

install SQL Server

Continue clicking on Next until you reach Database Engine Configuration. Under the Authentication Mode, choose Windows authentication mode. This option is the safest one to use. Then, click Add Current User so that you will be added as a SQL server administrator.

install SQL Server

The next step will give you three options for the Server Mode. Choose Tabular Mode. Also, click Add Current User again to specify the administrator for this mode.

install SQL Server

Continue clicking Next until you reach the Ready to Install setting. Check for any missing options or features. Once you’re done checking, click Install.

install SQL Server

The installation takes some time to perform. Once it’s done, you’ll see a summary of the features you’ve downloaded along with SQL server. Now, click Close.

Install The SQL Server Client Tool

Once the SQL server has been installed, the next step is to download a client tool. A client tool will allow you to interact with the database.

Install the SQL server management studio by clicking the Install SQL Server Management Tools option.

install SQL Server

You will then be redirected to a webpage where you can download SSMS.

install SQL Server

Once it has been downloaded, open the file and choose the Install option. You also have the option to choose a different installation path. But for this case, the default path is used.

install SQL Server

Click Close once the SQL server management studio is successfully installed.

Now, launch the SQL server management studio. In the server name text box, define the server name you declared while installing SQL server. In this example, DemoDax was used.

install SQL Server

If you click the Connect option, you’ll notice that you aren’t able to connect to the SQL server using the SQL server management studio. This is because you don’t currently have a database installed.

Install The Database For SQL Server

The database is used for loading data inside Power BI. To download the database, simply go to Google and search for SQL Server Contoso Retail Database. The database you’ll be using is provided by Microsoft.

install SQL Server

Once you’re redirected to the website, click Download.

Choose the first file with the filename ContosoBIdemoBAK.exe and click Next.

install SQL Server

Once the file has been downloaded, you can now import the database in the SQL server management studio. Open the file and specify the path where you want to extract the file contents. Once done, click Okay and then Unzip.

When you go to the folder where you extracted the items, you’ll see a file with a .bak extension. This is what you’ll use for importing a database inside the SQL server management studio.

Now, open the SQL server. Right-click Database and choose Restore Database.

Choose Device and then click the ellipses (…).

Click Add and then copy the file path.

Exit SQL server and then open a new folder dialogue box and paste the path you copied.

Then, copy and paste the .bak file in this folder path.

Go back to SQL server. Right-click on Database again and choose Restore Database. Choose the same options as before. You’ll notice that the .bak file has been added as an available database.

install SQL Server

Once you click all the Okay options, the database will then be successfully installed in SQL server.

install SQL Server

If you expand the database you restored and go to Tables, you can see that there are a lot of tables you can use in your data model.

install SQL Server

Check And View Table Contents

You can execute a simple query to check the content of a given table. For this demonstration, use the code below:

The data in the table is what you’ll be importing to Power BI. But before that, you need to create several views so that it’s easier to modify the content.

The code you see in this demonstration can be copied from the file available in the course module. Click Execute and you’ll then see that this creates several views in the data model.

Right-click the Views option and choose Refresh.

You’ll see that several views were created inside the View folder.

Right-click on any View and choose Select Top 100 Rows. You can then see the data contained inside that particular View option.

install SQL Server

***** Related Links *****
xmSQL Aggregations In VertiPaq DAX Studio
Power BI And SSAS – Sorting Columns Using TE3 & C#
How To Install DAX Studio & Tabular Editor In Power BI

Conclusion

In summary, the tutorial provides you with a direct step-by-step demonstration on how to successfully install SQL server. Make sure to follow everything in order to avoid errors.

SQL is a valuable tool that complements Power BI. Though it’s not necessary to install it, it’s recommended. Later down the line, this will become a useful tool when modifying your Power BI data model.

Also, a simple demonstration was presented on how to change View options for tables. In succeeding tutorials, more features of the SQL Server will be discussed which will assist you in optimizing the development time when creating Power BI reports.

Enterprise DNA Experts

Related Posts

Using the DISTINCT Function Effectively in DAX

DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.