Microsoft Power BI serves as the third layer of our data analytics stack. Power BI differs from BI tools such as Looker, Chartio, Periscope Data, and Mode in that it doesn't depend on or expose any SQL code to power its queries. Aug 10, 2016  Real-time in no time with Power BI. Recently, we’ve seen an explosion of powerful real-time use cases – from factories using IoT sensors to monitor their equipment, to media agencies monitoring their social media campaigns as they go viral. Power BI further amplifies the power of real-time data with an easy setup process, and an array of powerful visualization and analysis tools. Once the data is available, your analysts can use it to create reports. In this post, we'll look at how to start from scratch and create a report using Power BI. Three tiers of the data analytics architecture. We'll start with Stitch, a simple, powerful ETL service for businesses of all sizes, up to and including the enterprise.

• Power Bi Architecture Not Build Data In Real Time Money

IntroductionPower BI is a cloud-based Service, which provides data visualization options, based on the source data. Using Power BI, we can create dashboards, based on the data. In order to create the reports.

Using Power BI, make sure that you have a Power BI account.If you don’t have an account, you can sign up for it. You can either workwith Power BI online or download the Power BI Desktop version.Power BI has the flexibility to consume numerous data sources and generate business intelligence reports from the source data. In this article, we will see how to consume JSON data from a JSON file within Power BI and create reports using that data.The JSON file that we will be using contains the Q1-Q4 sales data of cars as shown below.Connect to Data sourceIn order to derive business intelligence reports, we will have to first connect to the data source. Click on ‘Get data’ to connect to the JSON file that has the source data.This will open up the Get Data window, Select JSON option and click on Connect.Browse to the JSON file which we will use as the data source.Upon clicking on Open, the JSON data will be loaded into the Query Editor window. The loaded data will be present as a single row which represents the JSON root node. Click on ‘Record’ to drill down to the list of records.Further, drill down to individual records by clicking on ‘List’.Convert JSON to table dataThus we have a record for each item in the JSON File.

We can now convert this to the Power BI table by selecting the ‘To Table’ option.Select the below-highlighted column mark.It will give us the option to select which column should be part of the table. Ensure that you uncheck the check box ‘Use original column name as prefix’. If it is checked, the generated column will have the naming convention ‘Column1.NewColumnName’.Upon clicking on OK, the table has been generated from the JSON file.Let’s save the table by clicking on ‘Close and Apply’ in the Query Editor. This will enable us to generate reports from the table using the available templates.The fields from which we can generate the Power BI report has become available in Power BI Desktop Designer.Format the DataBefore we can create reports, we should format the data by changing the data type of Q1-Q4 Sales data from Text to Whole number. From the designer select the table icon and select the column ‘Q1Sales’. By default, it is of the data type ‘Text’.Let’s convert it to a Whole Number so that we can use it to plot a chart.Click on Yes to start the data type change. Repeat the data type change process for Q1-Q4 Sales column.Generate Business Intelligence ReportsHead back to the report designer by clicking on the below icon.Now we can select from multiple Visualization options.

Let go with a bar chart for the time being.Drag and drop ‘Product’ field to the ‘Axis’ section which will form the X-Axis of the bar chart. Drag and drop the fields Q1-Q4 Sales to Values section so that it will come up on the Y-Axis of the bar chart.This will result in the Power BI bar chart as shown below.

Hovering over the bars will show us their respective values.We can also customize the existing chart by changing the colour, legend and font size by selecting the option below.Publish to Power BI OnlineWe can now publish the report from Power BI Desktop to, provided we have registered an account with Power BI. Click on ‘Publish’ from the Power BI Desktop designer.This will publish the report to Power BI Online.Heading over to Power BI online, we can view the report as shown below.SummaryThus we saw how to generate Power BI reports based on the data from the JSON file.

Power Bi Architecture Not Build Data In Real Time Money

