Attend FREE Webinar on Data Science for Career Growth Register Now

Data Analytics Blog

Data Analytics Case Studies, WhyTos, HowTos, Interviews, News, Events, Jobs and more...

Managing Power BI Data Sources: Tips and Techniques

5 (100%) 2 votes

Managing data coming from various sources lies at the core of Power BI. Whether you are exploring data, creating charts and dashboards, or asking questions, all data visualizations and answers you see, all of them get their underlying data from multiple data sources. Power BI Excel Data Source is a broad and elaborate topic, which we will explain in details in this discussion.

The best part of Power BI is that it supports a wide range of data sources and allows you to connect to different flat files, such as SQL database, and Azure cloud or even web platforms such as Facebook, Google Analytics, and Salesforce objects. Compatible Power BI Excel Data Sources include data types like the Whole Number, Decimal Number, Currency, Date, True/False, and Text.

Power BI and Dynamics are a very important part of our discussion on Power BI Data Sources. Power BI for Office 365 cloud service works in liaison with Microsoft Dynamics 365 to automatically refresh the Microsoft Dynamics 365 (online) data displayed. The Power BI Desktop or Microsoft Office Excel Power Query is more commonly used for authoring reports. Power BI is also useful for sharing dashboards and refreshing data from Microsoft Dynamics 365 (online), sales, marketing, and service personnel to improve workplace productivity.

In this discussion, I will provide an overview of the different types of data sources you can connect to from the Power BI service, right from your Power BI service site. My discussion will include:

  • Power BI Excel Data Source
  • Power BI and dynamics
  • Other data sources for Power BI

You can click Get data and it shows all the available data connections. Moreover, Power BI allows you to connect to different flat files, SQL database, and Azure cloud or even web platforms such as Facebook, Google Analytics, and Salesforce objects. It also includes ODBC connection to connect to other ODBC data sources, which are not listed.

How to Import Excel Data into Power BI

If you choose Import, any supported data in tables will be imported into a new dataset in Power BI. If you have any Power View sheets, those sheets will be re-created in Power BI as reports.

One of the best things about importing excel data into Power BI is that while you edit your workbook, the changes are saved and synchronized with the dataset in Power BI, within a span of an hour. For more immediate gratification, simply click Publish again, and your changes are exported. This also includes any visualizations you have in reports and dashboards will be updated.

If you are using Excel 2016, you can also use Publish > Export. You may check out Publish to Power BI from Excel 2016 for more detailed information on this.

Power BI Excel Data Source: Connecting, Managing, and Viewing Excel in Power BI

When you choose Connect, your workbook will appear in Power BI just like it would in Excel Online. However, Power BI gives you some attractive features to help you locate elements from your worksheets right to your dashboards.

If you want to make any changes in the workbook in Power BI, click Edit, and you can edit your workbook in Excel Online or open it in Excel. Any changes made are saved to the workbook on OneDrive.

When choosing this way, no dataset is created in Power BI. Your workbook will appear in your Power BI workspace navigation pane under Reports. Connected workbooks have a special Excel icon. Choose this option if you only have data in worksheets, or you have ranges, PivotTables, and charts you want to pin to dashboards.

Power BI Excel Data Source: Getting data from Power BI Desktop files

business intelligence and reporting are enjoyable with Power BI Desktop. You might be connecting to distributed data sources, querying, and transforming data, modeling your data, and creating powerful and dynamic reports, Power BI Desktop ensures faster and better completion of business intelligence tasks. Once you bring data into Power BI Desktop and create a few reports, remember to get your saved file into the Power BI service.

Data Analytics Course by Digital Vidya

Free Data Analytics Webinar

Date: 22nd Nov, 2018 (Thursday)
Time: 3 PM to 4 PM (IST/GMT +5:30)

File Saving with Power BI:

Saving in Local: If you save your file to a local drive on your computer or another folder location in your organization, you can import your file or you can publish from Power BI Desktop to get its data and reports into Power BI. A copy of the file will be retained on your local drive. A new dataset is created in Power BI and data and the data model from the Power BI Desktop files are loaded into the dataset. If your file has any reports, those will appear in your Power BI site under Reports.

Saving in OneDrive:

  • OneDrive Business – If you are using OneDrive for Business, you must sign into it with the same account you sign into Power BI with. This is the most effective way to keep your work in Power BI Desktop and your dataset, reports, and dashboards in Power BI in-sync. Since both Power BI and OneDrive are in the cloud, Power BI connects to your file on OneDrive about every hour. If any changes are found, your dataset, reports, and dashboards are automatically updated in Power BI.
  • OneDrive Personal – If you save your files to your own OneDrive account, you will get the same benefits as you would with OneDrive for Business. The biggest difference is when you first connect to your file (using Get Data > Files > OneDrive – Personal) you will need to sign in to your OneDrive with your Microsoft account, which is usually different from what you use to sign in to Power BI. When signing in with your OneDrive with your Microsoft account, be sure to select the Keep me signed in option. This way, Power BI will be able to connect to your file about every hour and make sure your dataset in Power BI is in-sync.
  • Saving to SharePoint Team-Sites –  Saving your Power BI Desktop files to SharePoint – Team Sites is almost the same as saving to OneDrive for Business. The basic difference is how you connect to the file from Power BI. You can specify a URL or connect to the root folder.

