Cloud Native Ecosystem / Cloud Services / Data

Microsoft Power BI Gets Low-Code Datamart Feature

24 May 2022 10:21am, by

Today, at its developer-oriented Build conference, Microsoft’s Power BI team is announcing a new self-service, low-code “datamart” feature for Power BI Premium. This initiative integrates, and bridges the gap between, Power BI’s own analytics engine and Microsoft’s Azure SQL Database technology, derived from its flagship SQL Server relational database. The announcement also brings with it a major usability dividend, by delivering a full-fledged Power BI authoring experience to users of non-Windows platforms.

The Analytics Technology Hodgepodge

Before getting to the details of the datamart feature, let’s take a look at what has motivated Microsoft to deliver it. The need for the feature stems from what is frankly an awkward situation: in the world of analytics, three main categories of technology coexist, often cooperatively but sometimes chaotically. Relational technology is used for data warehouses and data marts. Dimensional technology is used in business intelligence scenarios and data lake technology is used for open source big data workloads

The various technologies are of different vintages and based on slightly different paradigms. Nevertheless, most customers use at least two of them in combination. Integrating them typically involves the use of data pipelines and ETL (extract, transform and load) technologies to process the movement and transformation of data between them. Those pipelines can be complex, though, and they can be brittle. The need to create and maintain them often stymies the rate of progress organizations can make towards analytics success.

Executive Endorsement

Arun Ulag, Microsoft’s Corporate Vice President for the Intelligence Platform, briefed The New Stack on this major new capability set and explained that it represents a significant effort in bringing two major analytics technologies — relational and dimensional — together. Ulag also explained how the datamart feature does this in a fashion where the required data pipelines are generated in an automated, no-code fashion.

Speaking of the new datamart feature set, Ulag said “It’s probably the biggest capability that we have launched in Power BI in the last… two to three years.” He also explained the self-service value it represents. Ulag told the New Stack that “Today, a lot of work happens based upon centralized data warehouses provided by IT,” adding that “the challenge is that IT teams are highly overloaded. The number of requests they get from business today far exceeds what they can handle, which means business slows down.” Ulag says that, as a result, business users often build their own data marts in tools like Excel, Access and SharePoint, which aren’t designed for data mart workloads and often don’t perform or scale well. “Worst of all,” Ulag says, “they’re outside the visibility of IT… and it essentially creates lots and lots of data sprawl.”

How It Works

The implementation for Power Bi datamarts is actually pretty simple. Power BI has always monitored the process users go through to identify the data sources and tables/datasets needed for their reports. Now, instead of just creating a data model that is stored with the report, Power BI will design and deploy a full-fledged relational data mart and the data pipelines needed to populate and refresh it.

The same Power Query technology used to create Power BI models is also leveraged to generate the datamart. And since Power Query has always been usable in a low-code/no-code fashion, it still can be. Those who want to custom-code certain aspects of the needed data transformation and movement can still use Power Query’s rich “M” language do so, just as they could before. But they never have to.

The datamart feature leverages the Power BI cloud service’s dataflow facility, which had already implemented much of Power Query’s user interface for the Web browser environment, removing the dependency on Power BI Desktop. But now the full model design experience has also been implemented in the browser. This is a nice option for Power BI users who would like to do everything in the cloud service, rather than splitting their efforts between there and the desktop.

Power BI’s new browser-based data modeling experience
Credit: Microsoft

Bigger Tent

But the value of a browser-hosted design experience extends beyond elegance and flexibility. In fact, the removal of the dependency on Power BI Desktop means that users of non-Windows operating systems now have a full-fledged authoring experience available to them without having to run Windows under emulation. After almost seven years since Power BI’s release, full-featured authoring of Power BI reports will now be available to Mac users, Linux users, and Chromebook users too. That’s a very big deal and the last real competitive vulnerability Microsoft had against major players like Salesforce’s Tableau, Google’s Looker, Qlik Sense, and other BI players.

Interestingly, Microsoft is using its Azure SQL Database technology to implement the datamart feature. That means, in addition to being usable from inside Power BI, datamarts each get their own SQL Server endpoint and will work with all compatible tools and developer libraries. But users who don’t care about that can treat it as a mere implementation detail, as datamarts don’t require users to have an Azure account, or provision an Azure SQL server or database. Regardless, all datamarts are fully visible to customers’ IT organizations, providing self-service for business users, without locking IT out of the process.

Visibility extends to other Power BI-compatible platforms as well. This means that Power BI datamarts are discoverable from Excel and Teams, in addition to the native Power BI environment. Datamarts also participate Power BI’s information protection, endorsement, certification and security services.

Pricing and Availability

Power BI Premium is required to get the datamart feature, but that includes Premium per user (PPU), giving customers the flexibility to pay on an enterprise-wide or per-seat basis. Currently, PPU carries an approximately $10/month/user surcharge, for an all-in price of $20/month/user.

The new datamart feature is available in public preview now. Accordingly to Ulag, as of the announcement at Build, all 350,000+ Power BI customers should have access to the datamart capability. The timeframe for post-preview, general availability (GA) is somewhat “TBD.” Microsoft likes to let the dust settle on major new features before emblazoning them with the GA badge of honor.

Gave at the Office

In addition to the new datamart feature set, Microsoft is announcing a laundry list of other new features. These include the public preview, available by the end of this month, of a new data storytelling capability, based on the integration of interactive Power BI report pages inside PowerPoint presentations, as shown in the image at the top of this post. There are also previews of new integration of Power BI into Outlook and Office Hub, as shown below.

Power BI integration with Outlook
Credit: Microsoft

Power BI automatic aggregations has reached GA, and new Microsoft Purview integration, administrative capabilities, Power BI Embedded and paginated reporting features are being announced as well. To top it all off, Microsoft says a new migration service for moving Azure Analysis Services models to Power BI is coming soon.

Finish Line

The Power BI team is simply one of the busiest Microsoft has. It revs the product each month and every so often, including right now, the team assembles sufficient progress to constitute a sea change in the platform.

But while Power BI is going gangbusters, Microsoft is still playing second fiddle to Amazon Web Services in the cloud arena. The question that looms large, then, is whether, and how, Power BI’s dominance in the BI world can be transformative to Microsoft’s position in the cloud market overall. Microsoft needs to do more than Build; it also needs to win. Power BI’s momentum provides one potential route to such a victory, and Microsoft watchers should keep monitoring its progress.

Disclosure: Post author Andrew Brust is a Microsoft Data Platform MVP and member of Microsoft’s Regional Directors Program for independent influencers. His company, Blue Badge Insights, has done work for Microsoft, including the Power BI team.