Power BI Hybrid tables - Freedom to choose what you load

Autor: Miguel Peredo Zurcher

(Update 30.January.2022, Hybrid tables now work with Snowflake) (see here)

As an introduction, let's agree that we want dashboards with response times of less than one second. Nobody enjoys (or uses) dashboards that take two or more seconds to load.

How do you achieve fast-loading dashboards in BI?

BI tools have two access modes: Load Mode and Live or Direct Query Mode. In Load Mode, the data is copied (loaded, imported) from the source to the in-memory layer of the BI tool, which for specific volumes provides a sub-second response time. In Direct Query mode, the response time of the dashboard depends on the response time of the source where the tool is connected, which usually is not a sub-second time. There are other aspects to consider to Load Mode (import) or Direct Query mode (live); check your BI tool documentation.

Most cloud Dataware house-oriented products have an incredible response time, they can process vast amounts of data, and the response time will be just a couple of seconds, but not below one second. 

Because of this, you must first load the data to the BI tool (load mode), which is usually an in-memory layer that will respond in less than one second for most cases. And then, query the data from this layer.

So far, so good; we can load our data to the in-memory layer of the BI tool, and our sub-second response time challenge is solved, but can we really load all our data to the in-memory layer?

The answer is a qualified yes. Loading the data to BI tool in-memory engine had some challenges:

• In-memory is expensive 
• The complexity of doing incremental loads 
• Stale dashboards accentuated by streaming and frequent updates into the source data

These challenges have had workarounds, such as Power BI aggregates, allowing you to load aggregated data into the in-memory capacity. But as you can see, there is still the need to load from the data warehouse to the in-memory layer. 

I must mention that several vendors are working on putting an ultra-fast layer on top of their data warehouse solution, for example: What is BigQuery BI Engine? | Google Cloud, Using the Search Optimization Service — Snowflake Documentation, etc.

Making an extra load impacts the freshness of the data in your dashboard, meaning the data that has been written (maybe streamed) to the data warehouse is not visible on the dashboard until you do the next load.

Power BI has now a great feature (in public preview) that allows you to tackle the challenges mentioned before; here is the official announcement: Announcing Public Preview of Hybrid Tables in Power BI Premium | Microsoft Power BI Blog | Microsoft Power BI

What can you do with Power BI Hybrid Tables?

It allows you to decide which parts of a table (usually a fact table, like sales for the last ten years) to load in the in-memory layer and which parts you keep in the source but still accessible in the dashboards with different response times.

For example, your Sales table could look like this:

In part A, you have the data has been inserted into the source after the last load to B.

In part B, you have sales data from the previous two years, which resides in memory. You load this data in a scheduled way. (For example, every 8 hours).

In part C, you have historical data.

The objective is to make all the data available for the BI dashboards. 

The access modes for different parts of the Sales table are: 

For A - Direct Query Mode. Response time is acceptable when querying from the source since it's a small amount of data. 

For B - Load Mode. The performance of querying from memory will be top of the line. Most dashboard queries will hit only the last two years of data (as agreed with the business). 

For C - Direct Query Mode. For queries that span more than the last two years, the performance will depend on the source. But again, this is what the business requested.

All this is transparent to the report creators; for them is just another table, but behind the scenes, it is a union of in-memory and direct queries, automatically managed by Power BI.  

If that layout does not fit your scenario, you have the freedom to choose what goes to the in-memory (load mode) and what stays in the source. 

Do you want to try it out?

Remember that you need Power BI Premium (can be PPU or Capacity)

And I suggest following the instructions from the blog mentioned above: Announcing Public Preview of Hybrid Tables in Power BI Premium | Microsoft Power BI Blog | Microsoft Power BI

When experimenting, I first tried Snowflake, but it did not work. I suppose because the feature is still in preview. I posted the issue in the Power BI community:

Hybrid Tables, Snowflake and Query Folding - Microsoft Power BI Community

(Update 30.January.2022, Hybrid tables now work with Snowflake) 

Then I tried Synapse, and it worked.

After publishing to the Power BI premium and doing one refresh that created the partitions, I used Tabular Model Editor to see the partitions and check for each partition if those are in Load Mode or Direct Query Mode. The Tabular Model Editor also allowed me to change the settings for the partitions, but as mentioned in the Blog from Power BI, you can also make the changes programmatically. 

Some screenshots:

From IOT trough the cloud into analytics

I still remember the example from several years back telling us what the IOT is going to be about: "Your fridge will be connected to the internet and will order milk and eggs when you are running out". Sounded kind of appealing but, I thing IOT is much more interesting than that.

One nice example of what IOT can achieve is described in this article of Wired magazine.

I myself wanted to experience the topics IOT, Cloud, and analytics combined together, with a budget of 50€, during 5 days, at a rate of 1.5 hours per day.

The result of my experiment is this:

I've got an Onion Omega microcomputer (30 USD) that runs Linux and can be connected to any type of sensor, and connects to a Wi-Fi network. I originally wanted to use a Raspberry PI Zero (5 to 10 USD) but they were in Back Order.

In this case I decided to work with a temperature sensor (5 USD). So, inside the Onion I put a Python program (thanks Maria!) that reads the temperature from the sensor and send it to Microsoft Azure Cloud.

