This blog post is around the situation where you have SSRS setup to use HTTPS and thus using a certificate and the certificate expires (or just needs replacing). We had caught the initial error via our Continuous Monitoring of the SSRS site — basically when the certificate expired we got an exception and alerted on it.
The client installed a new certificate but the issue arose where in Reporting Service Configuration Manager we went to use the new certificate but when we chose it we got this error:
“We are unable to create the certificate binding”
Read on to see how to get past this.
So, what have we learned? Well, for one, this is a crappy situation born of poor in-product support. I should be able to configure Solution-level shared Data Sources, use them in as many Projects (within said Solution) as I want, and have VS configuration management support them; bonus points for doing so with saved & encrypted credentials. Ideally, when we check this into source-control, we’d check in the “DEV” environment flavor connection-configs. Then, when the reports get deployed to the “PROD” SSRS server, the same globally shared Data Sources are already present (and they don’t get over-written, thankfully by default!), configured by the DBA with prod credentials, and nobody in the development pipeline needs to know said credentials. Yay?
Not exactly a ringing endorsement.
Wolfgang Strasser covers Power BI Report Server’s scheduled data refresh ability in a two-part series. First, he shows how to configure scheduled data refresh:
What needs to be added here is the fact that data refresh can be speeded up to 1 minute refresh intervals. (Which is much more often compared to 8 times for powerbi.com PRO users).
When you are done with the schedule configuration, save it and the definition dialog is updated with the plan information. The last data refresh timestamp plus the last status is also displayed.
Just like Reporting Services, data refresh happens via SQL Agent job. Wolfgang then shows us what’s in the Power BI Report Server’s metadata database:
CAUTION: The PBIRS meta-database is a system database needed for successfully running PBIRS. Do NOT modify objects in that database – a non-functioning PBIRS could be the result!
The table dbo.SubscriptionHistory contains the history of data refresh actions. A SELECT on this table provides you with the following information:
Subscription metadata (SubscriptionID plus Type)
Execution runtime information (HistoryID, StartTime, EndTime, Status plus Message)
I’m pretty jazzed about this feature.
This post shows how you can generate optimized multi-value DAX parameters in SSRS and achieve greater performance compared to the DAX PathContains function. This will be a short post that provides the SSRS expression to convert multiple SSRS parameters into a double-pipe delimited string for use in a DAX query. In other words, the goal is to use the DAX OR operator (||) instead of the PathContains function. I’m assuming the reader has experience with SSRS, so not all steps will be shown.
Read on for the example, which ended up being a 16X performance improvement.
And the online documentation for the API is here:
Interestingly, the new API seems to be OData compliant – which means you can browse it in Power BI/Get&Transform/Power Query and build your own reports from it. For example in Power BI Desktop I can browse the API of the SSRS instance installed on my local machine by entering the following URL:
This is something that SSRS has been missing for a long time. I’m glad they’re introducing a real API.
In addition to managing to versions of Power BI Desktop, I also found myself mentally managing two sets of features. I was constantly asking myself “Can I do that in Power BI Report Server?”. Some of that is because PBI Desktop for Report Server is on a quarterly release cycle rather than monthly, so I had to remember if a feature I wanted to use was new (or in preview) and therefore not available in this version. The other part is trying to remember what you can and cannot do with a Live Connection. For example, you can make report measures, but you can’t use ad hoc grouping and binning.
We had several scenarios where users wanted to be able to group fields in multiple ways that changed somewhat frequently. Since we couldn’t use grouping and binning in Power BI Desktop to accomplish this, we set up an Excel data source in the SSAS Tabular model, and allowed users to change the groups there and refresh the Tabular model when finished. This could get rather unwieldy if you had lots of users who needed this kind of flexibility.
Ultimately, the customer considered it a success. Read on for more details.
Power BI Report Server (PBIRS) was first introduced in May 2017. Based on SQL Server Reporting Services (SSRS), it brings the ability to work with Power BI reports completely on premises in addition to all the other capabilities of SSRS. Given this, it would be reasonable to conclude that PBIRS was the next version of, or a replacement for SSRS, but that is not the case. I have heard people state that SSRS is “going away”, but this is simply not the case. SSRS is still a core part of the Microsoft BI stack. So, what are the differences between the two platforms? The differences boil down to features, licensing, and update cadence.
If you’re in the BI/report writing space, you will want to read the whole thing.
This is the story of two products – or rather one product that is now a service and another product that is now a component of another product. A few years ago, Microsoft began to formulate a mobile usability story among many fragmented tools. They had a really good reporting product: SSRS, and they had a pretty good self-service BI capability offered as a bunch of Excel add-ins; namely: Power Pivot, Power Query and Power View – but it didn’t do mobile. They bought Datazen which was a decent mobile reporting and dashboard tool, designed primarily for IT developers and semi-tech-savvy business pros to quickly create mobile dashboards using traditional data sources. Datazen wasn’t really a self-service BI tool and wasn’t really designed to work with BI data in the true sense. It was a good power user report tool but was young and needed to be refined and matured as a product. Datazen became “Reporting Services Mobile Reports” and was integrated into the SSRS platform as a separate reporting experience with a separate design tool, optimized exclusively for use on mobile devices using platform-specific mobile phone and tablet apps. Since initial roll-out, product development stalled and has not changed at all since it was released with SQL Server 2016 Enterprise Edition.
Paul gives us his current advice, as well as a hint at where things could be going.
Power BI Report Server August 2017 Preview is now available. Think of this a “v.Next” of Power BI Report Server [On-Premises], for all Data Sources.
Here’s an excerpt from the Power BI blog post from this weekend:
“With this August 2017 preview, users can create Power BI reports in Power BI Desktop that connect to any data source, and publish their reports to Power BI Report Server. There’s no special configuration required to enable this functionality“
Read on for more information and a link to download the latest preview. It had me as soon as I read “all data sources.”
If we take a look to the “New Subscription” form, we will discover about a dozen of fields that need to be configured. Doing this by hand can make you want to pull your hairs, also the probability of error is huge, even with copy & paste.
Who wants to do copy & paste of dozens of fields between reports? I know who doesn’t – me 🙂
Click through to learn more about Claudio’s cmdlets for getting, setting, and removing Reporting Services subscriptions.