A Fine Slice Of SQL Server
Soheil Bakhshi has (sometimes) three header rows for the price of one:
Easy!
Hmm… Not really. Especially when we have multiple other Excel files; some have two, some have three rows of comments, and some have even more. So not only is the task time-consuming, but it also is pretty boring and prone to human errors. What if there is a custom function that I can invoke to get the job done?
Click through for one method, followed by the proper method.
Comments closedGilbert Quevauvilliers wants you to back that thing up:
Continuing with my series of using Power BI Premium Per User (PPU), today I am going to show you how to back up your PPU database.
As far as I am aware all the options below will work for Power BI Premium as well.
To me this is critical when my dataset size grows. Especially when it takes multiple days to process all the data into the required partitions.
Not only is having a backup best practice, if something must go wrong with a deployment (let’s say I wipe out the partitions by mistake) it will be quick and easy to restore from a backup.
Read the whole thing.
Comments closedPrathy Kamasani wants to track slicer counts:
In one of the projects, I was working on, I received feedback saying it is hard to understand how many items they have selected in a slicer, and it is not the first time I came across this. It is a valid point, especially when you have quite a few items in a slicer, you use a search bar to look for items, you select a couple, but you were not sure how many were selected.
Read on for a rather clever solution to the problem.
Comments closedChris Webb troubleshoots a problem:
If you’re working in Power BI Desktop you may sometimes find that your visuals error with the message “Couldn’t load the data for this visual. The operation was cancelled because of locking conflicts”:
Read on to see what causes this problem and what you can do to avoid it.
Comments closedMarc Lelijveld wants to refresh a single table:
Back in February, I wrote a blog on how you can trigger a single table to refresh in your Power BI data model. This blog described how you can achieve this goal using a PowerShell script and the ASCmd cmdlets for Analysis Services, which also works for Power BI Premium. In the wrap-up of that blog, I promised to follow-up with a blog on how to achieve the same goal with Azure Data Factory. It took a little bit longer than expected to finalize this post, but here it is!
In this blog, co-authored by my colleague Paulien van Eijk, we will describe how you can automate your single table refresh in the Power BI Service, including all dependencies with downstream dataflows using Azure Data Factory and Azure Automation. All this is based on real life scenarios and a solution build in collaboration between Dave Ruijter, Paulien and me.
Read on for Marc and Paulien’s solution.
Comments closedEd Hansberry works around a Power BI limitation:
It is easy to add an animated GIF to your Power BI Reports. However, if you just add it as an image, it won’t animate. You’ll just get a static image.
Animated GIFs can be useful in reports to explain new features to users, or on hidden developer pages showing how certain things were done.
Click through for instructions on how to include an animated GIF on your Power BI report. Just make sure to pronounce it the right way.
Comments closedMarco Russo and Alberto Ferrari don’t have time to wait:
How many times have you heard an executive request a panel with the company’s sales data in real time? How frequently has this single request – which is more often a preference than an important business requirement – affected the overall architecture of your analytical solution?
In the Power BI world, requirements for real time often drive the creation of a pure DirectQuery model, with no aggregations to avoid data latency. This choice is incredibly expensive: the computational cost of each individual query is borne by the data source, which is often a relational database like SQL Server. On top of its cost, with this approach you will face scalability, performance, and modeling issues. Indeed, the relational database on top of which DirectQuery runs is mostly designed for transactional processing instead of being optimized for the workload of analytical processes. Optimizing the model is both difficult and expensive. Finally, using DirectQuery creates specific modeling constraints and the need for modeling workarounds to obtain good performance.
Creating an entire model using DirectQuery for the sole purpose of achieving a few real-time dashboards is definitely excessive. The primary scenario where relying on DirectQuery makes sense is when it is not feasible to import data quickly enough to satisfy the latency requirements for the majority of the reports. When the entire model can be in import mode, and a small number of dashboards require DirectQuery, there are better options available.
Definitely worth the read.
Comments closedEric Svensen has a quick one for us today:
When you build a date table in Power Query you might use the functions under Date to add year, month and day
And this will give you three steps in your Query
But we can do this a bit faster, and you will save a few clicks with your mouse
Click through to see how.
Comments closedNithin Sankar tracks deviations with cumulative sums:
With the advent of Internet of Things (IOT) and the proliferation of connected devices, comes the challenge of monitoring parts for maintenance before they break down. A common approach revolves around getting data from connected devices and performing a statistical test to determine the likelihood of the device failing. While this common approach is robust, it typically involves a significant time investment in exploratory data analysis, feature engineering, training, and testing to build a predictive model. It, therefore, often lacks the agility required to keep up with the monitoring demands of increasingly time-sensitive initiatives.
In this context, the question becomes: how can we ensure a similar degree of rigor, but also improve the timeliness and responsiveness of being able to perform predictive maintenance?
Click through for the process, as well as an example using Azure Stream Analytics and Power BI.
Comments closedGilbert Quevauvilliers diagnoses an error:
I have been doing a lot of work recently using Power BI Premium Per User and recently I got the following error below when trying to update my fact table in Power Query.
“An unexpected error occurred (file ‘tmsavepoint.cpp’, line 1503, function ‘TMSavePoint::GetProxyImpl’)”
Read on to understand what this error means and how you can fix it. Do be sure to look out for the very important warning about 2/3 of the way in.
Comments closed