Sometimes you want to connect to a report server instance using Management Studio, for example to create a new security role or modify an existing one. Recently I tried to log into our newly installed Power BI Report Server (March 2018 edition). I was greeted with the following error:
The Reporting Services instance could not be found.
Read on to see how to solve this problem.
What can you query from ADLS?
You can connect to the data stored in Azure Data Lake Store. What you *cannot* connect to currently is the data stored in the Catalog tables/views/stored procedures within Azure Data Lake Analytics (hopefully connectivity to the ADLA Catalog objects from tools other than U-SQL is available soon).
You’re not sending a U-SQL query here. Rather, we’re sending a web API request to an endpoint.
With an ADLS data source, you have to import the data into Power BI Desktop. There is no option for DirectQuery.
In other words, data that you’ve already prepped using U-SQL and want to display to the outside world. Click through for a demonstration as well as additional helpful information.
Implementing a Power BI solution is not just about developing reports, creating a data model, or using visuals. Power BI, like any other technologies, can be used in a correct, or incorrect way. Any technology can be used more effective if it harnesses the right architecture. A right architecture can be achieved after a requirement gathering and designing aspects and components of the technology to fit the requirement. In this post, you will learn about some of the most common architectures to use Power BI. You will learn about using Power BI in different architecture guidelines;
Read on to learn more about these three patterns.
As you can see in the image below, when a slicer is added to the product table and then you select an item in the slicer, the invoice detail table ONLY shows that single selected product. But the requirement it to see ALL the items on ALL the invoices that contain that selected product.
Read on, as the solution definitely isn’t trivial.
This is just great on its own as I get information shown below. It is basically data like this at the Database/FileGroup/File levels
Free of Max size
Size as a percent at the instance level
Free size as a percent at the instance level
Max size as a percent at the instance level
Free of Max size as a percent at the instance level
Read on to see how Jana makes use of this data, as well as where you can get the code.
A common way to review categorical variable relationships is to create a cross tab, also known as a matrix, to evaluate the counts for each resulting combination.
For example, in my current data set, I can create a matrix to compare the number of players in two teams, say the Knights and the Sharks, by position and by handedness.
In descriptive analytics, I’m not trying to prove anything by looking at these values. I’m just reporting them. (Although I do find it interesting that there is a preponderance of lefties in these two teams.)
In the business world, I might do something similar by placing product categories on rows and customer geography (country or state) on columns.
Stacia also gives her explanation of descriptive analytics, so check that out too.
After making changes and testing your report, make sure to clear any slicer values before publishing, if you have row-level security on a field shown in a slicer and you leave values selected. The selected values will be shown to users when they view the report. For example, let’s say you have created a row-level security role that can only see Product A, but you can see everything, and you left Product A and Product B selected and deployed the report. A user who views the report next and is a member of that RLS role will see the two selected values in the slicer, even though they can’t see the data for Product B on the page. This may not be a big deal for an internal report. But now imagine this is for clients. You don’t want clients to see other clients in the list. This behavior is consistent in the Power BI web service and isn’t specific to embedding. It’s just important to remember this.
There are plenty of interesting notes here, so check it out if you’re thinking of a Power BI project.
My approach to teaching people to use Power Query is to always use the UI where possible. I first use the UI to do the hard work, then jump in and make small changes to the code created by the UI to meet any specific variations required. Keep this concept in mind as you read this article.
I am going to use Power BI Desktop as the tool for this, but of course Power Query for Excel will work just as well and the process is identical. In fact the calendar query at the end can easily be cut and pasted between Power BI and Power Query for Excel.
Check it out for another method for building calendar tables. I tend to build them in SQL Server because that’s what I’m most familiar with, but it’s good to know a few different ways of doing this.
Usually, the logical AND condition is the expected behavior for scenarios with different slicers. However, there could be cases where there is a need for a logical OR condition between several slicers. In our example, the semantic would be, “show the quantity sold of products that are of brand Contoso or that have been sold to customers who have a Professional occupation”. The measures OR #1, OR #2, and OR #3 show the same result obtained with three different techniques.
The first two techniques are best practices and might provide different performance depending on the data distribution. The third technique is usually a bad idea because it implements a table filter rather than a multi-column filter. It is included for informational purposes, and as an example of what not to do in production code.
It’s not trivial, but it’s possible.
A substitution variable substitutes a variable (a changing value) to get a different result
a + b = c (where ‘a’ and ‘b’ are substitution variables that define value ‘c’)
- If a = 5 and b = 2 then c = 7
- If a = 25 and b = 9 then c = 34SubVars are especially helpful when
- you need to move Power Query files from one file path, folder and file name to something completely different
- you want to use the same PQ reports for different time periods (ie changing months)
It involves looking for changeable items. If something may or will change in the future, it’s a candidate for a substitution variable.
file paths are good for subVars
so are time elements such as Year or Month
you can also combine subVars where both the file path and month may change
Click through for several good examples.