Watch the video demonstration of adding Excel Dataset in Power BI. If you have signed-up for PowerBi, here is the link to access the app https://app.powerbi.com/
Power BI supports varied types of Data sources which can be defined as Dataset. We can connect to or import datasets from a variety of sources: Power BI Preview provides “out of the box” connectivity to a number of popular SaaS applications. Below is the list of supported Data Sources.
- Excel Data – Power Pivot Model/ Excel Worksheets data
- Excel Data stored in One Drive for Business
- Power BI Designer File
- SQL Server Analysis Service Tabular data
- Microsoft Dynamic CRM
- Odata Feed
Admin center allows administrators to register on-premise data source with the portal. Here is the link to access Admin Center: https://admin.powerbi.com/ . Admin Center has following tabs: system health, data sources, gateway, role management, settings etc. that allows various settings regarding data source, security and health.
Using Gateway Tab we can:-
- Enable OData feed: Expose data from an on-premise data source that can be consumed by user in Power Query
- Enable Cloud Access: In this scenario, an information worker creates an Excel workbook with a Power Pivot model that uses an on-premises data source of SQL Server or Oracle, tests it, and posts it to the SharePoint Online to share it with others. When the information worker tries to refresh data in the workbook on SharePoint Online, it fails to connect to the on-premises data source. The information worker then contacts an IT Administrator with a link to/copy of the Excel workbook (or) the connection string for the data source. The administrator extracts the connection string from the Excel workbook if needed, uses the connection string to register the on-premises data source with the Admin Center, enable cloud access for the data source (CRM Online Endpoint), and grant access to appropriate users so that they can refresh the Excel workbook with the latest data from on-premises data sources.
Data Management Gateway
The Data Management Gateway is a client agent that provides access to on-premises data sources in your organization. You must have at least one gateway installed in your corporate environment and register it with the Power BI for Office 365 Admin Center portal before creating data sources in the portal. The Power BI Admin Center lets you create multiple gateways, and download and install gateways on multiple on-premises computers.
In Power BI, there are two ways you can connect to SQL Server Analysis Services tabular model data. In Get data, you can connect to a SQL Server Analysis Services server, or you can connect to an Excel workbook that already connects to an Analysis Services server. If your Excel workbook has reports based on your tabular data, you’ll be able to see and edit them right in Power BI. In order to connect to a tabular model, a Power BI Analysis Services Connector must be installed and configured by an administrator. When you use the SSAS connector, your data remains on-premises. The reports you create based on that data are saved in the cloud.
Power BI Designer
Power BI Designer also in Preview mode, is a new companion of Power BI, it’s a standalone Windows Desktop application that can be downloaded from the Power BI site. This application combines Power Query, Power Pivot Data Model and Power View into a seamless experience that will allow customers to build their Power BI elements in an offline fashion and then upload to the Power BI Service. The Add-ins for Excel 2013 (Power Pivot, Power View, and Power Query) are still available and customers can continue to use that to model their data and build reports. The Power BI Designer will be another option and allow customers with an older version of Office to be able to create reports. Basically it removes the requirement of Excel 2013 Professional Edition (Save cost and don’t worry about upgrade if not using Excel 2013). More about Power BI Designer later on.
Power! Power! Power!
Seems like Microsoft loves that word “Power” but at times its confusing too. Unless you start using it you will stay confused. All Power to you.