Power BI Shared Datasets: What is it?

Power BI Shared Datasets: What is it? How does it work? Why should I care?

Nowadays, if you are in the world of Power BI you will have heard a lot about certified datasets, and also shared datasets becoming available across multiple workspaces. In this article you will learn about:

  • What is a shared dataset in Power BI?
  • How the shared dataset can help in Power BI development?
  • Where is the place of shared datasets in the Power BI architecture?
  • How shared datasets work behind the scene in the Power BI service?
  • What are certified and promoted datasets?

What is a shared dataset in Power BI?

When you create a Power BI report (or let’s call it a *.PBIX file), the report has two components (if the data connection mode is import data); a report and a dataset. When you are in the environment of Power BI Desktop, you can’t see the separation that easily, unless you go to the task manager and see the dataset running behind the scene under the Power BI Desktop task threads.

However, when you publish the PBIX file into the service (the Power BI website), you can easily see there are two objects.

  • The report is the visualization layer of your Power BI implementation
  • The dataset includes the data, tables, relationships, calculations, and connection to the data source.

You can schedule to refresh for the dataset. and connect to on-premises sources (through a gateway), or cloud-based sources.

What is a shared dataset?

Now that you know about the dataset, let’s talk about the shared dataset. A shared dataset is a dataset shared between multiple reports. For a long time, you could create a new report from an existing dataset through the Power Bi website. This feature has been available from the early days of Power BI.

About April 2017, the ability to create a report from Power BI Desktop that can point to an existing dataset, and has a live connection to an existing dataset became available.

Those days it was called; Get Data from Power BI Service. Nowadays, this feature is renamed as; Getting Data from Power BI Dataset.

shared datasets

A shared dataset is a dataset that is shared between multiple reports. Multiple reports connecting to one shared dataset. When that dataset gets refreshed, all of those reports will have new data. A shared dataset is one step closer to the multi-developer tenant in the Power BI environment.

shared datasets

Sharing Datasets Across Multiple Workspaces

For a long time, sharing datasets was only possible inside a workspace. You could not use a dataset from workspace 1 as the resource for a report in workspace 2. However, recently, the feature became available and you can share the dataset across multiple workspaces. This is an amazing update and changes the way that Power BI development works in the future.

When you get data from a Power BI dataset through the Power BI Desktop, you have the option to select which dataset you want to get data from.

shared datasets

How does shared dataset work behind the scenes?

When you share a dataset in the same workspace, everything is clear. You have one dataset to schedule refresh, and multiple reports connected to it. However, when you use a dataset shared from another workspace, you get something that might look a bit different.

Linked Dataset

When you get data from a Power BI dataset that appears in workspace 1, and then save your report in workspace 2, you might get something like a copy of your dataset in workspace 2.  you might say this is not a shared dataset, it is a copied dataset. The fact is that what you see is just a link. Power BI will bring a link to that dataset into the new workspace, this link helps you to understand when the dataset gets refreshed.

Here is what a linked dataset looks like and you can see the difference of that of a normal dataset.

shared datasets

You cannot manually refresh or refresh based on the schedule of a linked dataset. The refresh action can only be configured in the main dataset. The linked dataset is just a link, showing you when was the last date and time of the last refresh, and an easier way to generate more reports from that dataset.

Certified and Promoted Datasets

When Power BI developers use the function of Getting data from the Power BI dataset, they see all datasets from all workspaces that they have access to. This might be a bit confusing. There might be tons of datasets shared in the environment. Developers end up with a question of: Which of these can I use? Which of these are valid to use? Which of these are reconciled and tested? etc.

A new labeling system is added to the Power BI datasets which helps in this scenario. You can mark some of the datasets as certified or promoted. To get a dataset certified, there is an approval process that can assure the dataset passed some of the tests. You can clarify through this labeling system, like what datasets can be used as the source and which can’t. You can build the concept of gold, silver, and bronze datasets. Having gold datasets that are fully tested and reconciled and then down to other levels where the bronze datasets that haven’t been tested yet.

