Let us Learn Power BI Together!

[Rathinagiri]

1: Columnar Databases and the Tabular Engine
Power BI leverages a highly efficient, in-memory storage known as columnar databases within its Tabular Engine. Unlike traditional databases that store data row by row, columnar databases store data column by column. This methodology allows for faster data retrieval speeds, making it highly suitable for financial data analysis where millions of transactions are analyzed across different dimensions such as time, geography, and product categories. For instance, when analyzing financial transactions for trends, the Tabular Engine can quickly aggregate values like sales revenue or profit margins, reducing the time spent on data processing.

[Rathinagiri]

2: Understanding the Relational Model Difference

Traditional relational model stores data in rows. This method can be cumbersome for analysis that require aggregation of data across multiple columns. In financial reporting, where one might need to query large datasets for specific ledger entries, a relational model may result in slower performance due to the row-wise data layout. Power BI’s adoption of a columnar approach means that financial analysts experience remarkably faster data retrievals, aiding in real-time decision making. RDBMS uses Entity Relationship model where as Power BI is using Fact->Dimension data model.
Traditional databases are designed to process the transactions quickly and used in Online Transaction Processing (OLTP). For Online Analytical Processing (OLAP), we need a different kind of storage model ie., Columnar Databases. Apart from Microsoft's Tabular Engine, there are other columnar databases also available like Amazon Redshift, Google BigQuery, Snowflake, Apache Kylin, Vertica, ClickHouse, SAP HANA, Apache Druid, Exasol, DuckDB etc.,

[Rathinagiri]

3: Power BI Data Modelling

Data modelling in Power BI involves structuring data in a way that it is useful for analysis and reporting. This could include defining relationships between different data sets, creating calculated columns or measures, and organizing data into a cohesive model. A typical example in the financial sector could be integrating sales data with cost information to create a profit analysis model. This model would help in visualizing profitability across different regions or product segments.

[Rathinagiri]

4: Fact Tables

In data modelling, particularly in the context of financial data analysis, fact tables are central to understanding transactional metrics. They store quantitative data like sales revenue, costs, and profit margins. Each row in a fact table represents a transaction or event. In a financial scenario, a fact table might record daily sales transactions, capturing data about each sale such as the sale amount, the date, and a link to dimension tables like Products or Time.

[Rathinagiri]

5: Dimension Tables

Dimension Tables are used in Power BI to store contextual information. Dimensions give meaning to the facts. These tables are key to enriching fact tables as they provide descriptive attributes related to the metrics captured in fact tables. For example, in a financial reporting context, a Products dimension table might include details like product name, category, and supplier. Analysts use dimension tables to slice and dice the fact table data, allowing for detailed analytical insights, such as sales by product category.

[Rathinagiri]

6: Hierarchies

Hierarchy in Power BI helps in drilling down through data, enhancing the data exploration capabilities in reports. For example, in a financial report, a time hierarchy might enable users to drill down from year to quarter to month, examining trends at various temporal granularities. This kind of data drill-down can help in pinpointing specific periods of interest, such as understanding quarterly revenue growth. Most of the dimensions like place, products, customers, time, employees have hierarchies which make data analysis intutive.

[Rathinagiri]

7: Measures

Measures in Power BI are calculations used across reports and dashboards. In finance, common measures include total revenue, average transaction size, or year-over-year growth. These calculations are dynamic and are computed based on the context of the other data elements in reports. Measures are essential for creating key performance indicators (KPIs) that help financial analysts monitor and assess financial health. DAX expressions are used to create measures in Power BI.

Measures can be classifed into two namely:

1. Implicit Measures
2. Explicit Measures

[Rathinagiri]

Implicit Measures:

Implicit measures are automatically created by Power BI when you drag and drop a field into a value area of a visualization. Power BI automatically applies an aggregation function like SUM, AVERAGE, COUNT, etc., depending on the data type. These are not pre-defined in the data model but are created on-the-fly based on user interaction with the report interface.

Example:

Dragging a field named "SalesAmount" into a value area of a bar chart. Power BI might automatically sum the "SalesAmount," creating an implicit measure that calculates the total sales.

[Rathinagiri]

Benefits of Implicit Measures:

- Ease of Use: They require no DAX knowledge, making them very user-friendly for beginners.

- Quick Setup: Allows for fast and straightforward report generation for simple aggregation tasks.

Limitations of Implicit Measures:

- Limited Flexibility: Implicit measures offer less control over calculations since they are automatically applied.

- Performance Implications: Using many implicit measures can slow down report performance, especially with large datasets.

- Lack of Transparency: It's not always clear which aggregation function Power BI applies, leading to potential confusion or inaccuracies in reports.

- Reusability: Implicit measures can not be used across different reports and visualizations within the same dataset.

[Rathinagiri]

Explicit Measures: Explicit measures are defined by the users using the DAX formula language. They are created within the data model and can involve intricate calculations, logical tests, and the use of multiple DAX functions.

Example:

Creating a measure to calculate the average sales per transaction:
Average Sales per Transaction = DIVIDE(SUM(Sales[SalesAmount]), COUNTROWS(Sales))
This explicit measure uses DAX to divide the total sales amount by the number of transactions.