Empower Power BI with Lyftrondata
Microsoft’s front end is improving every day and its fans are growing month after month, release after release, and it’s not uncommon to find Power BI attacking no-SQL data sources.
There was a time when Microsoft did not seem to listen to the specific concerns of customers, but it has always been excellent at creating and delivering world-class, robust commercial software. It took them a few years to close the circle of BI SQL, since there was no real BI Front End and although they tried to convince us, EXCEL could not be a Front End for BI.
Most of the major data providers are already built and deployed in production. There is also a catch-all ODBC database connector and new connectors are continually being developed. There are many cloud-based connectors that allow users to easily connect to cloud content such as Google Analytics, SalesForce.com and many more online software service providers. But, there is something that transforms Power BI into a war machine without equal. We are talking about Lyftrondata.
Lyftrondata transforms any data source into an SQL Query without using any connector and in real time. This gives Power BI the possibility to work with tables of an Oracle or MongoDB without having to create a classic Data Warehouse structure and above all without ETL processes.
Reasons for a success story
Power BI is an easy and very stable product and also uses Microsoft’s muscle to constantly improve its features, so it has monthly releases and a large users community who contribute with elements, ideas and solutions.
It is always the undisputed leader in the analytics market, especially because it integrates seamlessly into the Microsoft SQL BI environment.
It’s an affordable solution, offered for free in selected Microsoft subscriptions, so every company user can use it for analytics. Its use is a good way to move from analytics in Excel to Self- Service BI, without trauma or excessive investment in licensing and training. Its ability to use DAX makes it an easy and powerful tool.
Some current weaknesses
Although the Microsoft product presents advanced features, there are certain aspects that limit its use, especially in an environment with a large amount of data. Let’s look at some aspects where these weaknesses can be appreciated.
This weakness is common in most Business Intelligence front ends: Data models will be duplicated between similar dashboards. Currently there is no way to standardize data modeling without having to assume it in typical Data Warehouse structures such as OLAP cubes from Analysis Services.
Other aspect is the limitation of in-memory caching, bigger databases do not fit into memory. Actually, joining data across different data sources in DirectQuery is not possible, both sources must be imported into memory and the storage is limited using an import mode.
Although Microsoft continues to work on this, Power BI does not support all the data sources that users may consider important. In this sense, it is clear that the Seattle’s team has focused on the principle of making it compatible with its environment in order to gradually add other sources of data, but there is still a long way to go. For example Power BI in the cloud supports only Microsoft data sources at the moment. The data modelling lacks layers, so when a dashboard is based on dirty data (duplicate customers), it is hard to define data cleansing rules because Power BI focuses on the frontend, not the data modeling aspect. Finally the data transformation logic is hidden inside one dashboard and hard to migrate to other dashboards that should be based on the same dataset. This will cause that a similar new dashboard will be just a copy-paste and their the data preparation logic will be duplicated. For this reason any changes to the data source or data cleansing rules must be manually corrected in all dashboards. This type of approach makes it appear necessary to have a Data Warehouse, but it’s design and development would delay the BI project by several months. For sure, an Agile type of a Data Warehouse is needed that combines the agility of Power BI in data connectivity with the benefits of a governed Data Warehouse.
Solve the limitations of Power BI with Lyftrondata
Slow Speed? Lyftrondata it’s an query accelerator and give a burst speed to Power BI dashboard using in-memory analytics and the powerful Apache Spark Big Data engine.
No enough source data? Lyftrondata puts at the disposal of Power BI users more than 100 different data sources that can be combined with each other. No more limitations of cross datasource joins in DirectQuery mode. Caching limits? In Power BI Caching cannot be offloaded to more performant engines, with Lyftrondata it’s possible derivate the caching to Apache Spark and other engine like: SQL Server DW, SAP Hana or Amazon RedShift. Huge Data Sets? There’s a lot of talk about in-Memory but we have to be realistic: adding hundreds of millions of rows to a Direct Query connection requires a cube with pre-aggregated axes.
Lyftrondata solves it by using Apache Spark for in-memory processing combined with materialized views that store pre-aggregated data ( joins, groupings, aggregations, filters). Lyftrondata transparently rewrites queries from Power BI to use a pre-aggregated copy.
Now we speak about the issues involved in migrating from other BI tools to Power BI. Without doubt, the major benefit it’s that Power BI may be deployed to more people but that means older dashboards must be ported and their data model is hidden inside dashboards. With Lyftrondata, the data models (how data sources are joined, filtered and transformed) will be migrated to a view in a Lyftrondata Logical Data Warehouse, then the same data model will be reused by both the old BI tool (like Tableau or Qlik) and Power BI at the same time. As a result, collaborative data management is possible, changes to the data definition (like a VIP customer definition) done once in Lyftrondata will be used instantly across all dashboards in Power BI and previous BI tools.
The most radical change that we suggest to make in a Power BI environment is to introduce Lyftrondata as an intermediate layer between data sources and Power BI. Note that any data source in Lyftrondata is transformed into an SQL query that can be directly attacked with Direct Query in Power BI. Lyftrondata simulates Microsoft SQL Server and is recognised by Power BI as an SQL Server instance. Connection from Power BI to Lyftrondata is realized through the standard Microsoft SQL Server connector, Lyftrondata translates Transact SQL (SQL Server version of SQL) to over 100 data sources. As a result, there will be only one data source in all dashboards which simplifies development.
In other words, the same agility of Power BI with Direct Query can be adapted to all data instantly. The most important advantage it’s to have all the benefits of a data warehouse, without the average 6 months time to build one. A Virtual DW in Lyftrondata is build using a self-service portal using virtual views. The others features of a VDW are activated on demand: caching, query acceleration, data cleansing, data transformation.
With Lyftrondata, it is possible to show the first dashboards to a user/customer instantly without a delay of a traditional Data Warehouse implementation (ETL) and improve the data definition after receiving feedback from the customer. The data cleansing (when a customer identifies discrepancies in the dashboard) or acceleration (when the dashboard is too slow) is delayed and activated in Lyftrondata later on. However the business user who requested the dashboard is kept engaged, receives his dashboards instantly and incrementally sees improvements.
Another important benefit of Lyftrondata for a Power BI is related to Power BI hosted in Azure. Now a SaaS version of Power BI has a limited set of supported connectors, however it can connect to SQL Server. Lyftrondata emulates SQL Server and may be installed in the cloud or on-premise and will serve as a data layer for Power BI. Now it is possible to connect both cloud and on-premise data sources to Power BI and move only selected data to Azure and use SQL Server Data Warehouse for storage and query processing. The decision to offload any data source is made by just a few clicks in Lyftrondata.
LDW Technology the magician behind Lyftrondata
The new concept of the Logical Data Warehouse enables IT departments to fulfill their tasks and responsibilities in BI-related matters. The logical layer provides several mechanisms for displaying data on the DW and elsewhere in an enterprise without the need to relocate and transform data before the display time. In this way the data is fresher and the structure of the supplied data is created at runtime. Achieving these benefits has been a challenge in the past, as software, hardware and networks simply lacked the speed, scalability and reliability required for installations, but Data Virtualization and the Cloud have opened up new scenarios.
About Power BI
Power BI is a business analytics service provided by Microsoft. It provides interactive visualizations with self-service business intelligence capabilities, where end users can create reports and dashboards by themselves, without having to depend on information technology staff or database administrators.
- Seamlessly connects more than 100 types of data sources
- Consolidates data on the fly without an ETL that is time consuming to build and operate.
- Combines the agility of instant, real-time analytics of self- service BI tools like Power BI with the data quality and acceleration features of a traditional data warehouse.
- Fosters collaboration in the area of data management for BI users by decreasing dependency on IT departments.
- Leverages the best Big data SQL engines like Apache Spark to get the best performance.
- Provides a model for any presentation tool or data analysis as Power BI, Excel, Tableau or Qlik.