Anupama Natarajan

Lesson 13 - Line chart in Power BI Desktop

2023-12-13T06:00:00.013+13:00

A line chart is a type of graph that shows how something changes over time. Data points in the line chart represent the individual values at specific points in time or in a continuous interval. These data points are then connected with lines to illustrate how the values change or trend over the period.

How to Proceed?

Step 1
Launch power BI desktop app and open the new report page and import the data required.

Refer Lesson 7 – Power BI Datasets to build great visuals

Step 2

In “Visualizations” pane click on “Line chart” which is highlighted in the given figure.

Step 3

Drag the data fields into “Field Section “that we want to compare and analyze.

X axis: Represent categories – Year

Y axis: Shows the Count of medal

Legend:

The legend represents the different data series or categories which helps us to understand which line corresponds to which data elements.

Here we are showing different types of medals (Gold, silver, bronze) count by Year.

Step 4:

Filters in Power BI allows us to control and shape the data as per our needs. Here we are showing the page level filter where we can display only the data we want to show in reports or dashboards. By slicing and dicing the data we can gain insights from it.

Here, we used filters in medal (Filtered only Gold, Bronze and Silver) and in Region (Top 10 countries by medal count).

Step 5:

Customizing the appearance of Line chart

You can customize the appearance of the visual. You can change Title, Font size, Style, Colours and Data labels.

Click anywhere on the visual and set the below properties in the Format section.

Refer Lesson 6 - Formatting the visuals in Power BI Desktop

In this visual, I chose background colour as blue and gave 87% transparency which reduces the intensity of the colour.

Give suitable title for the visual and customize the font size, colour and position of the title.

Chose colours for the line to show different categories.

To add data labels to the Line chart, click on the “Data Labels” section under “Visual”. Here you can choose which data to display in the labels and adjust their position, font, and color.

In this visual, to place the position of data labels. Under formatting visuals, click on data labels and choose the desired options.

Here for this visual, we used “Above” option to display the data.

Step 6 Save the visual

Finally, your Line chart is ready. Click save button to save the visual.

When to use Line chart?

Line chart are most appropriate when we want to visualize trends, changes over time, and to display continuous data, compare different categories

Pros

Line charts are generally simple and easy to understand the visual which makes easily accessible to everyone
It is great in showing changes of data over time and patterns in a clear manner.
In line chart we can clearly identify when the data goes up or down which helps in spotting important changes

Cons

Line chart can be used only to show changes over time, it will not be suitable to show other types of data
With too many data points, the line chart become clumsy and difficult to read the data

Conclusion

Line charts are great for showing trends or comparing things over time, but it may not work well for all types of data.

Tags	Power BI
Useful links	https://powerbidocs.com/2020/01/25/power-bi-line-chart-visualization//
MS Learn Modules	Visualization types in Power BI - Power BI \| Microsoft Learn
Test Your Knowledge	Quiz

Lesson 12 - Smart Narrative in Power BI Desktop

2023-12-12T06:00:00.001+13:00

Smart Narrative in Power BI allows you to auto generate human like narration to explain key factors, trends and insights about the data. It helps you provide quick summary of visuals which can also be customized.

Here for Stacked Area chart visual I created a smart narrative.

How to Proceed?

Step 1

To create smart narrative about any visual, Right click anywhere on the visual and select summarise.

Step 2 Editing Smart Narrative

The smart narrative is now created. It can be customized based on the requirements.

We can add dynamic values or own values to the summary. Click on the smart narrative and

Click “+” sign to add dynamic value.

We can use our natural language to set up any dynamic measure value to get updated to the text. It is more like adding our own calculations without using DAX to display in the text

We can name the value so that we can use the value name whenever required.

We can also customize the value by showing values in currency, decimal number etc. We also have the flexibility to choose separator for thousands.

The smart narrative visual is dynamic. If we apply slicers or cross filter it will automatically update the generated text and dynamic values in summary.

We can also edit the dynamic value created in Review tab. We can see the list of dynamic values listed in Review tab.

Toggle Auto generated values to ON to display auto generated values

Sometimes we may see some hidden summary symbol in smart narrative which means there is current data and filters doesn’t produce any result for the value.

Add smart narrative icon to the visual

Click anywhere on the visual and in the format pane choose general.

Expand Header icons and set smart narrative to ON to add Smart narrative icon to the visual

Click on the “Triple dot “button on top right of the visual. Now you can see the smart visual icon added to the header icons of the visual.

Now we can easily create summary of the visual by clicking on the icon

We cannot create summary for all types of visual.

Step 6 Save the visual

Finally, your Smart narrative is ready. Click save button to save the visual.

When to use Smart Narrative visual?

When you want to generate human like narratives to write a summary about created visuals very quickly, we can go for smart narrative.

Pros

Smart narrative is great time saver which generates summary about visuals in a jiffy.
It gives instant textual insights.
Narrative content updates dynamically when users interact with slicers and filters.
User friendly

Cons

Limited Customization
Smart narrative does not work with all visuals.

Conclusion

Smart Narrative is a powerful feature in Power BI that enhances the storytelling capabilities of reports by automatically generating human-like narratives based on data visualizations. While it offers convenience, users should also be aware of its limitations. It serves a best option for an experienced data analyst or users who is new to data analytics.

Tags	Power BI
Useful links	https://www.sqlshack.com/generating-smart-narratives-in-power-bi/
MS Learn Modules	https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-smart-narrative
Test Your Knowledge	Quiz

Lesson 11 - Card (New) Visual in Power BI Desktop

2023-12-11T06:00:00.012+13:00

Microsoft released this new card visual in June 2023 with some added features to the existing card visual. This new card visual can able to show multiple metrics and can be grouped into a single container.

Here I showed Medal count of Top 5 Regions with their country flag

How to Proceed?

Step 1

Launch power BI desktop app and open the new report page and import the data required.

Refer Lesson 7 - Power BI Datasets to build great visuals

Step 2

In “visualizations” pane click on “Card (New)” which is highlighted in the given figure.

Step 3

Drag the data field into “Data Section” that you want to display.

In the above visual, I have displayed No of Medals by Top 5 Region as Card. In this card visual we are showing the Count metrics in card. we can also show financial metrics, sales metrics, percentages etc.

The card showing Top 5 Countries based on their medal count. I created 5 card new visual with medal count and grouped together.

You can also apply filters on the card visual.

Step 4

Customizing the appearance

You can customize the appearance of the card visual. Click anywhere on the visual and set the properties in the Format section. Compared to the old card visual, the new card has many formatting option. Let’s look into that in detail

Refer Lesson 6 - Formatting the visuals in Power BI Desktop

Shape: Expand Shape and we can format the shape of the card. Here we have the ability to customize the shape corners.

For this visual, I Applied Rounded rectangle.

Expand Callout option, we can format the font style, size, color of the callout value displayed in the card

Expand label for formatting labels in the card. Spacing can be customized to adjust the distance between the label and card value

One of the best added features of new card visual is that we can add image to the cards which looks more appealing.

Here for our card visual I have added Countries Flag image to represent Region with their medal count.

For card visual they added a feature called Accent bar a vertical line which can be added to the card and it can be customized with these options.

Step 5 Save the visual

Finally, your Card Visual is ready. Click save button to save the visual.

When to use Card visual?

New card visual can be used when we need to show multiple metrics adding some more interactive features.

Pros

Simple and straightforward visual.
Card visual represents focused presentation of key data points
It supports interactive features
Easy to understand
Can show multiple metrics in single card