Then I used the newest Microsoft analytics services, called PowerBI to create some charts with the resulting data.

In a Nutshell, any small and cheap micro computer connected to a equally small and cheap sensor, and to a WI-FI network can send its reading to a server in the cloud. Then with any device (PC, Smart phone, tablet) connected to the Internet you can analyse and visualise this information. Great, isn't it?!

Right now I'm in the process of creating a platform which enables anybody to collect and analyse information from sensors (whatever sensor you can imagine) in a cheap and fast way. So, if you have an idea where you can use this, please contact me.

Some pictures:

1. The Onion with an USB stick (A) and the temperature sensor (B). The Onion is on top a pack of cigarettes so you can guess its size.

2. A simple dashboard (running on my iPad) that shows the temperature of the last 30 mins, the current temperature, and the average of the last 5 days. I created a couple of artificial temperature variations by putting some ice near the sensor.

The new world of analytics and BI. From a text file to the iPad in a couple of hours.

The world  has changed in many ways in the last 5 years. It has changed in  analytics and business intelligence too.

A friend of mine is running a web proxy, a program that monitors which web pages are visited and by whom from inside his company. This proxy generates a log file in text format which contains the username, the page visited, the amount of bytes, etc. The file looks like this:

192.168.15.63 user2 [15/Sep/2015:09:10:09 -0400] "GET http://api.new.livestream.com/accounts/8665913/events/4253600/broadcasts/99302610/availability HTTP/1.1" 304 342 TCP_MISS HIER_DIRECT
192.168.15.150 user1 [15/Sep/2015:09:10:16 -0400] "CONNECT ci3.googleusercontent.com:443 HTTP/1.1" 200 5917 TCP_MISS HIER_DIRECT
192.168.15.150 user1 [15/Sep/2015:09:10:16 -0400] "CONNECT ci5.googleusercontent.com:443 HTTP/1.1" 200 11657 TCP_MISS HIER_DIRECT
192.168.15.150 user1 [15/Sep/2015:09:10:16 -0400] "CONNECT ci6.googleusercontent.com:443 HTTP/1.1" 200 17674 TCP_MISS HIER_DIRECT
192.168.15.63 user2 [15/Sep/2015:09:10:25 -0400] "POST http://livestream.com/analytics/api/track HTTP/1.1" 204 484 TCP_MISS HIER_DIRECT

Based on the contents of the log file, my friend wants to know which are the top 10 internet users, the top 10 visited sites, or the peak hours of daily internet use. And he asks me to process the file and get this information for him. There are many ways to achieve it, but the one I tested is the following:

I loaded the log file to a Hadoop-based platform (Microsoft Azure HDinsight) and gave it a queryable structure. For achieving this I had to write only two lines of  code.

Now I have the file loaded and I want to give my friend a familiar tool to query the logged data. It could be Excel (there are ODBC drivers for Excel to connect to HDinsight), but why not something more mobile? For example Microsoft PowerBI, it has has the ability to connect to HDInsight and you can run PowerBI in your iPad.

Now, I have a log file from a web proxy loaded into a Hadoop-based tool, and my friend can query its contents from his iPad, amazing isn't it? 

I used technologies like cloud and big data for a trivial task like this, and I did it without installing any tools locally. My point here is: The way we process and make information available has changed a lot in the last 5 years, and we (IT professionals) have to adapt to those changes, and more importantly, we must take advantage of them!

Hot start for BI tools in 2015! Microsoft PowerBI and SAP Lumira Edge are here!

Just when the first month of 2015 was about to end, two big BI news appeared on the Internet:

Microsoft PowerBI and SAP Lumira EDGE are now available; let me explain why this is big news for me.

I've been working with BI (for the last 11+ years) specifically with SAP BW / BO. I think that for corporations SAP BW / BO is a spectacular tool, but what about small companies, or small units inside corporations?

In my opinion there was no All-in-one (ETL, Presentation, authorisations, scheduling, etc.) BI tool for small companies. This is no longer the case after the release of PowerBI and the use of Office 365. Basically you can use it to:

Extract data from:
- Sources in the cloud (SalesForce, Access Apps, etc.).
- Sources in your office (SQL server databases, Excel/Text files, etc.)

Create nice static printer friendly reports or stunning Dashboards and publish them on your Office 365 Portal. Also you can schedule them to be refreshed automatically. Then anyone with access can use these reports from a PC or from a tablet.

In less than 4 hours I managed to do the following: Install a piece of software that allowed PowerBI to connect to an SQL database in my PC enabling an automatic refresh of the BI model from my local data. Publish two reports to the Office 365 portal that can be accessed from a PC or a tablet: Wow!

Easy and powerful BI is available now for small companies. Off course there are still some rough edges since it is so new, for example the error messages that apear when using other languages than English are totally cryptical, or the screenshots from the help do not match the actual screens.

Inside corporations there are highly specialized teams that could really benefit from an In-Memoy BI solution like Lumira EDGE, which is easy to install a deploy. I've not tested it yet, but it looks promising.

Now January 2015 is gone, but we have two new interesting options for BI.