To use this labeling system, the creator of the dataset can go to the settings of the dataset.

shared datasets

In the settings you can set the Endorsement levels as below:

shared datasets

As you can see the Certified option might not be available. The Power BI tenant administrator has the authority to enable that labeling and give access to who needed it in the tenant settings.

The labeling system helps Power BI developers to then see what is the level of certification that a dataset has to be used as a shared dataset, and then they can select based on that respectively.

shared datasets

Shared Datasets in the Power BI architecture

Previously I have written about how Dataflow and shared datasets can play an important role in the multi-developer tenant of Power BI implementation. In a nutshell, using the dataflow ensures you can bring the data well prepared in a central area, which you can call a centralized data warehouse in the Azure Data Lake. Using the shared datasets, you can build data marts that can be used by multiple reports. Here is how the architecture works in a diagram view:

shared datasets

Instead of having silos of Power BI reports and files everywhere, you can build an architecture that works best with multiple developers, less redundancy in the data, in the code, in the logic, and easier to maintain.

Summary

The shared dataset is not a new feature in Power BI, but the ability to share it between multiple workspaces announced recently is a game-changer in the architecture of Power BI implementation. Using shared datasets, you can have centralized data models (data marts) that can serve multiple reports. You can reduce the maintenance time, the redundancy of the code, and the data through this approach. Having the labeling system of the certified or promoted dataset is also a great way of putting some processes and governance in place to make sure the shared datasets have been through testing and reconciling.

www.allonline365.com 

Resource Credit | RADACAD

Power BI: How to get started with data visualization

Microsoft Power BI: A cheat sheet for business professionals

The big data revolution continues in earnest and enterprises continue to generate more and more data about every business interaction they can track. Couple this with the volume of data now being generated by devices and sensors connected via the Internet of Things and you begin to appreciate the universal need for better data visualization.

All of this generated data must be transformed into actionable information that decision-makers can use. Data visualizations in the form of dashboards and reports must be developed and shared throughout the enterprise, crossing departmental and sometimes even international lines.

Microsoft’s Power BI suite of data visualization tools is designed to accomplish the transformation of data into useful information. Let’s take a look at what you need to know about Power BI.

Executive summary

  • what it is: Power BI is a set of visualization tools that allow users to transform collected data into actionable information for decision-makers.
  • Why it matters: In a modern enterprise, the ability to transform data into something useful is paramount for achieving overall success.
  • Who it affects: Power BI is a vital tool for any enterprise employee trying to communicate information to management, stakeholders, or other decision-makers.

What it is

Power BI is Microsoft’s suite of data visualization tools. The suite allows users to transform data found within, and even outside of the enterprise. Power BI can tap into numerous data sources, including SQL Server, Excel, SalesForce, and Google Analytics.

Why it matters

The ability to transform the huge amount of data generated by business enterprises into some form of actionable information that decision-makers can use on a regular basis is critical to the success of the enterprise. Power BI provides tools for transforming raw data into information that be presented in dashboards, documents, and reports.

Who it affects

Anyone in an enterprise who needs to communicate information to their department, managers, vendors, or to the enterprise, in general, can use Power BI’s data visualization tools to make it happen. Power BI has a number of templates to get even the most technology-challenged user started, and Microsoft provides many free educational training videos that can help turn novice users into experts.

How to get it

Power BI is available in several ways. The most obvious is as a free web service users can access with a standard web browser. The free service is limited to cloud data sources and simple dashboard sharing, and it’s restricted to individual use. The free service is limited to 1GB of capacity per day.

The Power BI Pro service is subscription-based. The Pro version allows users to access all supported data sources and is designed for business use. This version is limited to 10GB of capacity per day.

A desktop version of Power BI is also available for download. And enterprises with large mobile workforces can use the mobile version of Power BI, which is available on Windows 10, Apple iOS, and Android platforms.

For Office 365 subscribers, Power BI is available at no additional cost as part of the productivity suite. Users with the appropriate Office 365 subscriptions and administrative credentials can also download a desktop or mobile version of Power BI to use at their workstations or on their mobile devices. Power BI and its data sources are administered at the enterprise level, so you may need to ask your IT department for access.