Cons

Since this is in preview there will be some limitations.
While Applying background effect, glow effect to card visual ,shape of the card changes

Conclusion

Card visual in Power BI is a wonderful tool presenting focused and specific metrics, providing a clean and readable representation of KPI’s. It may not be suitable for every reporting scenario, especially for those requiring complex visualizations or a more exploratory approach to data analysis.

Tags	Power BI
Useful links	https://www.geeksforgeeks.org/power-bi-how-to-format-a-card/
MS Learn Modules	https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-card?tabs=powerbi-desktop
Test Your Knowledge	Quiz

Lesson 10 - Slicers in Power BI Desktop

2023-12-10T06:00:00.004+13:00

Power BI slicers are most effective and important visual element while creating Power BI Reports. Slicers are nothing but a filter which is more interactive. By using slicers, we can deeply analyze the specific data in reports. We have various formatting options for slicers. In this blog, we can see how to create and format the slicer.

How to Proceed?

Step 1:

Launch power BI desktop app and open the new report page and import the data required

Refer Lesson 2 – Introduction to Power BI Desktop

Step 2

In “visualizations” pane click on “Slicers” which is highlighted in the given figure

Step 3

“Field” is the only section in Slicers. We can add various columns of our dataset in the slicer. Here we used Region field as a slicer

Step 4: Formatting a slicer

After creating a slicer, it can be formatted as per our requirement.

Two types of settings under slicers

Visual Settings
General Settings

Click anywhere on the slicer, there are 3 types of options available under Visual settings

Visual Settings

Slicer Settings

Under slicer settings, we have options to style the look and feel of filters and options to select the type of selection we want to apply the filter.

Single select
Multi-select with CTRL
Show “select all” options

Single Select: when we turn on this, we can able to select only one option from the listed values

Multi-select with CTRL: When we turn on this, we can select multiple values from the listed values. This can be allowed only by clicking CTRL command while selecting.

Show “select all” options: When we turn on this, it will give select all option to the listed values

We have different options for displaying the filter values in different styles. It depends on the fields we choose for Slicers.

For category slicers we have these list of style options. In this Region slicer I have selected Dropdown option

For Numeric slicers we have these list of style options

For date Slicers, we have these list of style options. By default, for dates the style will be “between”

Slicer Header

Slicer Header is the heading of the slicer and not the header of visuals.If we want to give

heading to the overall slicer visual, We can give separately under Title section.We have following options under Slicer Header. Text,Border,Background,Pending Icon.

Text

Click on the Text option. We can customize the slicer title ,its font and the font color.Under font we can customize the font style of the title and can make it bold, italic or underlined

Border

Click on the Border option. We can able to add borders to the slicer heading. We can give borders to the slicer, we can add border color and the thickness of the border line can be customized.

In this slicer we are not using any border.

Background

Click on the Background option, here we can set the background color of the slicer. We can set the background by conditional formatting.

Pending Icon

In Power BI we have an option to “Apply all slicers” and “Clear all slicers” which is added recently in power BI update which will be useful to work on slicers in a single click. It is mainly added to make power BI more efficient by performance.

Pending icon will display only when Slicer visual header is turned on. Pending icon will appear with some customized text if the slicer is not applied to all visuals. We can customize that text with these settings like color, transparency, position of the text.

We can customize the text in “Label Text” space in pending icon section

Values

Click on the Values option. Like we discussed about slicer features above, we have different options under values where Font can be used for changing slicer value sizes. Border for the slicer values. We can also change the Font color and its background.

General formatting

We have multiple options under general formatting like properties, title, effects, header icons, alt text. Let’s discuss more about each of these options separately

Properties: Under this we can adjust size, position of the slicer

Title: Here we can name the slicer, change its font style color etc.

Effects: Under this we have options to set background color of the slicer, we can give border to it and we can give a shadow setting here.

Header icons

Under this we have options to set background color, color of the icon and the transparency

Alt text

Alt text option is available for all visualization element in power BI. This is used for people who cannot see the visuals or images. We can type the text we want to read it aloud by the system. It will be useful for blinds or with people with low vision. we can type up to 250 words.

Button Slicers (New)

We have new slicer with added feature was added to the power BI visuals

Step 1

In “visualizations” pane click on “Slicer (New)” which is highlighted in the given figure

Like the old slicer, for this new slicer we require single field to display the content

Here I have chosen Continent field for this button slicer

In this new slicer, the data's are represented as buttons, and the shape of the buttons can be customized which gives us more control on appearance of this visual.

We can align the values of the slicer and also can adjust the grid space and space between two buttons.

We can adjust the appearance of the button overflow style using these options and also direction of overflow style i.e., vertical or horizontal

Paginated

Continuous Scroll

We have option to insert image into this new slicer which makes the slicer more appealing. We have many upgraded options on this new slicer compared to previous slicer which makes more interactive and appealing.

Clear selections

We can easily clear our slicer selections using the “Eraser Icon” from the header icons which makes it simple to clear the selections which is not available in the Normal slicer.

When to use Slicers?

Slicers are mainly used to filter the reports across every page to maintain same context across all the pages with different charts, tables etc. and also to explore different aspects of data

Pros

Slicers are more interactive which is more engaging and better user experience.
We can explore different aspects of data without using complex filters
We can create dynamic reports with slicers on the fly

Cons

In Reports with multiple visual, screen space will be occupied by using slicers instead of filters.
In reports with large dataset usage of slicers leads to influence on performance

Conclusion

Slicers are very interactive and user-friendly visual in Power BI, enabling efficient data analysis and exploration. Customization options, including the ability to format slicers and adding customizable text enhances their adaptability to various reporting needs.

The upgraded slicer in Power BI with enhanced customization, dynamic interactivity, extended filtering capabilities, and seamless integration with icon headers, the new slicer is a powerful tool for unlocking the full potential of our data visualizations.

Tags	Power BI
Useful links	https://www.geeksforgeeks.org/power-bi-slicer/
MS Learn Modules	https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers?tabs=powerbi-desktop https://powerbi.microsoft.com/en-us/blog/new-slicer-button-public-preview/
Test Your Knowledge	Quiz

Lesson 9 - Multirow card Visual in Power BI Desktop

2023-12-09T06:00:00.013+13:00

Multirow card helps us to display multiple data points in a single card which helps users to get quick insights by comparing multiple values across different categories. Let’s discuss about how to create Multirow card in this blog post.

How to Proceed?

Step 1

Launch power BI desktop app and open the new report page and import the data required.

Refer Lesson 7 - Power BI Datasets to build great visuals

Step 2

In “Visualizations” pane click on “Multirow card” which is highlighted in the given figure.

Step 3

Drag the data field into Fields that you want to display in multirow card visual

Here I showed No of Medals, Region and Sport

Step 4

Customizing the appearance

You can customize the appearance of the multirow card visual. Click anywhere on the visual and set the properties in the Format section.

Refer Lesson 6 - Formatting the Visual in Power BI Desktop blog post for general formatting

Callout value is the key content of the card visual. Expand Callout option, we can format the font style, size, color of the callout value displayed in the card

Category label is the text written below the key content. Expand Category Labels option, we can format the font style, size, color of the Category Labels displayed in the card

Style option has multiple options like Border position, background color, padding.

Accent bar is the vertical line appear at each sub group of multirow card. We can adjust the width of the bar and also color of the accent bars can be customized.

Step 5 Save the visual

