{"id":174285,"date":"2023-11-30T08:56:00","date_gmt":"2023-11-30T07:56:00","guid":{"rendered":"https:\/\/liora.io\/en\/?p=174285"},"modified":"2026-08-08T13:01:59","modified_gmt":"2026-08-08T12:01:59","slug":"connecting-power-bi-and-sql-server-how-do-i-do-it","status":"publish","type":"post","link":"https:\/\/liora.io\/en\/connecting-power-bi-and-sql-server-how-do-i-do-it","title":{"rendered":"Seamlessly Connect Power BI and SQL Server: A Step-by-Step Guide"},"content":{"rendered":"\n<p><strong>SQL Server and Power BI are two complementary tools for data analysis. On one hand, SQL Server is a database management system (DBMS) that allows for storing, controlling, sorting, and updating databases using the SQL language. On the other hand, Power BI is a business intelligence tool designed to help organizations make informed decisions using data. So, how can you connect SQL Server and Power BI? Discover three approaches to achieve your goals.<\/strong><\/p>\n\n\n<h2 class=\"wp-block-heading\" id=\"sql-server-and-power-bi-connected-by-a-script\">SQL Server and Power BI connected by a script<\/h2>\n\n\n<p>To connect <strong>Power BI and SQL<\/strong> Server using a script, you can <a href=\"https:\/\/liora.io\/en\/avoid-these-top-5-common-python-coding-mistakes\">use R or Python.<\/a> Here, you will find a method based on the Python language.<\/p>\n\n\n<h2 class=\"wp-block-heading\" id=\"step-1-install-these-libraries-using-pip-in-the-command-terminal\"><strong>Step 1: Install these libraries using Pip in the command terminal:<\/strong><\/h2>\n\n\n<p><strong>pip install pyodbc<\/strong><\/p>\n\n\n<p><strong>pip install pandas<\/strong><\/p>\n\n\n<p><strong>pip install matplotlib<\/strong><\/p>\n\n\n<ul class=\"wp-block-list\">\n<li>The <strong>pyodbc library<\/strong> provides access to ODBC (Open Database Connectivity) databases.<\/li>\n<li>The pandas library allows for data manipulation and analysis.<\/li>\n<li>Finally, matplotlib allows you to <a href=\"https:\/\/liora.io\/en\/pandas-the-python-library\">plot graphs and charts directly from pandas.<\/a><\/li>\n<\/ul>\n\n\n<p>Make sure to check where the <strong>Python Home Directory<\/strong> points to. To do this, go to &#8216;Files&#8217; and then &#8216;Options and Settings&#8217;. Click on &#8216;Python Scripting&#8217; and verify that &#8216;Python Home Directory&#8217; points to your Local Python Installation Path and your &#8216;<strong>Python IDE<\/strong>&#8216; on which the system executes the script.<\/p>\n\n\n<h2 class=\"wp-block-heading\" id=\"step-2-select-get-data-from-the-panel-at-the-top-of-your-screen\"><strong>Step 2: Select &#8216;Get Data&#8217; from the panel at the top of your screen.<\/strong><\/h2>\n\n\n<p>The &#8216;Get Data&#8217; panel opens. Then, select &#8216;Other&#8217; and then &#8216;Python script&#8217;. Click on &#8216;Connect&#8217;.<\/p>\n\n\n<h2 class=\"wp-block-heading\" id=\"step-3-in-the-dialog-box-enter-the-following-script\">Step 3: In the dialog box, enter the following script:<\/h2>\n\n\n<p><strong>import pandas as pd<\/strong><\/p>\n\n\n<p><strong>import pyodbc<\/strong><\/p>\n\n\n<p>conn = pyodbc.connect(&#8216;Driver={SQL Server};&#8217;\n&#8216;Server=servername;&#8217;\n&#8216;Database=databasename;&#8217;\n&#8216;Trusted_Connection=yes;&#8217;)<\/p>\n\n\n<p>cur = conn.cursor()<\/p>\n\n\n<p>sqlquery = pd.read_sql_query(&#8216;SELECT * FROM tablename&#8217;, conn)<\/p>\n\n\n<p>print(sqlquery)<\/p>\n\n\n<p>This <a href=\"https:\/\/liora.io\/en\/sql-developer-what-is-it-whats-it-for\">query connects to SQL Server<\/a> using the &#8216;pyodbc&#8217; library installed earlier and uses this connection to retrieve data from our table. These data are then converted and loaded into Power BI.<\/p>\n\n\n<h2 class=\"wp-block-heading\" id=\"step-4-in-the-navigator-panel-select-the-result-and-click-on-load\"><strong>Step 4: In the &#8216;Navigator&#8217; panel, select the result and click on &#8216;Load&#8217;.<\/strong><\/h2>\n\n\n<p>You will then have your data, which you can visualize as desired.<\/p>\n\n\n<h2 class=\"wp-block-heading\" id=\"using-a-connector-for-power-bi-and-sql-server\">Using a connector for Power BI and SQL Server<\/h2>\n\n\n<p>In this method, you are using only <em>Power BI and a connector.<\/em><\/p>\n\n\n<p>1. Open Power BI Desktop on your computer.\n2. In the panel at the top of your screen, click on &#8220;SQL Server.&#8221;\n3. Enter the server name in the &#8220;Server&#8221; dialog box of SQL Server database. Select &#8220;DirectQuery.&#8221; In the &#8220;Name&#8221; dialog box, you can specify the name of your database.\n4. Click &#8220;OK.&#8221;<\/p>\n\n\n<p>You will then have your data table, and you can visualize the data according to your needs.<\/p>\n\n\n<p>This approach is convenient if you have already established a connection between <strong>Power BI and SQL Server<\/strong>.<\/p>\n\n\n<p>Creating the connection between these two entities can sometimes be a bit cumbersome. The other drawback occurs when you select &#8216;Import&#8217; as your <strong>Connectivity Mode.<\/strong> In this case, all the data is imported, making it challenging to fully utilize <a href=\"https:\/\/liora.io\/en\/demystifying-power-bi-date-formats-the-most-commonly-used-formats\">Power BI&#8217;s visualization potential.<\/a><\/p>\n\n\n<h2 class=\"wp-block-heading\" id=\"export-sql-server-data-to-excel-and-import-into-power-bi\">Export SQL Server data to Excel and import into Power BI<\/h2>\n\n\n<p>This process can indeed be time-consuming, especially if you perform this task regularly. Nevertheless, this old-school method works well.<\/p>\n\n\n<p>Step 1: In SQL Server, right-click on the database containing your future table. Then, click on &#8216;Tasks&#8217; and &#8216;Export Data&#8217;.<\/p>\n\n\n<p>Step 2: A panel opens: &#8216;Choose a Data Source&#8217;. Select &#8216;SQL Server Native Client 11.0&#8217; as the data source and click &#8216;Next&#8217;.<\/p>\n\n\n<p>Step 3: Click on &#8216;<a href=\"https:\/\/liora.io\/en\/excel-vba-how-to-automate-tasks-with-this-language\">Microsoft Excel<\/a>&#8216; for the destination. Specify the file path and name in the &#8216;Excel file path&#8217; dialog box. Then, click &#8216;Next&#8217;.<\/p>\n\n\n<p>Step 4: If you want to export specific data, select &#8216;Write a query to specify the data to transfer&#8217;. Click &#8216;Next&#8217;.<\/p>\n\n\n<p>Step 5: In the &#8216;Provide a Source Query&#8217; panel, enter the following script:<\/p>\n\n\n<p><strong>SELECT * FROM tablename;<\/strong><\/p>\n\n\n<p>Click &#8216;Next&#8217;.<\/p>\n\n\n<p>In the &#8216;Select source tables and views&#8217; and &#8216;Review data mapping type&#8217; panels, click &#8216;Next&#8217;. Then, select &#8216;Run immediately&#8217; in the &#8216;Save and Run Package&#8217; panel and click &#8216;Finish&#8217;.<\/p>\n\n\n<p>Step 6: Open <a href=\"https:\/\/liora.io\/en\/excel-to-power-bi-how-to-transform-a-pivot-table-in-excel-into-a-dataset-that-can-be-used-by-power-bi\">Power BI and select Excel<\/a> in the panel at the top of your screen. Find the exported file from SQL Server.<\/p>\n\n\n<p>Step 7: In the &#8216;Navigator&#8217; panel, select your data and click &#8216;Load&#8217;.<\/p>\n\n\n<p>You will then see your data appear in Power BI.<\/p>\n\n\n<h2 class=\"wp-block-heading\" id=\"conclusion\">Conclusion<\/h2>\n\n\n<p>There are several ways to connect <strong>Power BI and SQL Server,<\/strong> each with its own advantages and disadvantages. You can use an R or Python script to establish the connection, use a specific connector, or export SQL data to Excel and import it into Power BI. The method to use depends on your preferences and specific needs.<\/p>\n\n\n<p>By using one of these approaches, you can easily connect SQL Server and Power BI to access your data and gain insights to <strong>improve your decision-making.<\/strong><\/p>\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center wp-container-core-buttons-is-layout-5ee10de4\" style=\"margin-top:32px;margin-bottom:32px\"><div class=\"wp-block-button\"><a class=\"wp-block-button__link wp-element-button\" href=\"\/en\/courses\/data-ai\/power-bi\">Learn more about Power BI<\/a><\/div><\/div>\n\n","protected":false},"excerpt":{"rendered":"<p>SQL Server and Power BI are two complementary tools for data analysis. On one hand, SQL Server is a database management system (DBMS) that allows for storing, controlling, sorting, and updating databases using the SQL language. On the other hand, Power BI is a business intelligence tool designed to help organizations make informed decisions using [&hellip;]<\/p>\n","protected":false},"author":76,"featured_media":174290,"comment_status":"open","ping_status":"open","sticky":false,"template":"elementor_theme","format":"standard","meta":{"_acf_changed":false,"editor_notices":[],"footnotes":""},"categories":[2433],"class_list":["post-174285","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-ai"],"acf":[],"_links":{"self":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/174285","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/users\/76"}],"replies":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/comments?post=174285"}],"version-history":[{"count":2,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/174285\/revisions"}],"predecessor-version":[{"id":210052,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/174285\/revisions\/210052"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media\/174290"}],"wp:attachment":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media?parent=174285"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/categories?post=174285"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}