How to configure an Excel file for use in Power BI

One of the simplest ways to take advantage of Power BI’s data visualization abilities is to link it with an Excel workbook – it’s the way most business users will employ the Power BI tool. But there are some key tips to keep in mind before you attempt to link your Excel workbook. This quick tutorial will show you how to prepare your workbook for Power BI.

Flatten your data

For Excel power users, this may come as a bit of a shock, but workbooks destined to be linked to Power BI for its data visualization tools must be restricted to flat data. That means no matrix views and especially no pivot tables.

Take a look at the example Excel workbook shown in Figure A. It is a list of stocks and some common data points typically associated with stocks: shares, prices, gain/loss, etc. Notice that there are no summed columns or rows, just column headings.

In general, an Excel workbook like this one would have conditional formatting, summed columns, and so on. If you have a workbook you want to link to Power BI with those kinds of analysis already embedded, you will have to deconstruct it until you get down to a pristine list of flat data.

The next step is to convert your flat data into the standard Excel table format. The easiest way to do this is by highlighting the data in your workbook and pressing Ctrl+T. Or if you prefer, you can click the Format As Table icon in the Ribbon of the Home Tab. Make sure the box that indicates your data has column headers is checked. This procedure will allow you to convert your flat data into a standard Excel table as shown in Figure B.

This last step is optional but it is recommended as a way to reduce confusion in the future. You should name your table something descriptive that you will recognize and understand later. Highlight the cells of the table, click the table name box and give it a new descriptive name.

Now your Excel workbook is ready to be linked to Power BI. If you do not prepare your workbook this way Power BI will just ignore it, so this process can’t be avoided.

How to create your first Power BI dashboard

With a few clicks Power BI can transform your raw data into a visual dashboard that informs the reader and reveals information that otherwise might not have been shrouded by the white noise of enterprise data overload.

Get data

The first thing you need to do when creating a dashboard is to access the data. For this example, we will get our data from an Excel workbook. Load up Power BI inside Office 365 and then click the Get Data button located at the bottom of the left navigation pane, as shown below.

One of the choices will be Files. Click that button and proceed to the location where your Excel file is stored. Find your file, point to it, and click the Connect button, shown in Figure B.

It will take a few minutes to process, depending on the size of the workbook. Once it’s finished, you will be presented with a blank Power BI workspace. On the left navigation pane, click the name of the dataset you just imported. The blank workspace will then change into an active dashboard creation tool. It should look something like Figure C.

Create your dashboard

Similar to a paint program, Power BI has icons that represent the various data visualization tools you can use for your dashboard. It also displays a Fields section, where you can choose what data you want to work with at any given point.

The example is a portfolio of stocks, so one useful visualization might be to show the market value of each stock in relation to the whole portfolio – and the most common way to do that is with a pie chart. Click the checkbox for the stock name and the market value and then click the pie chart icon. Power BI will automatically create a pie chart, similar to the one shown in Figure D.

Another important visualization for our stock portfolio would be to see where we have a gain versus where we have a loss. A bar chart would work for this. Click the checkbox for stock name and gain/loss and then click the bar chart icon to create a visualization of gains and losses (Figure E).

Click the save icon in the toolbar to reserve this dashboard report for later viewing. By tying the dataset to an Excel workbook, our dashboard will change when the data changes in the workbook.

How to harness OneDrive to keep your Power BI dashboard fresh

One of the primary benefits of working in a cloud-based environment is the ability to access data with any device and any application at any time. Connecting to data stored in the cloud means that data can always be up to date when you access it, so as long as there is an internet connection, you will always be up to date.

In Power BI you can create dashboards and reports that will always contain the latest data by connecting to a file stored on OneDrive. When you or your team update a file on OneDrive, the changes are disseminated to everyone with access to that cloud storage. Changes on OneDrive can also be communicated to apps like Power BI, saving time and increasing overall productivity. Connecting to data on OneDrive with Power Bi is similar to connecting to any other file, but there are some additional procedures to keep in mind.

