As promised I started writing blog post series for the Online Book: Power BI from Rookie to Rockstar, and this is the first post.
Power BI is not a new name in the BI market, components of Power BI has been in the market through different time periods. Some components such As Power BI Desktop is such new that released as general availability at 24th of July. On the other hand Power Pivot released at 2010 for the first time. Microsoft team worked through long period of time to build a big umbrella called Power BI, this big umbrella is not just a visualization tool such as Tableau, it is not just a self-service data analysis tool such as PivotTable and PivotChart in Excel, it is not just a cloud based tool for data analysis. Power BI is combination of all of those, and it is much more. With Power BI you can connect to many data sources (wide range of data sources supported, and more data sources add to the list every month). You can mash up the data as you want with a very powerful data mash up engine. You can model the data, build your star schema, or add measures and calculated columns with an In-Memory super fast engine. You can visualize data with great range of data visualization elements and customize it to tell the story behind the data. You can publish your dashboard and visualization tool in cloud and share it to those who you want. You can work with On-premises as well as Azure/cloud based data sources. and believe me there are much more things that you can do with Power BI which you can’t do with other products easily.
So What is Power BI?
There are many definitions for this tool, here is my version of it simplified for everyone to understand;
Power BI is a cloud based data analysis, which can be used for reporting and data analysis from wide range of data source. Power BI is simple and user friendly enough that business analysts and power users can work with it and get benefits of it. On the other hand Power BI is powerful and mature enough that can be used in enterprise systems by BI developers for complex data mash-up and modelling scenarios.
Power BI made of 6 main components, these components released in the market separately, and they can be used even individually. Components of Power BI are:
- Power Query: Data mash up and transformation tool.
- Power Pivot: In-memory tabular data modelling tool
- Power View: Data visualization tool
- Power Map: 3D Geo-spatial data visualization tool
- Power Q&A: Natural language question and answering engine.
- Power BI Desktop: A powerful companion development tool for Power BI
There are many other parts for Power BI as well, such as;
- PowerBI.com Website; which Power BI data analysis can be shared through this website and hosted there as cloud service
- Power BI Mobile Apps; Power BI supported in Android, Apple, and Windows Phones.
Some of above components are strong and has been tested for very long time. Some of them however are new and under frequent regular updates. Power BI built easy graphical user interfaces to follow, so a business user simply could user Power Query or Power BI desktop to mash up the data without writing even a single line of code. It is on the other hand so powerful with power query formula language (M) and data analysis expression (DAX) that every developer can write complex codes for data mash up and calculated measures to respond challenging requirements. So if you’ve heard somewhere that Power BI is a basic self-service data analysis tool for business analysts and cannot be used for large enterprises systems, I have to say this is totally wrong! I’ve been using Power BI technology myself in many large enterprise scale systems and applications, and I’ve seen usage of that in many case studies all around the world.
Power BI components can be used individually or in a combination. Power Query has an add-in for Excel 2010 and Excel 2013, and it is embedded in Excel 2016. The add-in for Power Query is available for free! for everyone to download and use it alongside with existing an Excel (as long as it is Excel 2010 or higher versions). Power Pivot has been as an add-in for Excel 2010, from Excel 2013 Power Pivot is embedded in Excel, this add-in is again free to use! Power View is an add-in for Excel 2013, and it is free for use again. Power Map is an add-in for Excel 2013, it is embedded in Excel 2016 as 3D maps. Power Q&A doesn’t require any installation or add-in, it is just an engine for question and answering that works on top of models built in Power BI with other components.
Components above can be used in a combination. You can mash up the data with Power Query, and load the result set into Power Pivot model. You can use the model you’ve built in Power Pivot for data visualization in Power View or Power Map. There is fortunately a great development tool that combines three main components of Power BI. Power BI Desktop is the tool that gives you combined editor of Power Query, Power Pivot, and Power View. Power BI Desktop is available as stand-alone product that can be downloaded separately. With Power BI Desktop you will have all parts of the solution in one holistic view.
A Quick Overview of Components
To give you an overall view of what you would expect to see in each component I’ve put few explanation for each component here. There will be detailed description for all components later on in future chapters.
Power Query
Power Query is data transformation and mash up engine. Power Query can be downloaded as an add-in for Excel or be used as part of Power BI Desktop. With Power Query you can extract data from many different data sources. You can read data from databases such as SQL Server, Oracle, MySQL, DB2, and many other databases. You can fetch data from files such as CSV, Text, Excel. You can even loop through a folder. You can use Microsoft Exchange, Outlook, Azure…. as source. You can connect to Facebook as source and many other applications. You can use online search or use a web address as the source to fetch the data from that web page. Power Query gives you a graphical user interface to transform data as you need, adding columns, changing types, transformations for date and time, text, and many other operations are available. Power Query can load the result set into Excel or into Power Pivot model.
Power Query also uses a powerful formula language as code behind called M. M is much more powerful than the GUI built for it. There are many functionality in M that cannot be accessed through graphical user interface. I would write deeply about Power Query and M in future chapters so you can confidently write any code and apply complex transformations to the data easily. screenshot below is a view of Power Query editor and some of it’s transformations.
Power Pivot
Power Pivot is data modelling engine which works on xVelocity In-Memory based tabular engine. The In-Memory engine gives Power Pivot super fast response time and the modelling engine would provide you a great place to build your star schema, calculated measures and columns, build relationships through entities and so on. Power Pivot uses Data Analysis eXpression language (DAX) for building measures and calculated columns. DAX is a powerful functional language, and there are heaps of functions for that in the library. We will go through details of Power Pivot modelling and DAX in future chapters. Screenshot below shows the relationship diagram of Power Pivot
Power View
The main data visualization component of Power BI is Power View. Power View is an interactive data visualization that can connect to data sources and fetch the metadata to be used for data analysis. Power View has many charts for visualization in its list. Power View gives you ability to filter data for each data visualization element or for the entire report. You can use slicers for better slicing and dicing the data. Power View reports are interactive, user can highlight part of the data and different elements in Power View talk with each other. There are many configurations in Power View visualization that I will explain fully in future chapters.
Power Map
Power Map is for visualizing Geo-spatial information in 3D mode. When visualization renders in 3D mode it will gives you another dimension in the visualization. You can visualize a measure as height of a column in 3D, and another measure as heatmap view. You can highlight data based on the Geo-grahpical location such as country, city, state, and street address. Power Map works with Bing maps to get best visualization based on Geo-graphical either latitude and longitude or country, state, city, and street address information. Power Map is an add-in for Excel 2013, and embedded in Excel 2016.
Power BI Desktop
Power BI Desktop is the newest component in Power BI suit. Power BI Desktop is a holistic development tool for Power Query, Power Pivot and Power View. With Power BI Desktop you will have everything under a same solution, and it is easier to develop BI and data analysis experience with that. Power BI Desktop updates frequently and regularly. This product has been in preview mode for a period of time with name of Power BI Designer. There are so much great things about Power BI Desktop that cannot fit in a small paragraph here, you’ll read about this tool in future chapters. because of great features of this product I’ll write the a section “Power BI Hello World” with a demo of this product. You can have a better view of newest features of Power BI Desktop here in this blog post. screenshot below shows a view of this tool;
Power BI Website
Power BI solution can be published to PowerBI website. In Power BI website the data source can be scheduled to refresh (depends on the source and is it supporting for schedule data refresh or not). Dashboards can be created for the report, and it can be shared with others. Power BI website even gives you the ability to slice and dice the data online without requiring any other tools, just a simple web browser. You can built report and visualizations directly on Power BI site as well. screenshot below shows a view of Power BI site and dashboards built there;
Power Q&A
Power Q&A is a natural language engine for questions and answers to your data model. Once you’ve built your data model and deployed that into Power BI website, then you or your users can ask questions and get answers easily. There are some tips and tricks about how to build your data model so it can answer questions in the best way which will be covered in future chapters. Power Q&A and works with Power View for the data visualizations. So users can simply ask questions such as: Number of Customers by Country, and Power Q&A will answer their question in a map view with numbers as bubbles, Fantastic, isn’t it?
Power BI Mobile Apps
There are mobile apps for three main mobile OS providers: Android, Apple, and Windows Phone. These apps gives you an interactive view of dashboards and reports in the Power BI site, you can share them even from mobile app. You can highlight part of the report, write a note on it and share it to others.
Power BI Pricing
Power BI provide these premium services for free! You can create your account in PowerBI.com website just now for free. Many components of Power BI can be used individually for free as well. you can download and install Power BI Desktop, Power Query add-in, Power Pivot add-in, Power View add-in, and Power Map add-in all for free! There are some features of these products that reserved for paid version however, such as Power BI Pro which gives you some more features of the product. If you want to learn more about pricing of the Power BI I encourage you to read this page. However create your free account today and give it a try, it won’t cost you anything except your precious time, which I’d say definitely worth it.
Preparation
To follow examples of this book download the latest version of Power BI Desktop from here. Most of examples will be demoed through this product, however there might be some exceptions, which I will mention at the beginning of the specific section if you need another tool to download. for the data source for some demos I’ll use AdventureWorks database examples, it might be the database itself or the tabular model or other versions and shapes of that, I’ll write more information about the requirement for running demo at the beginning of each demo. Files of demos will be shared, either *.pbix files (Power BI Desktop files) or Excel files for you as a reference. If you have any questions just use the comment section below each post.
If you want to have a clue about what to expect in this book read the table of content here.
What to Expect in Next Section
In summary you’ve read about what Power BI is, and what are Power BI components. You’ve learned that Power BI is cloud based data analysis tool that can be used by data analysts, business analysts and power users because it is easy to use, however it is so much powerful that can be used to answer complex BI requirements. In next section I’ll explain how to use Power BI Desktop for a Hello world example. You’ll learn some great features of this product through a demo and you will have a better understanding of Power BI tools.
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.
Great post and great website!!!
I am really looking to read all of your “book”.
Many thanks.
Nir.
Thanks Nir for visit, and reading.
I’m glad to hear your great feedback.
Cheers,
Reza
Thank you for your excellent work. I can figure out the effort you’re putting in realizing this online book.
Your introduction was clear and served me to clarify some doubts about name and positioning of the various Power BI components.
I’ll certainly follow your series to gain knowledge of this product.
Andrea (Italy)
Thanks Andrea for your great feedback and kind words.
Cheers,
Reza
Excelent post Reza! I just wrote a brief post so people from IT, analysts and power users from latin-america are invited to read your posts.
Thanks David for your kind words, and many thanks for sharing this
Cheers,
Reza
Thank you .I’m preparing a resersh about power BI but I have many questions .First I don’t know where the Datawarhouse is stored in Power BI?Second ,I don’t understand what’s the diffrence between ssas connector and personal gateway,also I need to know about the terme of direct Query.
Can you please explain this point to me.
Hi Mo,
to answer your questions:
There is no actual data warehouse when you work with power BI. you can build your model in Power BI solution. However the data source might be data warehouse or might not be. it can be on cloud or on-premises.
SSAS connector is for connecting to Analysis Services on-premises. Personal Gateway is for connecting to on-premises data sources, but it can’t connect to SSAS on-prem.
DirectQuery is a live connection to the source data. that means data shouldn’t be LOADED into Power BI. Power BI will query data LIVE, so the data connection shouldn’t be refreshed to provided up-to-date information
Cheers,
Reza
can we say that Power BI is just a visualisation tool as QlikView and Tabelau?
Hi Mouna,
No Power BI is far beyond that. with Power BI you can transform the data as you want with complex data transformations available in Power Query. You can build the model and calculations with measures as you want in Power Pivot with DAX. and only the presentation layer is a visualization. There is also a Power Q&A engine on top of the model that answer your natural language questions from the model as well. So Power BI gives you a full end to end expereince.
thank you ,it was helpful for me
Thank you for your great post. your writing is fluent and I like it.
I have questions.
What is difference between (SSIS,SSAS,SSRS Microsoft) and Power BI? On the other hand, are the same their functionality? If the answer is Yes, Which one is better?
Hi Saieh,
Thanks for your kind feedback.
The main difference between Microsoft BI Suite in SQL Server and Power BI is that; SQL Server BI is mostly a corporate solution, and Power BI more self-service solution. There is no “better” here. each of these are serving different purposes. For an end-to-end BI solution you might need a bit of both, or only one of them. Requirement defines which one should be used.
Cheers,
Reza
Thank you for your reply. But can you explain what are these requirements?
Because in this post you said: ” I’ve been using Power BI technology myself in many large enterprise scale systems and applications”
Power BI can be used as the whole BI solution, or part of it as a hybrid architecture. Some companies don’t have a BI solution at all, so depends on the size of their data, deployment approach, integration architecture…. Power BI or any other application might be used for the whole BI implementation. for some organizations who might have part of a BI solution like SSAS multi-dimensional, tabular, or even a SQL Server or database back end as data warehouse, then Power BI might be only the presentation layer. For some other requirements some other approaches might be useful. Wide range of requirements are in market, and the solution is highly depends on what each business want to achieve.
Thank you very much. It was helpful.
Successful!
Excellent piece of work Mr. Reza Rad.
stay blessed and caring
Good quality writing.
Great information given by you.
Thanks……