It’s been a while since the last post about Power BI REST API. I have written previously about how to get the REST API, and use it in your application, how the authentication works, and things such as embedding content, and data source management. In this post, I’m going to explain one of the functionalities of REST API which is pushing data to Streaming data set. I’ve previously explained how to do real-time streaming using Azure Streaming Analytics and Power BI. However, this post explains real-time functionality just with the REST API. You won’t need azure services besides it.

If you want to learn more about Power BI, read. If you want to learn more about REST API, follow this series: PrerequisiteFor running the sample in this post, you need to have an application that is registered and followed the authentication process. If you want to see how it works, here are the two steps you have to have prepared as the prerequisite:You also need a Power BI account to create a data set in the service. Streaming DatasetIn Power BI, you can create different types of dataset.

The normal data set that everyone knows about it is a data set that automatically generates when you create a Power BI Desktop solution and publish it to the website. However, There is another very important type of data set, called Streaming data set. This dataset is used for pushing data into Power BI in a real-time scenario. How to create a streaming data setGo to Power BI Service website. Log in to the website with your Power BI Account.

When logged in create a new streaming dataset;There are three ways to create a streaming dataset. I have explained the. For this example, choose API;In the next part, is where you specify the name of your dataset, and add fields to it. Your fields can be of type DateTime, Text, or Number. With adding every new field, you will see a JSON format will be created underneath, which is the format that the data needs to be sent through REST API.Push, Hybrid or Streaming?One very important option to choose is the Historic Data Analysis. By default, this option is off.

It means the streaming data will not be stored anywhere. Service keeps up to 200,000 rows of data. When row # 200,001 comes in, the first row will be deleted. If you want the history to be saved, you have to switch this option to On.

When you turn this option your dataset from a streaming dataset, changes to be a Hybrid dataset.Streaming datasetOnly streams the data rows. Keeps up to 200,000 rows. First In, First Out. When row #200,001 comes in, row #1 will be removed. Cannot be used easily in reports, because there is no historical data. You can create a report with that, however, because it only focuses on streaming data. Not all options are available.Hybrid datasetStreams the data, and also keep the data rows.

You will have the rich experience of Power BI Report. Because data is stored, you can interact with that easier from a report.Push datasetCreated when you create a solution from Power BI Desktop with importing data into it. This dataset is not a streaming dataset. It just stores the data.

The data should be then scheduled to refresh. We are not talking about this type of dataset in this post.In this example, doesn’t matter which option to choose. Create a Dashboard and ReportAfter creating the dataset, you need to create a report with a simple line chart with value and timestamp on it, and then save it, and pin it to a dashboard. The dashboard’s chart at the moment will be blank because there are no data entries in it for now. In the next step, we will be adding rows into this data set from an application.

Connecting to the dataset from REST APIAs mentioned before, you need to prepare your application for this step as a prerequisite. In the application ready for this (after registration, and authentication), you can add few lines of code to pass this data through REST API. Here is a sample code. Majority of the code above is about finding the dataset (which I explained it in details in and posts). After finding the dataset, I used a loop (highlighted lines) to create a random number from 0 to 100, and pass it to the service and dataset with a function named PostRowsInGroupAsync. This process is in a loop and happens every second for 5 minutes.

As a result, now you can see the dashboard data changes; SummaryIn this post, you’ve learned how easy is to create a streaming dataset and interact with that with REST API. You’ve learned that there are two types of datasets that you can work with for streaming; Hybrid and Stream only.

One is storing data as well as streaming it. The other one is just streaming.

If you are interested to learn more about REST API, read blog series below. Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for eight continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.His articles on different aspects of technologies, especially on MS BI, can be found on his blog: wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group.

He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.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. Post navigation. Course Title: Power BI from Rookie to Rock StarLength:From 1 to 9 days (Lecture + Labs): depends on the modules you enrol.Delivery method:In-Person or Online: Check the schedule of upcoming courses.Type of training:Public or private (contact us for more details)You can join this course online if you are in other countries and cities. We use the reliable channel of GoToMeeting (which is like Skype) for the course.