OneDrive

To connect an Excel file located on OneDrive, follow the steps we showed you above. Only this time, choose a different location. In Figure A, I can choose from Local Files, OneDrive (Personal), OneDrive for Business, and a SharePoint Team Site.

For the purpose of keeping the data connected to Power BI up to date, the two OneDrives and the SharePoint location would all work. In this example, we connected an Excel workbook on the OneDrive for Business server. Be sure to choose the Import connection.

Using the sample data of a simple report of salespeople and their respective sales of products and geographical areas (Figure B), I created the simple Power BI report shown in Figure C.

Because the data connection was made to a file on OneDrive, any changes made to that Excel workbook will automatically flow to the Power BI report. Note: The default refresh rate for Power BI is one hour. To see more immediate results, you will likely have to refresh the data manually by clicking the Refresh button on the Ribbon of your report. 

Dynamic data

Just by connecting to data stored on OneDrive or SharePoint, users can keep their Power BI reports and dashboards continuously updated automatically. This not only saves time, but it also eliminates the need to remember to update reports that need to be disseminated to a team, department, or even an entire company. You can’t get much more productive or efficient than that.

How to share your Power BI dashboards and reports

The advantage of sharing Power BI dashboards versus mass distribution is that you can target and tailor each report for a specific individual, group, team, department, or entire enterprise if necessary. Once you have created your Power BI dashboard, sharing is not difficult but there are some steps involved and a few caveats to consider.

Learning to share

The key thing to remember about Power BI sharing is that it is domain-based. In other words, the Power BI dashboard is created under the allonline365.com domain, it can be shared only with other email addresses in that domain. It is important that the enterprise IT department and Office 365 administrators understand this limitation and plan accordingly.

To share a dashboard, first open Power BI. In this example,  I am using Office 365 version. Next, navigate to the dashboard you want to share. Right-click the dashboard name in the navigation or click the Share button on the tab bar in the upper-right corner. Either method will take you to a screen where you can list email addresses of the people you want to share this dashboard within your enterprise. It should look something like Figure A.

As you can see, you can send a message with each invitation, which will be a good place to explain what you are sharing and why. Also, note the two checkboxes located near the bottom of the screen. The first lets you grant permission to recipients to re-share your dashboard with others. The second will send an email to each invitee with a link to your shared dashboard.

Any invitation recipient not already registered with a Power BI account will have to register before they can accept and view the dashboard. Once your Power BI dashboard is shared, you will have access to a Shared Dashboard Admin screen, where you can see which users have accepted your invitation and which have not. This admin screen will allow you to revoke a user’s shared status when needed.

On target

Because you can create separate dashboards using the same dataset, you can tailor each report to specific users and share only the information each user needs to see. This can greatly decrease the amount of data clutter users must wade through on a regular basis.

How to download and install Power BI Desktop

The application can be accessed as a web service for free, but it is most often acquired as part of the Office 365 productivity suite. While the online version does a fine job, there are occasions when users want to work on their data visualizations offline on their desktop. This requires them to download the Power BI Desktop tool.

Installation procedure

There are two ways to start the download process: Go to the Power BI Desktop webpage and click the download button or click the download button located under the down-arrow tab in the online Office 365 version of Power BI. Either way, you will download a .msi file to your PC. Run this file once the download is complete.

After accepting the license agreement, you will be able to choose where you want to install the application. The installation process will take about a minute.

Start the app

Note that the first time you start the application it will go through an initialization process. When that is finished, you will be asked to sign in to your Office 365 account. To share reports and dashboards you must be logged in.data visualizations

After logging in, you will be presented a Power BI workspace similar to the online version (Figure B). From here you can import data and create reports and dashboards, then share them with your colleagues.

data visualizations

How to change default visualization formats in Power BI