Finally, your Multirow Card Visual is ready. Click save button to save the visual.

When to use Multirow card visual?

Multirow card visual can be used when we want to showcase multiple data points and compare the data points in a structured way.

Pros

Helpful in decision making
Multirow card visual represents focused presentation of key data points
Multiple data categories can be shown in a unified space.
Easy to understand

Cons

Customization in multirow card is limited
Less Interactive

Conclusion

Multi-Row Cards in Power BI presents complex data in a clear and concise manner. Multirow cards can be incorporated into your Power BI dashboards to elevate the data analysis, which helps users to make better decision making.

Tags	Power BI
Useful links	https://www.geeksforgeeks.org/power-bi-format-multi-row-card/
MS Learn Modules	https://powerbidocs.com/2020/01/25/power-bi-multi-row-card-visualization/
Test Your Knowledge	Quiz

Lesson 8 - Card Visual in Power BI Desktop

2023-12-08T06:00:00.013+13:00

The Card Visual in Power BI is the straightforward visual designed for displaying single data points in a visually appealing way. Card visual display specific measure which is considered to be key metrics of the dataset which helps in highlighting important KPI’s (Key Performance Indicator) of reports.

How to Proceed?

Step 1

Launch power BI desktop app and open the new report page and import the data required.

Download Raw files - https://github.com/shantha05/PowerBILessons/tree/main/RawFiles

Download .pbix files - https://github.com/shantha05/PowerBILessons/tree/main/pbixfiles (.pbix file populated with Raw data ready to use for visualization)

Step 2

In “visualizations” pane click on “Card” which is highlighted in the given figure.

Step 3

Drag the data field into “Field Section” that you want to display.

In the above visual, I have displayed No of Regions as Card. I renamed that Field name as Countries. In this card visual we are showing the Count metrics in card. we can also show financial metrics, sales metrics, percentages etc.

The card showing No of regions participated in Olympics

You can also apply filters on the card visual.

Step 4

Customizing the appearance

You can customize the appearance of the card visual. Click anywhere on the visual and set the properties in the Format section.

Refer Power BI Formatting blog post for general formatting

Select the card expand Callout value and change font style, size etc. and also format the category label

Expand Category label and change font style, size etc.

Step 5 Save the visual

Finally, your Card Visual is ready. Click save button to save the visual.

When to use Card visual?

Card Visual can be used to show straightforward key data points such as KPI’s or key metrics without any need of complex visualization. Also, we can show summary of data points in a easy clear manner

Pros

Simple and straightforward visual.
Card visual represents focused presentation of key data points
It supports interactive features
Easy to understand

Cons

We cannot show multiple metrics in single card
Cannot add additional information to the data card
May be less attractive compared to other interactive visuals

Conclusion

The Card visual in Power BI is a wonderful tool presenting focused and specific metrics, providing a clean and readable representation of KPI’s. It may not be suitable for every reporting scenario, especially for those requiring complex visualizations or a more exploratory approach to data analysis.

Tags	Power BI
Useful links	https://www.geeksforgeeks.org/power-bi-how-to-format-a-card/
MS Learn Modules	https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-card?tabs=powerbi-desktop
Test Your Knowledge	Quiz

Lesson 7 - Power BI Datasets to build great visuals

2023-12-07T06:00:00.008+13:00

Power BI is a powerful tool for data visualization and analysis, and it provides several options for creating different charts. In this blog post, we will explore how to create different charts in Power BI and how to customize its appearance.

Dataset Used:

I used raw dataset “120 years of Olympic history: athletes and results” from Kaggle and transformed for further analysis. I added additional data along with this dataset to cover all visuals in Power BI.

Context:

This is the historical dataset on Olympic games from Athens 1896 to Rio 2016.It has 2 Files. NOC Region file with distinct 230 rows with 4 rows and Olympic Events file have 268,768 rows with 16 columns. Winter and Summer Games were held in the same year until 1992. After that, they changed like Winter Games and Summer Games occur on a 4 year cycle starting with Winter in 1994, then Summer in 1996, then Winter in 1998, and so on.

It also has information on events, sports they participated, NOC (National Olympic committee) regions. From this diverse information on data set we can analyze the data based on various aspects like country’s performance, athlete performance, Evolution of women participation in Olympics every year.

Field Description:

Olympic Events-File 1

ID- Unique Identification number for each Athletes (Integer).
Name- Name of the Athlete (Text).
Gender- Gender of the Athlete(M/F) (Text).
Age- Integer representing the Age of the Athlete (Integer)
Height- Height of the Athlete in cms (Integer)
Weight-Weight of the Athlete in kgs (Decimal)
Team- Name of the Team under the Athletes Participating.
NOC – National Olympic Committee (3 letter code) representing specific region (Text)
Games- Year and season (Winter/Summer) (Text)
Year- Year Olympic held (Integer)
Season -Summer/Winter (Text)
City – City where Olympics Hosted (Text)
Sport- Sports Information (Text)
Events – Events Information (Text)
Medal -Medals Secured (Gold or Silver or Bronze or NA) (Text)

NOC Region – File 2

NOC- National Olympic Committee (3 letter code) representing specific region (Text)
Region – Name of the region
Notes- Additional Info
Continent – Name of the continent

Shape Map – File 3

It has Australia's population data as of 2023