Following are the available data sources in Power BI −

  • Flat Files
  • SQL Database
  • OData Feed
  • Blank Query
  • Azure Cloud platform
  • Online Services
  • Blank Query

Power BI Data Sources includes other data sources such as Hadoop, Exchange, or Active Directory.

To get data in Power BI desktop, click the Get data option on the main screen. It shows you the most common data sources first. Then, you need to click the More option to see a full list of available data sources.

available-data

When you click the More tab, you can see a new navigation window, where on the left side it shows a category of all available data sources. You also have an option to perform a search at the top.

data-source

Following are the various data sources listed −

  • All

Under this category, you can see all the available data sources under Power BI desktop.

  • File

When you click File, it shows you all flat file types supported in Power BI desktop. To connect to any file type, select the file type from the list and click Connect. You must provide the location of the file.

  • Database

When you click the Database option, it shows a list of all the database connections that you can connect to.

database_image

To connect to any database, select a Database type from the list as shown in the above screenshot. Click Connect.

You must pass Server name/ Username and password to connect. You can also connect via a direct SQL query using Advance options. You can also select Connectivity mode- Import or DirectQuery.

Note: You cannot combine import and DirectQuery mode in a single report.

Data Analytics Course by Digital Vidya

Free Data Analytics Webinar

Date: 22nd Nov, 2018 (Thursday)
Time: 3 PM to 4 PM (IST/GMT +5:30)

Import vs DirectQuery

DirectQuery option, another feature of Power BI, limits the option of data manipulation and the data stays in the SQL database. DirectQuery is live and there is no need to schedule refresh as in the Import method.

Import method allows you to perform data transformation and manipulation. When you publish the data to PBI service, the limit is 1GB. It consumes and pushes data into Power BI Azure backend and data can be refreshed up to 8 times a day and a schedule can be set up for data refresh.

import_method

Advantages of Using DirectQuery

Using DirectQuery, you can build data visualizations on large datasets, which is not feasible to import in Power BI desktop.

  • DirectQuery does not apply any 1GB dataset limit.
  • With the use of DirectQuery, the report always shows current data.
  • Azure

Using the Azure option, you can connect to the database in the Azure cloud. Following screenshot shows the various options available under the Azure category.

MS_Azure

Power BI and Dynamics

Power BI for Office 365 cloud service works with Microsoft Dynamics 365 to provide a self-service analytics solution. Power BI automatically refreshes the Microsoft Dynamics 365 data displayed. With Power BI Desktop or Microsoft Office Excel Power Query for authoring reports and Power BI for sharing dashboards and refreshing data from Microsoft Dynamics 365, sales, marketing, and service personnel in your organization have a powerful new way to work with Dynamics 365 data.

How to use Power BI with Dynamics 365

The simplest way to start using Power BI is to use Microsoft Dynamics content packs. A content pack is a collection of pre-configured, ready-made visuals and reports based on a specific data source. The content packs for Microsoft Dynamics allow you to quickly gain access to data through Power BI and start interacting with it.

How to Embed Power BI visualizations on Personal Dashboards

Before you embed Power BI visualizations on personal dashboards, ensure that the organization-wide setting is enabled.

Enable Power BI visualizations in the organization

Follow the steps below to Enable Power BI visualizations in the organization:

  • Sign-in to Microsoft Dynamics 365 as a user with the system administrator security role.
  • Go to Settings > Administration > System Settings.
  • On the Reporting tab in the Allow Power BI visualization embedding option, select Yes to enable or No to disable.
  • Click OK.

How to Use Power BI Desktop to connect directly to Microsoft Dynamics 365

You can connect to Microsoft Dynamics 365 (online) with Power BI Desktop to create custom Dynamics 365 reports and dashboards for use with the Power BI service.

Requirements:

  • Power BI service registration
  • Power BI Desktop.
  • Microsoft Dynamics 365 (online) instance

Steps for Connecting to Dynamics 365:

  • Start Power BI Desktop.
  • From the Home tab, click Get Data, and then click More.
  • In the Get Data list, select Dynamics 365 Online.
  • Enter the Dynamics 365 (online) OData endpoint URL. It should look like this URL, where OrganizationName is the name of your Dynamics 365 (online) organization, and v8.1 is the version.
  • Click OK.
  • In the Access, an OData feed dialog click Organizational account and then click Connect.
  • The organization database entity tables appear in the Power BI Desktop Navigator window. You can select both default and custom entities. For more information about creating reports with Power BI Desktop, see Power BI Support: Report View in Power BI Desktop.

Now that you know a lot about Power BI Data Sources and how to manage them, how it gets data from Excel Data Source, and how it works with Microsoft Dynamics 365, you may enroll for an advanced degree course in Power BI or Excel. It would hive your data analytics career, the right boost.

We offer one of the best-known Data Analytics Certification Training with Excel and Power BI. The course enables you to learn tools such as Advanced Excel, PowerBI, and SQL.  The live projects and intensive training programme also empower you to come up with solutions for real-life problems.

A self-starter technical communicator, capable of working in an entrepreneurial environment producing all kinds of technical content including system manuals, product release notes, product user guides, tutorials, software installation guides, technical proposals, and white papers. Plus, an avid blogger and Social Media Marketing Enthusiast.

  • Data-Analytics

  • Your Comment

    Your email address will not be published.