You will be online at the same time as other students and instructor. We will have full-screen sharing, video and audio experience through GoToMeeting.

You would be able to ask any questions you have and get responses directly from the instructor, and even share your screen to ask a question from the instructor in case needed.The well-known worldwide training in Power BI field, and the most comprehensive Power BI training on the planet from one to nine days of training delivered by the well-known experts and MVPs, authors of books, and speakers of many conferences themselves. In this training course, you will learn Power BI from beginner to advance. You will learn how to use Power BI for simple data analysis situations as well as complex business intelligence scenarios. You will learn about Power BI Components such as Power Query (Get Data and Transform), Modelling and DAX, Visualization, Power BI Desktop as the main tool, Power BI Service, Gateway configuration, and architecture. You will learn all the concepts with live demos.

Expect learning best practices with great scenarios in this course. This course is designed in separate modules based on the type of audience. If you are a data analyst, data wrangler, data modeler, or data architect, or even a data scientist, this course has many things to teach you all.This course is delivered to thousands of people all around the world, check out only a few of the recommendations at the bottom of this page, and check some of our clients.Instructor: Reza RadOur trainer is the world well-known name in the Microsoft BI field. Is, a speaker in world’s best and biggest Microsoft Data Platform, BI and Power BI conferences such as Microsoft Ignite, Microsoft Business Applications Summit, Microsoft Data Insight Summit, PASS Summits, PASS Rallys, SQLBits, TechEds, and so on. He is the author of books on this topic, and he has more than 15 years’ experience in the Microsoft BI technologies. Reza is the founder of RADACAD and a consultant for more than decades. He is also a Microsoft Certified Trainer for years.

He is (Most Valuable Professional) focused on BI and Data Analysis; Microsoft has awarded him MVP because of his dedication and expertise in Microsoft BI technologies from 2011 till now (more than eight years). He is the author of.Power BI from Rookie to Rock StarThis is the most comprehensive course for Power BI on the planet which split in modules.

You can enroll in any of these modules separately or take the whole course. Modules designed independently, which means each module can be taken regardless of the order of modules. Here are a list and detailed agenda of each module:. Module 1: Power BI for Data Analysts (2 days). Module 2: Power BI for Data Wrangler and Transformer (2 days).

Module 3: Power BI for Data Modeler (2 days). Module 4: Power BI for Architects (1 day). Module 5: Power BI for Data Scientists (2 days)Module 2: Power BI for Data Wrangler/Transformer (Data Transformation) – 2 days courseThis training is designed for data wrangler, data developer or data transformer, who have the raw data coming from one or more data sources and want to make it ready for further modeling and analytics. In this training, you will learn detailed data transformation practices of Power BI using Power Query.In this course, you will learn all things about Power Query, from zero to hero. You will learn Power Query from basic level with Getting data from different data sources.

You will learn about different types of transformations available in Query Editor. Table transformations such as Pivot and Unpivot will be discussed, as well as specific column transformations such as date column transformations. You will also learn about M (Power Query Formula Language) in deep. Unique features such as error handling, generators, structured columns, custom functions and many other advanced level features of Power BI data transformations will be explored through hands-on labs and lecture.After this course, you will be able to implement any types of data transformation through Power Query in Excel or Power BI. You will be able to work through your raw data and make it ready for modeling and analytics.The training includes but not limited to topics below:2.1: Get DataIn this section, you will learn about Power Query basics which starts with Getting data.

You will learn that Power Query is the data transformation tool in Power BI. You will learn different parts of the Query Editor through an example of using Power Query to transform a dataset. Introduction to Power Query. Query Editor. Get Data from Web.

Basic Transformations. Get Data from Excel. Use First Row As Headers / Use Headers as First Row. Get Data from SQL Server2.2: Data Types and Data StructuresBefore going any further in learning Power Query, you need to understand about data structures and data types. There are three main data structures in Power Query; table, record, and list.