Change over previous year ('000) - Change in population compared to previous year (in numbers)
Change over previous year (%) - Change in population compared to previous year (in percentage)
Population at 31 March 2023 ('000) - Population data as of 31st march 2023 (in numbers)

Medal Target - File 4

I have fixed some random targets for medal counts

Medals Won - Medal count by region
Region - Name of the region
Target - Target by region

Importing Dataset into Power BI

Step 1: Launch Power BI desktop. Import the Data files into Power BI

Step 2: Import the data Source to the Report

Step 3: Once we import the data into power BI, the below dialog box will get opened.

Load : Here at the very first step, Load option is where you bring the data directly into Power BI to create a data model without any transformations and start building visualizations and reports

Transform Data: Transform Data is the step where we can shape, clean, and manipulate the data before loading it into Power BI. When we connect to a data source, Power BI gives us with a Power Query Editor window. In this window, you can perform various data transformation tasks, such as:

To rename columns and Tables
Remove Unwanted columns
Filtering Rows
Splitting, Merging rows
Data type Conversions
Creating Custom Calculations and so on.

These transformations help to make sure the data is in the desired format and making it more suitable for analysis in Power BI. Once we have finished transforming the data, we can load the data further.

Note: Once the transformation is done make sure all the filters applied are cleared. Only filtered rows will get loaded if any filters are applied.

Data Preparation & Cleaning

In the above dataset we made some basic transformations using Power Query Editor.

As Age, height, weight are numerical columns. Replaced those null values by zero.

In medal column, empty cells are replaced as “No Medal”.

Removed row duplicates

To replace Null values as NA or 0

To remove duplicates

To change Datatypes

To Extract values from the column

Once the data comes into desired form as per the requirement, we can load the data and start working on visuals.

Once we load the data into power BI, data model will get created.

We can view the data model by clicking on “Model view” on the left side pane.

The relationship created was “Many to one relationship” as per our dataset

NOC is the common field in both the table.

We can further edit the relationship or to make any data model related changes we can click on the arrow button above

Under the option Cross filter direction, we have two options

· Single directional filter

· Bi-directional filter

Single -In a single-directional filter, the filtering flows in only one direction. when you apply a filter in the one table, it will affect the related data in the other table, but not vice versa.

Both - In a bi-directional filter, the filtering can flow in both directions. When you apply a filter in either the one or other table it will affect the related data in both directions.

Lets create visuals based on the above dataset in further blogs. Get ready !

Download Raw files - https://github.com/shantha05/PowerBILessons/tree/main/RawFiles

Download .pbix files - https://github.com/shantha05/PowerBILessons/tree/main/pbixfiles

Lesson 6 - Formatting the Visual in Power BI Desktop

2023-12-06T06:00:00.015+13:00

You can set visual Title, Font size, Style, Colors and Data labels. Click anywhere on the visual and set the below properties in the Format section.

Format section has two main purposes.

General
Visual

General

In Power BI, the "General" section within the "Format" pane provides essential options for customizing and fine-tuning the selected visual's appearance. It includes fundamental settings that impact the overall look and behavior of the visual.

The “General “section includes options such as

Properties – In the "Properties" section, you can adjust the size of the visual and its position on the page, along with adding padding to ensure it fits perfectly within your report layout.

Title –The "title" in the "General" section allows you to name the visual and change how the title looks. You can make the title bigger or smaller, choose its color, and decide where to put it on the report page. It helps you create a clear and attractive report with visuals that have clear names and styles.

Effects – In the "General" section, you can add special effects to your visualizations. These effects make your visuals look more interesting and attractive. For example, you can add background color, shadows, reflections, or glowing highlights to make your data stand out and catch the viewer's eye.

Header icons –Header icons refer to small graphical symbols or images placed at the top of a visual. These icons serve various purposes and provide quick access to different functionalities and actions within the Power BI service. For example, you can apply filters, go into focus mode, export the visuals.

Tooltips –In Power BI, tooltips are like little boxes that pop up when you move your mouse over a data point or item in a report. They show extra information related to that data, helping you understand it better. Tooltips are a handy way to get more insights without overcrowding the main visual, making data exploration easier and more informative in your reports and dashboards.

Tooltips can be customized to show relevant details, such as the exact values, percentages, or descriptions related to the data point.

Alt text-In Power BI, alt text (alternative text) is a brief description that can be added to images and visuals. It provides a text-based representation of the visual content, making it accessible to people with visual impairments or those using screen readers.

Visual

The "Visuals" option provides you with various settings to customize and fine-tune the appearance and behaviour of the selected visual element in your report or dashboard. This section provides various settings specific to the type of visual you have chosen.

Some common options you may find in the "Visuals" section include.

X-Axis and Y-Axis- You can format the labels, titles, and gridlines of the X and Y axes to make the visual easier to understand.

Title – Add or edit the title for X & Y axes and can adjust its font size, color and alignment.
Axes Type – Choose between “Categorical” (Text based fields) or “Continuous” (numerical data) for both axes.

Legend – You can show or hide the legend, which explains the colors or categories used in the visual. Customize the position of the legend, adjust the font size, color and can add title to the legend.

Gridlines – Customize the appearance of gridlines (Dotted, Solid, Dashed).

Zoom slider - It allows you to adjust the level of zoom or magnification applied to a visual or data visualization. By using the zoom slider, you can focus on specific parts of the visual to see more detailed information or zoom out to get a broader view of the data.

Data Labels- You can choose to show or hide data labels on the visual to display the actual values directly on the elements. You can control the position of data labels to avoid overlap and enhance readability.

Plot area background - The plot area is the space where the actual data is displayed in the visual, excluding the axis, titles, and other elements around it. You can choose to set a solid color as the background or use an image to create a more visually appealing and informative visual representation.

Customizing the plot area background helps you align the visual with your report's theme and design, making it more engaging and effective in conveying insights from your data.

Useful Information

Tags	Power BI
Useful links	Format Visual in the New Power BI Desktop - Radacad
MS Learn Modules	Getting started with formatting report visualizations - Power BI \| Microsoft Learn
Test Your Knowledge	Quiz

Lesson 5 - Introduction to Power BI Themes

2023-12-05T06:00:00.002+13:00

Power BI offers an incredible feature that allows users to personalize the look and feel of their reports and dashboards using themes. With these themes, you can make sure your reports have a consistent and polished look by using the same branding and design elements throughout.

With themes, you can easily make your data visualizations more appealing and create a professional and impressive presentation for your audience.

What is Power BI theme?

In Power BI, themes are collection of JSON (JavaScript Object Notation) objects that defines the visual elements of the report such as color, fonts. A JSON file is a set of instructions that tells Power BI how to make your reports and dashboards look.

Themes in Power BI offer a convenient way to change the overall style of a report or dashboard quickly. Users can customize colors, typography, and visual styles to create a professional and aesthetically pleasing representation of their data.

Power BI Themes provide users with the flexibility to design eye-catching and informative data visualizations effortlessly.

Types of Power BI themes

There are two main types of Power BI themes. They are.

Built in Themes

Power BI provides users with a variety of built-in themes that can applied instantly to change the appearance of their dashboards. These themes contain pre-defined combination of colors, fonts and styles, making it easy to create professional look with just few clicks.

Custom Themes

Unlike built-in themes, custom themes are created by the users. Custom themes in Power BI allows users to personalize the design of their visuals based on their specific preferences or their branding needs.

How do I use Built-in themes in Power BI?

Follow the steps to use Built-in themes.

Launch Power BI Desktop
Navigate to the View tab and click on the drop-down arrow at the end in Themes section.

Select a suitable theme for your report from Built-in themes. It will apply to your dashboard instantly.

In the provided illustration, you can see a simple dashboard, adorned with one of Power BI's ready-made themes, giving it a distinct visual appeal.

How to create/Customize a new Theme?

To create new theme, open Power BI Desktop and navigate to the “View” tab.
From there, select “Themes” (click on the drop-down arrow), click on “customize current theme” option.

Then Customize theme page will open where you can select colors of your choice.

Name and Colors There are eight primary colors, customizing these eight primary colors allows you to maintain consistency throughout your report and align it with your branding or design preferences.

Text Power BI enables users to customize font styles, size and color for general text in visuals, Titles, and also for table headers to ensure consistency throughout your report.

Visuals Power BI allows users to customize the appearance of the visualizations, such as background color, border, header and tooltip.

Page Power BI allows users to customize the overall layout and style of their report pages which includes wallpaper and page background.

Filter Pane Power BI enables users to customize the appearance of the filter pane, which is important element for interacting with data in reports and dashboards. Users can modify the colors, fonts, and styles of the filter pane to match their chosen theme.

Click Apply. Your customized theme is applied to your report.

In the provided illustration, you can observe a simplistic yet visually appealing dashboard, where a custom theme has been applied to enhance its distinct look.

How to Save/Export the customize theme?

In addition to customizing the theme feature, Power BI allows users to save the tailored theme for future use. This feature allows users to effortlessly access and apply their custom themes across different reports and dashboards.

Again, navigate to View tab, select “Save current theme” option in Themes section.

After customizing the themes according to your preferences, Power BI allows you to export them as JSON files.

These JSON files can be conveniently saved and utilized in the future for other reports. Additionally, this feature enables seamless sharing of themes with colleagues by sharing the JSON files, ensuring consistent branding and design elements across team projects in Power BI.

Note: You can also create theme (JSON file) using PowerBI.tips

Theme Gallery

The Theme Gallery in Power BI is a built-in collection of pre-designed themes that users can access and apply to their reports and dashboards.

With the Theme Gallery, users can easily explore and select themes that align with their desired look and feel.

Theme Gallery also allows users to share their custom themes with the community, contributing to a collaborative ecosystem of theme creators.

On the View tab, select drop-down arrow in Themes section -> Theme Gallery.

The following link will open Themes Gallery - Microsoft Fabric Community. Within the Theme Gallery, you'll find a collection of pre-designed themes.

Select the theme that best aligns with your desired look and feel. Download the JSON file associated with the chosen theme.
Now you can import the theme you want.
If you have created a customized theme in Power BI and want to share it with others, you can do so by clicking on the "Submit a report theme" button. However, before submitting, you need to log in with your Microsoft credentials.

How to import theme files?

Power BI offers the capability to import theme files, making it easier to share and reuse customized themes across different reports and projects.

On the View tab, select drop-down arrow in Themes section ->click Browse for themes.

A file dialog box will appear, allowing you to browse your computer and find the JSON file of the theme you want to import.

Select the theme that you want to apply to your reports and click "Open."
Power BI displays following dialog box to confirm that your theme file is added successfully to you report.

As soon as you import the theme, Power BI will immediately apply its colours, fonts, and styles to your current report or dashboard.

Useful Information

Tags	Power BI
Useful links	PowerBI tips
MS Learn Modules	Use report themes in Power BI Desktop - Power BI \| Microsoft Learn\| Microsoft Learn
Test Your Knowledge	Quiz

Lesson 4 - Introduction to Power BI Templates

2023-12-04T06:00:00.005+13:00

Power BI templates are pre-designed report layouts that can be saved and reused, which helps in consistent and efficient report creation.

Creation of Power BI templates involves designing and configuring a report or dashboard layout, visuals, data connections, applying themes which acts as a base for efficient and consistent report development.

Why Power BI Templates?

Consistency: Power BI templates allow us to create standardized report layouts, visuals, and data connections which ensures that all reports created from the template follows same design principles and formatting guidelines, which provides consistent and professional look across all our reports.
Time Savings: Templates saves time by providing predefined structure of report which avoid creation of report from the scratch each time by reusing the templates eliminating recreation of common elements.
Collaboration and Standardization: Power BI templates can be shared within teams which ensures everyone is working with the same design standards. This promotes better collaboration and standardization
Efficiency and Productivity: Power BI templates helps in eliminating the need of manually recreate visuals and settings which in turn we can quickly generate reports by populating data with consistent formatting and Layouts.

Overall, Power BI templates provide a range of benefits, including consistent design, time savings, increased productivity etc.

In this Blog post we going to discuss more about Power BI layouts which comes under creating Power BI templates.

Power BI Layouts

The layout of our reports and dashboards plays a major role in effectively communicating insights to our clients. A well-designed layout can transform raw data into visually appealing stories. Here we going to explore different approaches of creating powerful layouts for Power BI Reports or dashboards.

Approach 1: Pre-designed Layouts

https://powerbi.tips/tools/layouts/ offers a collection of pre-designed layouts that can be downloaded and imported into Power BI. These layouts provide ready-to-use designs for various data visualization scenarios, saving time and effort in report creation.

By clicking on the required layouts based on our requirement we can download and import into power BI as (.Pbit files)

Click on the link https://powerbi.tips/tools/layouts/

Here I have used this free Pre-designed Layout “Layouts – Left Slicers with Logo” for my Power BI Report. There are also paid layouts available here.

Click on File option in Power BI Desktop, File  Import  Power BI template and import the downloaded Pbit file.

Note: After Importing Pbit file, Our Power BI layout will get opened as a New Untitled Power BI report. We need to populate data and create visuals according to the layout

Import data into the Power BI report. Below Screenshot shows the layout after loading data into Power BI

POWER BI LAYOUT

Below shows the final dashboard with 4 Quadrant predefined layout.

REPORT CREATED USING PRE-DEFINED LAYOUT

Approach 2: Custom Layout using Power Point

Power BI custom layouts can be beneficial for specific scenarios, even when there are pre-defined layouts available.

Custom Layouts can be used when we need a tailored design when predefined layouts do not align with some reporting needs. It can also use when organization already follows some standard reporting templates.

While pre-defined layouts in Power BI are easy and convenient for many users, custom layouts offer personalization and control that cannot be achieved with standard templates.

Open Power Point app, Click on the Insert New Blank Slide

Go to Insert tab and Click on Insert-->Shapes-->Desired shape

Create the layouts using shapes as per the requirement, click on home tab and select color and outline of the shape for further formatting options

We can give effects to the shape using Shape effects options. We can give any effects like shadow, glow etc. as per our requirement. Click on the Shape -> Shape Effects--> Shadow

Finally, the Report layout looks like this. We can save this as .png file and we can use this as a canvas background in Power BI

Save the layout as PNG file format

Open Power Desktop. Click on the empty canvas, now on the Right side click on the visualizations pane--> Canvas background-->upload the PNG file

Now the Report layout to build visuals is ready to use.

Now drag and drop the visuals and populate the data.

REPORT CREATED USING CUSTOM POWER POINT LAYOUT

Tags	Power BI
Useful links	https://powerbi.tips/tools/layouts https://www.mssqltips.com/sqlservertip/5444/power-bi-layouts-and-themes/
MS Learn Modules	Create and use report templates in Power BI Desktop - Power BI \| Microsoft Learn
Test Your Knowledge	Quiz

Lesson 3 – Introduction to Power BI Service

2023-12-03T06:00:00.001+13:00

Power BI Service or Power BI Online is a cloud hosted Software as a Service component of Power BI. Power BI Service allow users to share and collaborate with the Power BI Reports. Whether you are a Report Writer or a Report Consumer, to access the reports hosted on Power BI Service you need to have a Power BI License.

Power BI Service Licenses

Free per user license

You can create a free Power BI Service account from (https://app.powerbi.com/). With free license, users can only create content in “My workspace” and consume content that is hosted in a Premium workspace.

The license type can be checked by clicking View account as shown below.

You need to have either a Power BI Pro or Premium Per User (PPU) License if you want to publish and share reports within or outside your organization.

Power BI Pro License

Power BI Pro license is an individual user license that allow users to create, share and view reports created by other users. With this license they can share reports and dashboards with other pro users. If the reports and dashboards are hosted on Power BI premium capacity, then pro users can share content with Free and PPU users also.

Premium per user (PPU) license

Premium per user license is an individual user license that provides all capabilities of Power BI Pro in addition access to Premium capacity features. The reports and dashboards that are shared by a PPU user can be shared with other users who have a PPU license, or it needs to be hosted on a Premium capacity workspace.

Premium Capacity

Premium Capacity allow Pro or PPU users to create and save reports and dashboards in Premium capacity workspaces.

Power BI Pricing (as on 20/05/2023)

Note: Check this link for latest pricing.

Components of Power BI Service

Once the report writers have created the reports then the best way to collaborate and share them with other users is to publish them using Power BI Service.

Workspaces

Workspaces are basically containers or folders that can have dashboards, reports, apps, datasets and dataflows. There are 2 types of workspaces in Power BI Service.

My Workspace – Personal workspace for any Power BI user to work with their own content. Only they have access to their workspace.

Workspaces – Create other workspaces to share and collaborate content with other uses inside or outside your organisation.

Apps

Apps are mainly the easy mechanism for sharing many types of content at the same time. Report writers can bundle their dashboards, reports, and datasets. The apps can just be viewed by the users, but they can’t edit them. Sharing multiple reports and dashboards are quite easy with an app.

Dataflows

Dataflows allow organisations to unify data from disparate data sources. They promote single source of the truth whereby data can be collected, cleansed, transformed and business logic can be applied once, and reports can consume from the same source of the truth.

Datasets

Datasets are a collection of data that is either imported or connected to. Datasets are part of workspaces, and a single dataset can be part of multiple workspaces. Each dataset can also be a source of one more report.

Reports

A Power BI report is a collection of one more page with Visualizations or visuals. All visualizations for the report come from a single dataset. Reports come with 2 views reading and editing view. Power BI report can be created using Power BI Desktop or Power BI Service.

Dashboards

Dashboards are always single page and is a collection of visuals pinned from one or multiple reports. You can also pin an entire report in a single dashboard tile. Dashboards are always one page and can be created only using Power BI Service.

Useful Information

Tags	Power BI
Useful links	Getting around in the Power BI service - Power BI \| Microsoft Learn Basic concepts for designers in the Power BI service - Power BI \| Microsoft Learn Power BI service features by license type - Power BI \| Microsoft Learn Pricing & Product Comparison \| Microsoft Power BI
MS Learn Modules	Explore what Power BI can do for you - Training \| Microsoft Learn Manage workspaces and datasets in Power BI - Training \| Microsoft Learn Create dashboards in Power BI - Training \| Microsoft Learn
Test Your Knowledge	Quiz

Lesson 2 – Introduction to Power BI Desktop

2023-12-02T06:00:00.023+13:00

Power BI Desktop is a free desktop tool provided by Microsoft to perform the following tasks.

Connect to different data sources (Cloud, SaaS, on-premises, File System etc.)
Cleanse and Transform the data
Create a data model
Create reports with visuals on multiple pages
Share reports with other users through Power BI Service and Power BI Mobile

Power BI Desktop is an easy to use tool to build reports with visualizations.

You can downloaded via the Windows Store (https://aka.ms/pbidesktopstore) or from the website (https://www.microsoft.com/download/details.aspx?id=58494).

Note: Power BI Desktop gets updated monthly for new features and any bug fixes. If you want your Power BI Desktop to get the new version automatically then install Power BI Desktop using Windows Store. If you want to manually control the installation of the new version, then install via the Website.

Power BI Desktop – Minimum Requirements

Power BI Desktop is no longer supported on Windows 7 and Power BI Desktop is not available for Macs. To use Power BI Desktop on Mac, you need to either

Provision a VM on the Cloud and install Power BI Desktop and access it or
Install Windows on Mac using bootcamp or
Install Parallels Desktop and load Windows 10 and install Power BI Desktop

Other requirements are listed here - (https://learn.microsoft.com/en-us/power-bi/fundamentals/desktop-get-the-desktop#minimum-requirements?WT.mc_id=DP-MVP-5003080 )

Quick overview of the Power BI Desktop Tool

Power BI Desktop – Views

Power BI Desktop supports 3 types of views:

Report – Default view. Shows all Visualizations.
Data – Shows all the tables, rows, and columns if data is imported inside Power BI Desktop
Model – View that allow users to add/change/view relationships between tables

Connect to different data sources

Power BI Desktop provides the capability to connect to various data sources irrespective of whether they are available on-premises, SaaS or Cloud. The screenshot below shows how the different data sources are categorized in Power BI Desktop. We will look at how to connect to these data sources using Power BI Desktop in our later modules.

To connect to data sources via Power BI Desktop, click on Get Data > More…

Cleanse and Transform data

Data is not always perfect in real world scenarios and will require some sort of cleansing and transformation. Power BI Desktop allows users to do cleansing and transformation of the data using the built-in Power Query Editor. Key cleansing and transformation you can do with Power Query Editor are Change data types, remove columns, Replace Values, Merge or Append data from multiple data sources, Combine files etc.

To access Power Query Editor from Power BI Desktop, click on Transform Data > Transform Data

Power Query Editor provides “Applied Steps” feature that allows to see the history of all transformations made to the dataset.

Create Data Model

Power BI Desktop provide users the ability to join data from disparate data sources and create a data model. A good data model is a great foundation for reporting and analytics. Through the model view, Power BI allows to create relationships between tables if they haven’t been defined in the underlying data sources which forms the data model.

Create Reports

Once the data and data model are ready, users can create reports using Power BI Desktop. Power BI Reports comprise of a collection of visuals. Visuals include charts, slicers, maps, tables, custom visuals etc.,

Share Reports

Reports created using Power BI Desktop can be shared with other users once they get published to the Power BI Service Portal. Once the report is published to the portal, it can be accessed using the web browser or using Power BI Mobile.

Useful Information

Tags	Power BI
Useful links	What is Power BI Desktop? - Power BI \| Microsoft Learn Get Power BI Desktop - Power BI \| Microsoft Learn Query overview in Power BI Desktop - Power BI \| Microsoft Learn
MS Learn Modules	Get data with Power BI Desktop - Training \| Microsoft Learn Clean, transform, and load data in Power BI - Training \| Microsoft Learn Design a data model in Power BI - Training \| Microsoft Learn Use visuals in Power BI - Training \| Microsoft Learn Publish and share in Power BI - Training \| Microsoft Learn
Test Your Knowledge	Quiz

Lesson 1 - What is Power BI?

2023-12-01T06:00:00.003+13:00

Welcome everyone, I am back in writing my blog posts and going to write posts on Data and Analytics topics.

What is Power BI?

Power BI is a Visualization tool developed by Microsoft to turn data into actionable insights. Power BI is part of the Power Platform ecosystem providing Business Intelligence capabilities. It is a collection of Software services, apps and connectors.

Power BI allows users to create reports and dashboards by pulling data from different data sources irrespective of whether they are hosted on-premises, cloud or SaaS applications that supports data driven decision making for organizations.

Power BI comprises of the following components.

Power BI Desktop – Windows desktop application mainly used to design and develop reports with visualizations. The Power BI files are with extension .pbix
Power BI Service – Also termed as Power BI Online is a SaaS service to host and share Power BI reports and dashboards.
Power BI Mobile – Mobile Apps to access the Power BI reports and dashboards hosted on Power BI Service.
Power BI Report Builder – Used to create Paginated reports (reports with a fixed layout formatted to fit on a page) that can be hosted on Power BI Service

How it works?

Users can use the Power BI Desktop to create reports and publish them to Power BI Service to share the reports through the Power BI Service Portal or through Power BI Mobile Apps.

How do I get it?

Power BI Desktop – Free tool that can be downloaded via the Windows Store (https://aka.ms/pbidesktopstore) or from the website (https://www.microsoft.com/download/details.aspx?id=58494)

Note: Power BI Desktop gets updated monthly for new features and any bug fixes. If you want your Power BI Desktop to get the new version automatically then install Power BI Desktop using Windows Store. If you want to manually control the installation of the new version, then install via the Website.

Power BI Service – Licensed SaaS product. The available licenses are Free Per User, Pro Per User, Premium Per User (PPU) (https://app.powerbi.com)
Power BI Mobile – Free to download from App Store, Google Play store and Microsoft Store

Power BI Report Builder - Free to download from Windows store Power BI Report Builder - Microsoft Store Apps or from the website https://go.microsoft.com/fwlink/?linkid=2086513

Useful Information

Tags	Power BI
Useful links	What is Power BI? - Power BI \| Microsoft Learn
MS Learn Modules	Get started building with Power BI - Training \| Microsoft Learn
Test Your Knowledge	Quiz

Data Platform Tips 87 - Azure SQL Analytics

2020-04-04T21:27:00.000+13:00

Azure SQL Analytics provides ability to monitor all Azure SQL Databases (single, pooled, managed instances and managed instance databases) using a single view. It is a cloud only monitoring solution and doesn't support monitoring SQL Server databases hosted on-premises or in virtual machines.

Azure SQL Analytics provides 2 views with the following functionality.

View for monitoring Databases and Elastic pools

View for monitoring Managed instances and Managed instance databases

The tiles in Azure SQL Analytics will be populated only if the Diagnostics logs for the Azure SQL Databases are configured to be sent to Log Analytics. With Azure SQL Analytics you will be able to monitor SQL errors, timeouts, blocks, database waits, query duration, CPU usage, Data IO usage, Log IO usage and Query waits statistics.

In the next post, we will look at how to provision and configure Azure SQL Analytics.

Data Platform Tips 86 - Clustered Columnstore indexes

2020-02-21T22:37:00.000+13:00

Many relational databases physically store data as sets of rows, where all the data for a row is located together. This behavior is common for many OLTP systems. For large analytical workloads it is good to use a columnstore index to organize and access the data by column.

A clustered columnstore index physically reorganizes a table. The data is divided into a series of rowgroups of up to 1 million rows (approximately) that are compressed to improve I/O performance. When querying data by column, the data warehouse simply needs to read the column segments for that column. Decompression is performed quickly in memory, and the results returned to the query.

With clustered columnstore index over a table, entire table is indexed.

CREATE TABLE clusteredColumnstoreTable

(

id int NOT NULL,

firstName varchar (50),

lastName varchar (50),

zipCode varchar (10)

)

WITH (CLUSTERED COLUMNSTORE INDEX);

More information - https://docs.microsoft.com/en-nz/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15

Data Platform Tips 85 - Clustered and Nonclustered Indexes

2020-02-20T22:04:00.004+13:00

The purpose of adding indexes to database tables is to increase the speed of retrieving data from these tables. An index contains key values that are created from one or more columns from a database table.

There are two different types of indexes:

Clustered indexes
Nonclustered indexes

Clustered indexes essentially dictate the way data rows are sorted and stored physically in that sorted order when inserted into a table. Clustered indexes are good for quickly retrieving a range of rows based on the key values—because the table is already sorted, based on those key values.

CREATE CLUSTERED INDEX Idx1 ON dbo.Employee (EmployeeID)

GO

Nonclustered indexes do not alter the way in which the data rows are stored in a table.
Nonclustered indexes are created as separate objects from the database table and have pointers back to the data rows in the table.

CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (BusinessEntityID);

More Information - https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15

Data Platform Tips 84 - Clustered Columnstore Index

2020-02-19T22:03:00.000+13:00

A clustered columnstore index physically reorganizes a table. The data is divided into a series of rowgroups of up to 1 million rows (approximately) that are compressed to improve I/O performance; the greater the compression ratio, the more data is retrieved in a single I/O operation. This index uses column-based data storage and query processing to achieve gains up to 10 times the query performance in the Data Warehouse.

Each rowgroup is then divided into a set of column segments, one segment for each column. The contents of each column segment are stored together. When querying data by column, the data warehouse simply needs to read the column segments for that column. Decompression is performed quickly in memory, and the results returned to the query.

Note: when you create a clustered columnstore index over a table, you don’t specify which columns to index; the entire table is indexed.

More info - https://docs.microsoft.com/en-nz/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15

CREATE TABLE clusteredColumnstoreTable

(

id int NOT NULL,

firstName varchar (50),

lastName varchar (50),

zipCode varchar (10)

)

WITH (CLUSTERED COLUMNSTORE INDEX);

Data Platform Tips 83 - Data Warehouse Units

2020-02-18T22:11:00.002+13:00

A SQL pool represents a collection of analytic resources that are being provisioned when using SQL Analytics. Data Warehouse Unit (DWU) is a unit of measure of resources (CPU, memory and IOPS) assigned to the analytic resources. The analytic resources can be scale out or scale back compute by adjusting the data warehouse units setting.

For higher performance, you can increase the number of data warehouse units. For less performance, reduce data warehouse units.

Note: Storage and compute costs are billed separately, so changing data warehouse units does not affect storage costs.

Workload Management - https://docs.microsoft.com/en-us/azure/sql-data-warehouse/resource-classes-for-workload-management

Memory and Concurrency limits - https://docs.microsoft.com/en-us/azure/sql-data-warehouse/memory-concurrency-limits

Data Platform Tips 82 - Heap tables in Azure Synapse Analytics

2020-02-17T22:21:00.000+13:00

Heap table is a special type of database table that enables faster loading of data. Enables faster loading of data in staging tables before any transformation for Data Warehouses. Heap tables are better than tables that has clustered or nonclustered indexes as loading data is slower than heap tables.

A heap table doesn't have any specific ordering and it is simply set of rows. If a table has less than 100 million rows it is recommended to create a heap table and load the data in Data Warehouse. Once data is loaded to the heap table then add the indexes required.

CREATE TABLE heapTable

(

id int NOT NULL,

firstName varchar (50),

lastName varchar (50),

zipCode varchar (10)

)

WITH (HEAP);

Data Platform Tips 81 - Azure Synapse Analytics - Distribution Strategies

2020-02-16T21:26:00.001+13:00

Azure Synapse Analytics (formerly known as Azure SQL Data Warehouse) uses three ways to distribute data. A distribution is the basic unit of storage and processing for parallel queries that run on distributed data. When SQL Analytics runs a query, the work is divided into 60 smaller queries that run in parallel using massively parallel processing (MPP) engine.

Round robin
Hashing
Replication

Round robin: Data is spread evenly among all the 60 underlying distributions without any regard to data values. There is no specific key used to distribute data.

Hashing: Most common and effective data distribution method. Data is distributed according to the most common distributed according to the value of a selected column.

Replication: Data is copied to every database rather than being split up. Replication is good small lookup tables or dimension tables that are frequently joined with other big tables.

Data Platform Tips 80 - Data Virtualization using PolyBase

2020-02-15T22:01:00.001+13:00

One of the key scenarios for SQL Server 2019 is the ability to virtualize data. This process allows the data to stay in its original location and still virtualize the data in a SQL Server instance so that it can be queried there like any other table in SQL Server. This is possible using PolyBase connectors.

PolyBase enables SQL Server to process Transact-SQL queries that read data from external data sources.

With SQL Server 2016 and above PolyBase was able to access data in Hadoop and Blob storage alone. Starting in SQL Server 2019,

PolyBase can be used to access external data in SQL Server, Oracle, Teradata, and MongoDB.

PolyBase enables

Query data stored in Hadoop from SQL Server
Query data stored in Azure Blob Storage
Import data from Hadoop, Azure Blob Storage, or Azure Data Lake Store
Export data to Hadoop, Azure Blob Storage, or Azure Data Lake Store
Integrate with BI tools

Data Platform Tips 79 - Job Diagram in Azure Stream Analytics for Debugging

2020-02-14T22:04:00.002+13:00

One of the way to Debug your Azure Stream Analytics Query is using Job Diagram via the Azure Portal. The job diagram gives a helpful visual representation of the Stream Analytics job that is used to identify issues and bottlenecks quickly.

On the Stream Analytics job in the Azure portal, click Job diagram in the SUPPORT + TROUBLESHOOTING section on the left pane.
View the metrics for each query step by selecting the query step in the diagram
To view the metrics for partitioned inputs or outputs, select the ellipses (…) on the input or
output then select Expand partitions.
Click a single partition node to view the metrics for that partition.
Click the merger node to view the metrics for the merger.

Data Platform Tips 78 - Event Delivery Guarantees in Azure Stream Analytics

2020-02-13T22:05:00.002+13:00

Azure Stream Analytics guarantees exactly-once event processing and exactly-once delivery of events, so events are never lost.

Exactly-once processing

Exactly-once processing guarantees the same results for a given a set of inputs and vital for repeatability, and applicable when restarting the job, or with multiple jobs running in parallel on the same input. Azure Stream Analytics guarantees exactly once processing.

Exactly-once delivery

Exactly-once delivery guarantees means events are delivered to the output sink exactly once, so there is no duplicate output. This requires transactional capabilities on the output sink to be achieved.

Azure Stream Analytics guarantees at-least-once delivery to output sinks, which guarantees that all results are outputted, but duplicate results may occur. However exactly-once delivery may be achieved through the outputs such as Cosmos DB or SQL.

Output supporting exact-once delivery with Azure Stream Analytics

Cosmos DB

Using Cosmos DB, Azure Stream Analytics guarantees exactly-once delivery. No transformations required by the user.

SQL

With SQL, exactly-once delivery can be met if the following criteria is met.

all output streaming events have a natural key,
the output SQL table has a unique constraint (or primary key) created using the natural key

Azure Table

All entities in an Azure Storage Table are uniquely identified by the concatenation of the RowKey and PartitionKey fields. To achieve exactly-once delivery, each output event must have a unique RowKey/PartitionKey combination.

Data Platform Tips 77 - Azure Stream Analytics on IoT Edge

2020-02-12T22:56:00.001+13:00

Azure Stream Analytics (ASA) on IoT Edge empowers developers to deploy near-real-time analytical intelligence closer to IoT devices. Enterprises can now deploy control logic close to the industrial operations and complement Big Data analytics done in the cloud using ASA on IoT Edge.

Azure Stream Analytics on IoT Edge runs within the Azure IoT Edge framework.

ASA Edge jobs run in containers deployed to Azure IoT Edge devices.

They are composed of two parts:

Cloud part responsible for job definition: users define inputs, output, query, and other settings (out of order events, etc.)
A module running on your IoT devices containing the ASA engine and receives the job definition from the cloud.

ASA uses IoT Hub to deploy edge jobs to devices. Refer to IoT Edge Deployments

More information on IoT Edge - https://docs.microsoft.com/en-us/azure/iot-edge/about-iot-edge

Data Platform Tips 76 - Metrics to monitor on Azure Stream Analytics

2020-02-11T22:29:00.000+13:00

Azure Stream Analytics Job need to be monitored for resource consumption and error handling scenarios and it is a good practice to monitor the following metrics that will assist in trouble shooting the jobs.

Metric	Description
Backlogged Input Events	Number of input events that are backlogged. A non-zero value for this metric implies that your job can’t keep up with the number of incoming events. If this value is slowly increasing or consistently non-zero, you should scale out your job.
Data Conversion Errors	Number of errors that the Stream Analytics job encountered when attempting to convert data types.
Early Input Events	Events whose application timestamp is earlier than their arrival time by more than 5 minutes.
Failed Function Requests	Number of failed Azure Machine Learning function calls (if applicable).
Function Events	Number of events sent to the Azure Machine Learning function (if applicable).
Function Requests	Number of calls to the Azure Machine Learning function (if applicable).
Input Deserialization Errors	Number of input events that could not be deserialized.
Input Event Bytes	Amount of data received by the Stream Analytics job, in bytes. This can be used to validate that events are being sent to the input source.
Input Events	Number of records deserialized from the input events. This count does not include incoming events that result in deserialization errors.
Input Sources Received	Number of messages received by the job. For Event Hub, a message is a single EventData. For Blob, a message is a single blob.
Late Input Events	Events that arrived later than the configured late arrival tolerance window.
Out-of-Order Events	Number of events received out of order that were either dropped or given an adjusted timestamp, based on the Event Ordering Policy.
Output Events	Amount of data sent by the Stream Analytics job to the output target, in number of events.
Runtime Errors	Total number of errors related to query processing (excluding errors found while ingesting events or outputting results)
SU % Utilization	The utilization of the Streaming Unit(s) assigned to a job from the Scale tab of the job. Should this indicator reach 80%, or above, there is high probability that event processing may be delayed or stopped making progress.
Watermark Delay	The maximum watermark delay across all partitions of all outputs in the job.

Anupama Natarajan

Lesson 13 - Line chart in Power BI Desktop

How to Proceed?Step 1Launch power BI desktop app and open the new report page and import the data required.

When to use Line chart?

Conclusion

Lesson 12 - Smart Narrative in Power BI Desktop

How to Proceed?

Add smart narrative icon to the visual

When to use Smart Narrative visual?

Conclusion

Lesson 11 - Card (New) Visual in Power BI Desktop

How to Proceed?

When to use Card visual?

Conclusion

Lesson 10 - Slicers in Power BI Desktop

Visual Settings

General formatting

Button Slicers (New)

Clear selections

When to use Slicers?

Conclusion

Lesson 9 - Multirow card Visual in Power BI Desktop

How to Proceed?

When to use Multirow card visual?

Conclusion

Lesson 8 - Card Visual in Power BI Desktop

How to Proceed?

When to use Card visual?

Conclusion

Lesson 7 - Power BI Datasets to build great visuals

Dataset Used:

Context:

Importing Dataset into Power BI

Data Preparation & Cleaning

Lesson 6 - Formatting the Visual in Power BI Desktop

General

Visual

Lesson 5 - Introduction to Power BI Themes

What is Power BI theme?

Types of Power BI themes

How do I use Built-in themes in Power BI?

How to create/Customize a new Theme?

How to Save/Export the customize theme?

Theme Gallery

How to import theme files?

Useful Information

Lesson 4 - Introduction to Power BI Templates

Why Power BI Templates?

Power BI Layouts

Approach 1: Pre-designed Layouts

Approach 2: Custom Layout using Power Point

Lesson 3 – Introduction to Power BI Service

Free per user license

Components of Power BI Service

Useful Information

Lesson 2 – Introduction to Power BI Desktop

Quick overview of the Power BI Desktop Tool

Power BI Desktop – Views

Connect to different data sources

Cleanse and Transform data

Create Data Model

Create Reports

Share Reports

Useful Information

Lesson 1 - What is Power BI?

What is Power BI?

How it works?

How do I get it?

Useful Information

Data Platform Tips 87 - Azure SQL Analytics

Data Platform Tips 86 - Clustered Columnstore indexes

Data Platform Tips 85 - Clustered and Nonclustered Indexes

Data Platform Tips 84 - Clustered Columnstore Index

Data Platform Tips 83 - Data Warehouse Units

Data Platform Tips 82 - Heap tables in Azure Synapse Analytics

Data Platform Tips 81 - Azure Synapse Analytics - Distribution Strategies

Data Platform Tips 80 - Data Virtualization using PolyBase

Data Platform Tips 79 - Job Diagram in Azure Stream Analytics for Debugging

Data Platform Tips 78 - Event Delivery Guarantees in Azure Stream Analytics

Exactly-once processing

How to Proceed?

Step 1
Launch power BI desktop app and open the new report page and import the data required.