With the announcement of Power BI a week ago and with great features in this product, there are many opportunities that this product and service can be used. Power BI can be used alongside some other Azure services, and even On-Premises services to build an end-to-end enterprise BI and Data Analysis Solution. In this post I’ll just explain one example scenario of Hybrid and End-to-End BI solution that uses below technologies;
- Data Sources On-Premises
- Azure Data Factory or SSIS with Azure Pack for Data extraction and transformation
- Azure SQL Database or Azure SQL Data Warehouse as the database / data warehouse engine in cloud
- Power BI as the data analysis and front-end tool
Above technologies together provide a highly reliable, and scalable data analysis solution. Obviously above scenarios is only one of the 100 scenarios that these products can be used together to build an end-to-end solution. I considered data sources to be on-premises because most of the data still sits on companies and organization’s on-premises data store. the on-premises data store can be of any type such as SQL Server database, Oracle, MySQL DB, or even CSV and Excel files…. As long as there are some data stored somewhere we can use that as the source.
Below is an architecture high level diagram of the solution;
Data Extract,k Transform, and Load
in the diagram you see two tools used for data extract, load and transform as below:
- Azure Data Factory
- SQL Server Integration Services
Azure Data Factory is cloud based compute service for data extraction and loading, it supports data sources such as SQL Server on-premises, Oracle on-premises, CSV files and some other sources. Azure Data Factory connects to on-premises data sources with a gateway named Data Management Gateway. Data Factory can load the data into on-premises data stores as well as cloud based such as Azure SQL Database and Azure SQL Data Warehouse. If you want to learn more about Azure Data Factory read the article here: Building the first Azure Data Factory.
SQL Server Integration Services (SSIS) is a familiar name in database world. SSIS is premier data transfer and consolidation tool that supports heaps of data sources, it has many built-in transformation that you can apply on the data set, and the result set can be loaded in any kind of destination. SSIS packages can load data into Azure data stores especially with the new Azure Pack released for SSIS. To learn more about SSIS please watch SSIS tutorial video series here: SSIS Tutorial Videos.
Data Warehouse
There are two main azure services that are recommended to be used as the Data Warehouse on cloud. Azure SQL Database is the SQL Server database engine, and Azure SQL Data Warehouse released recently and optimized and is configurable highly for massive data and the analytical usage of that. There are fortunately heaps of information about Azure SQL Database that you can read and watch. you can use Azure SQL Database documentation as starting point. Azure SQL Data Warehouse is very new, and there are not so much blogs, or articles about it, but documentation page of Azure SQL Data Warehouse has some informational videos about it.
Power BI for Data Analysis
Power BI 2.0 released for general availability almost a week ago, and it has many new features. Power BI made of three main components: Power Query for data extraction and transformation, Power Pivot for data modeling, and Power View for data visualization. Dashboards and visualizations of Power BI can be deployed into cloud (PowerBI.com) website, and then it can be accessed from everywhere, and every platform. There are mobile applications for Power BI for Android, and Apple as well as Windows phone. Power BI reports are interactive, and user friendly. Power BI has a lot to say in this section, so I just refer you to some Power BI blog posts for more information.
Power BI can connect to many data sources, and Azure SQL Database and Azure SQL Data Warehouse are included in the list. Connection can be easily made, and this connection also supports scheduled refresh. So the data can be refreshed based on the required schedule. Here is list of all supported data sources for Scheduled Data Refresh.
Too Many to say, To0 few space
Explaining all the components of above solution won’t be possible in a single post. I will write some more blog posts explaining in details each section. However for this post I like to mention only part of the work that might be helpful as a starting point for you. In this post I like to mention how easy is the connection from Power BI to Azure SQL Database.
Power BI Connection to Azure SQL Database
Connecting from Power BI to Azure SQL Database can be made simply through Get Data:
You’ll need to enter server name and database name
and then credential. You have options to use existing credential, or enter credential of a user (you might need Azure Active Directory for these), or you can enter SQL Database credentials.
and then you will see all objects that you have access to
Now you can start building model and visualization, and then deploy your reports to Power BI website. After deployment you can set Scheduled Data Refresh.
In next posts I’ll explain other components of this solution.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Very interesting post Reza!
For those of use that have developed on premise SSAS Multidimensional Model, how could we use them in Power BI if we want to move them in the Cloud on Azure?
Thanks and Regards,
Simon
Hi Simon.
Thanks for your kind feedback.
SSAS Multi-Dimensional at the moment is only supported through off-line connection from Power BI. which means data from SSAS MD have to be loaded again into Power Pivot model of Power BI which is an additional step. So it is not as great as SSAS Tabular to work with (from Power BI point of view I mean).
But Microsoft team is putting a lot of effort to support SSAS MD live connection soon.
For now there are two options for SSAS MD and hybrid solution:
1- Using SSAS MD with Power BI with off-line connection. with queries to get data, building model in Power BI and rest of the story.
2- Using Datazen to connect to SSAS MD.
I’ve heard also here and there that SSAS MD can be hosted as a web service type which can be used with Power BI. I haven’t tried this method myself, and not sure if this is an option.
Don’t hesitate to ask any questions you might have.
Cheers,
Reza