Using the default settings for each graphical type is fine as a starting point for reports. But for the final report, you are going to want to add personal touches, highlight specific data points, and maybe even add some eye-catching flair to the project. You have access to numerous settings, configurations, and adjustments inside the Power BI palette.

Format and configuration

The default format settings for most users are not going to properly communicate what you want to communicate. Click the report you want to format, which will change the visualization palette dynamically to match your focus. To change the formatting of a report or a particular data point, click the paint roller icon in the visualization palette as shown in Figure A.

data visualizations

Depending on what you have clicked on the report, the Format section will present you with various configuration options. To change the color from the default shown in our example, just click the paint roller and then the Data Colors item. You can change all the colors together or, if you click the Show All slider, you can change each data point individually. If you want to highlight the Facebook data point, for example, because it is your greatest gain, you can change the color to something more striking, as shown in Figure B.

data visualizations

If you can’t find the color you want, just click the Custom Color item and choose your own color from the available spectrum.

It’s your choice

Scroll down the Format list further to make changes to titles and data labels, add a legend, or even add a background if you wish. The choices you make at this point will determine how well your report disseminates what is important to whom.

How to add custom visuals to your Power BI reports and dashboards

Disseminating information derived from business intelligence data doesn’t have to be a dry and uninspired exercise. In some cases, effective communication is going to take some unconventional outside-the-box thinking.

Power BI developers looking for more interesting and compelling ways to communicate their message may find what they seek in the Custom Visuals section of the Microsoft Office Store. Finding, downloading, and inserting custom Power BI visuals is not difficult.

Microsoft AppSource

The first step is to point your web browser to the Microsoft Apps page and then click the Power BI Visuals item in the left navigation menu. As you’ll see, the list of vetted and approved Power BI custom visuals is extensive and varied. See Figure A.

data visualizations

Thoughtfully scan through the list of custom visuals to find the one, or perhaps two, you feel will be most helpful for the dataset in question. Keep in mind, these custom visualizations were created by the Power BI community and new entries will populate the list from time to time.

For this example, we will download the colorful World Cloud custom visualization. Click on the link to the app description and then click the Add button to start the download. Be sure to save it in a folder you can reach with the Power BI application. To use the custom visual, click the ellipses in the Power BI Data Visualization Tools palette, as shown in Figure B, and then click Import A Custom Visual in the list.

data visualizations

Navigate to the location of your custom visual and open the file. You should get a dialog box confirming that your visualization was successfully imported. A new icon should appear in your Data Visualization Tools palette, as shown in Figure C.

data visualizations

Click the new icon and then enter the fields you want to include in your new visualization. Each Power BI custom visual will require a specific set of data points to prove effective. Figure D uses the World Cloud visual to show the relationship of each stock’s gain to the rest of the stocks in the portfolio. As you can see, Facebook is dominant in this example.

data visualizations

Expand your toolset

Once you download a custom visual you can insert it into other projects. Depending on the data involved and the message you are trying to convey, there may be a Power BI custom visual that can make the difference between highly effective communication and not-so-effective communication. The community-driven custom visuals found in the Microsoft AppSource are great tools you can’t afford to ignore.

www.allonline365.com 

Resource Credit | TechRepublic

Power BI and Azure as the future of enterprise analytics

 

Combine Power BI and the various Azure data processing services and you get the next generation of business intelligence and analytics

It’s not surprising that many of Microsoft’s own services are built on Azure, but increasingly Microsoft is also offering Azure services as a way for customers to extend and customize products.

When you use dataflows to extract, clean and transform data that you’re loading into Power BI, that data is stored in Azure Data Lake. You could also use it in Azure Databricks or for analytics through Azure SQL Data Warehouse, which you can do through the Azure portal, or make interactive using the Power BI Desktop app.

The automated machine learning in Power BI is the AutoML feature from Azure Machine Learning, which looks at what you’re trying to predict and what data you have available, and iterates through multiple machine-learning algorithms to discover which gets the best score. Or you can take advantage of Azure Cognitive Services to analyze the data in images and text, or build your own machine-learning models and run them.