You will learn about these types through an example of getting data from a JSON structure. You will also learn about data types and their differences. Base data structures in Power Query. Get Data from JSON. Transforming Table, Record, and List. Data Types in Power Query. Query Operations.

Enable Load; Performance Boost. Query Operations; Duplicate, and Reference2.3: Combine QueriesOne of the most common data transformations is combining datasets. Depends on the types of datasets and the way that they are related to each other, you may want to merge or append them.

In this section, you will learn why you need to combine data at first, and then you will learn about scenarios that you combine data in Power Query. Dimensional Modelling; Designing the data model. Append, creating a single big query of the same structure. Merge; Joining queries when the structure is different. Join types in Merge. Tips to consider after Merge or Append2.4: Better Power Query Editor ExperienceTo get the best experience with Power Query Editor, you need to consider organizing your queries and steps in the right way. In this section, you will learn about actions you can do on steps, such as moving them up or down, splitting steps in a query, etc.

You will also learn about organizing your queries in groups (folders). Groups; Folders in Query Editor. Steps Operations. Splitting query steps. Moving steps up or down. Add as new query / Drill Down.

Be Careful of Actions; Undo!2.5: Reducing Number of Rows; FilteringFiltering rows in Power Query is an important transformation especially when the dataset is big, or when the data needs to be cleaned. There are different ways of doing filtering in Power Query.

You will learn about ways to remove some rows from the top or bottom of the table, and ways that you can filter a data table based on criteria. You will learn about basic filtering and the difference of that with the advanced filtering, and potential challenges that you may have through this process.

Row Operations; Removing rows. Row Operations; Keeping rows. Remove/Keep Errors. Remove/Keep Duplicates. Using Remove/Keep combination for troubleshooting report. Filtering based on Individual values.

The dilemma of the basic filtering. Advanced Filtering. Sorting2.6: Column OperationsA data table in Power Query can get big if you don’t care about columns. In this section, you will learn actions that you can do on columns, and what are best practices to make sure you have the best performance in your Power BI model considering columns in your tables.

You will also learn about some generic column operations and transformations. Column Operations. Choosing Columns.

Removing Columns. Data Type Change. Locale consideration for the data type. Replace Values. Fill Down/Up; Very Useful for Excel2.7: Table TransformationsSome of the most important table transformations will be explained in this section. You will learn about a way to change the granularity of a table; Grouping.

You will also learn scenarios that grouping data can be more than a simple transformation. You will learn about transformations such as Transpose, Pivot and Unpivot, and the difference of all these items with scenarios of using it on real-world datasets. Group By; Changing the granularity of the data table.

Group by Advanced. Scripting and Group by; First and Last item in each group. Transpose; rows to columns and reverse. Pivot; changing the name-value structure to columns. Unpivot; changing the budget column structure to rows2.8: Text TransformationsWhen you work with text values, there are many transformations you can apply.

Transformations such as a split column, removing part of a text, or adding a prefix or postfix to it, concatenating some columns together, etc. Split Column by Delimiter. Split Column by number of Characters. Split into rows instead of columns. Merge (Concatenate). Format. The difference between Clean and Trim.

Parse (XML or JSON). Extract part of the text2.9: Numeric TransformationsYou will learn in this section how to do numeric transformations. We will talk about standard transformations such as divide and integer-divide. You will also learn about transformations such as rounding, statistics transformations, and even some scientific transformations and use cases for those items. Standard transformations; Divide, Integer-divide, Multiply, Add etc. Scientific transformations; logarithm, power square, etc. Statistics transformations;.

Rounding. Information functions; Is Even, Is Odd, and Sign. Dealing with faults in Numeric calculations2.10: Date and Time TransformationsThere are many transformations applicable to date and time columns.

You may want to fetch year part of a date or get the fiscal calculation of a date. You may want to calculate age based on the birthday or calculate the difference between two dates.