Power BI also now has built-in-AI-powered visualizations like Key Influencers, which runs different statistical analyses like logistical regression or classification on the data to extract the key factor associated with a particular outcome. You drag the factors you think are important into the visualizations and Power BI ranks them. As you add more factors that you think might be relevant or drill into a specific segment, it keeps re-running the model to see if more information reveals anything new.

So if you are analyzing which visitors come back to your hotel and stay again, the Key Influencer might be which country they’re from. But if you select visitors in a certain age group the model runs on just that slice of data, where they Key Influencer might be whether they ate in the hotel restaurant or had a spa treatment. If you’re looking at shipping delays, you can add factors like which division sent the delivery, what factory it came from, or what area it was being sent from to see what has the most effect on what arrives on time and what’s delivered late.

There are two new AI visualizations. Distribution Change looks for what makes one data distribution different from another. The Decomposition Tree sends multiple queries to the Power BI model and then links them together so you can click on a metric in visualization to see what’s behind it, and then keep clicking down to the different levels of data to understand it in depth. That way, you can see if those 500 sales in one city are driven by a particular group of customers or many different customers who still have something in common.

All of this can feed into the visualizations, dashboards, and natural-language Q&A features that Power BI is known for, as well as the new paginated reports that previously required SQL Server. For example, when you use the automated machine learning the prediction for each row includes details of what contributed to the prediction, so you could include the explanation in a report to clarify where the figures come from and what factors appear to be involved.

azure

Data pros

Power BI has different paths for doing this, depending on whether you’re a data scientist who wants to make their work available to the rest of the business or an analyst who wants to use machine learning but doesn’t have the skills to do it themselves.

Data scientists can add steps to a dataflow to extract information from unstructured data like images or text from tweets or reviews, by extracting keywords, doing sentiment analysis or detecting what’s in a photograph. That’s powered by Cognitive Services, but without the usual steps of writing the code to call the API – you can just add the image and text analytics to the dataflow.

As new Cognitive Services come out, Power BI will add more of these features. The latest services are extracting text from images, handwriting recognition, and entity recognition – not just extracting keywords, but classifying what they refer to. If you’re a hotel owner looking at reviews on the internet, entity recognition can tell you whether ‘cycling’ in a review means a happy guest who stayed when they were on a cycling trip or an unhappy guest complaining about the air conditioning cycling on and off all night.

If you’re creating your own machine-learning models in Azure Machine Learning and publishing them as a web service, you can give Power BI analysts in your organization role-based access to them through the Azure portal, and then they’ll show up as models they can use in the same way as Cognitive Services. If you want to analyze the photos in those hotel reviews, you might need to train a custom image recognition model to understand pictures of the things you find in a hotel. Photos of air conditioners, light bulbs, windows and lifts in a hotel review are probably a bad sign, and the standard image recognition model might not highlight them as being important objects.

And if you’re building your own machine-learning model and use Python and R to integrate that into Power BI, or using the AutoML in Power BI to have it discover what machine-learning algorithm works best with your data, you can now upload those models to Azure Machine Learning to manage them or tune them further. That means business analysts could use the automated option, and if it proves useful a data scientist could pick it up and develop it further.

And all of these insights are available to use in a range of ways. Powerful as the interactive dashboards and visualizations in Power BI are, sometimes what business users want is the familiar report that they can print out and read, or email to a customer or supplier. Power BI now supports the same paginated reports with headers and footers and table, chart or matrix layouts as SQL Server Reporting Services (with a new Report Builder tool to create them). Paginated reports are part of Power BI Premium, but they’re also compatible with the on-premises Power BI Report Server.

So if you want to move your analytics from SQL Server Reporting Services to Power BI, you can create an enterprise business intelligence system that gives you the full range of business analytics, from the reports your organization probably already depends on, to machine learning that tries to automatically find insights in data that isn’t necessarily structured or numerical. If Power BI doesn’t fit your needs on its own, the idea is to make it so easy to extend with Azure that business users can do it themselves.

www.allonline365.com 

Resource Credit | TechRepublic 

Call Now Button