After 14 years using Microsoft On Premise BI Tools (SQL Server, Reporting Services, Integration Services and Analysis Services) Its time to embrace Business Intelligence in the cloud.
In many situations there is a business need to look at forecasts of, for example sales.
The following table shows example data of measures created to use for a Sales Forecasting measure
Creating some Key Measures
Using DAX
We have a Sales Table that contains Quantity and a Products table that contains Price
The following is a calculated column to get the total sales from Quantity and Current Price for each row
Add a filter to your report page for the year in order to
see the above table
To create the data for the table above, create a table and
drag Date (Date table joined to sales)
Next create a new measure
This becomes column 2 of the above table. At each date it
looks at the Totals Sales at that point last year
Create your 2 years ago measure
Next is the 3 years ago measure
These become the next columns in the table above
Creating the Sales Forecast using variables
Or, instead of creating three different measures to use for forecasting we can use variables (The 3 measures are basically set inside the forecast measure instead of creating all 4 measures
We can improve on this. We want our forecast to show a 5% growth rate
If you look at the sales forecast in a bar chart by day it would show you how much money you need to earn every day over the year based on the previous years data
Showing the cumulative Sales Forecast
Forecasts can now be derived by anything in your model.
Product name etc
Another forecasting example using Power BI Analytics
Now if you go into the analytics pane
Scroll all the way to the bottom you will see forecasting. The following are the forecasting defaults
Add: Forecast 1
Forecast Length: 10 Points (10 days into the
future because we are using day)
Ignore Last: 0 points
Confidence: interval 95%
Seasonality: Auto Points
Change Forecast Length to 12 months (Into the future)
Seasonality: Change to 365 points because there are 365 days in the year and this now takes you to a full forecasting year
this is a great way of playing with the Forecasting to see the effects of any change in the forecasting defaults
Currently you can only forecast on a line chart. Hopefully there will be lots more Forecasting updates in the future.
I’ve been spending some time revising for the Azure Fundamentals course. Here is a quick list of some of the  more problematic test questions I’ve come across
Azure Advisor
Detects threats and vulnerabilities
Ensures Fault Tolerance
Helps reduce spending
Protects data from accidental deletions
Speeds up your apps
Application Gateway
Multiple instances of a web application are created across
three availability Zones. The company also configures a networking product to
evenly distribute service requests based on 3 different URL’s
Application insights
Feature of Azure monitor
Visually analyse telemetry data
ATP (Azure threat Protection)
Pass the ticket – Attacker stealing KERBEROS data
Pass the hash – Attacker stealing NTLM data
Suspected Brute Force attack – Multiple attempts
to guess a users password
Audit Reports – Service is within the trust Portal
to determine Azure Compliance with GDPR
Compliance manager – Determines whether or not
your services meet industry standards
GDPR – Standards enforced by a government Agency
Germany – Country with a dedicated trustee for
customer data.
Physically isolated instance of Azure
Azure government – Only available in the US
ISO- International Standards based on non reg
agency
NIST – Standard based none reg agency based in
the United States
National Institute of Standards and technology
Cloud Shell, CLI and Powershell
Azure CLI
Az login
Cross platform command based line tool
Azure Cloud Shell
New-AzureRmVM
Web based tool after you log onto the Azure
portal
Azure Powershell
Connect -AzureRMAccount
Use when you need to log into Azure without
opening a web browser
Azure Governance
Locks – Prevent users from deleting resources
Advisor – Use information from the Security center
to best practices
Initiatives – Define a set of policies
Cloud Computing terms
Fault Tolerance – Power Outage in a data center.
Automatic Failover for continual operation
High Availability – Having data available when
you need it
Fault tolerance and High
Availability are both good for the scenario when you are moving on premise data
centers to the cloud. The data is mission critical, there is a need for access
to the data sources at all times. Changes are incremental and easy to predict.
Elasticity – Sudden spikes in traffic
Scalable – Increase the Number of VMs easily
Azure Locks
Multiple Locks applied to different scopes. The most
restrictive lock is applied
The lock applies to all resources contained in a
scope and any new resources added to the scope
Networking
NSG – Network Security Group. Inbound traffic for a virtual machine from specified IP addresses
DDoS- Distributed Denial of Service Prevents a flood of HTTP traffic to a VN that hosts IIS
Firewall – Create a rule that restricts network traffic
RBAC
Limit Access to Resources at the resource groups and
resource Scope
Service Health
Notifies if App service usage exceeds the usage quota
Columns vs Measures is a particularly misunderstood concept
in Power BI.
Often a user tries to create a Measure and realizes that the data item they want to use isn’t available and its hard to understand why. After a few times of this happening the user switches to using calculated measures all the time, losing the functionality of the measure.
The evaluation context is important to note for both of these column creation methods. This is the environment under which the formula is evaluated.
Calculated Columns
The evaluation context for the calculated column is
performed for each row
Example 1, I need to create an Order Total from Unit Price and Order Qty
This is correct for the Calculated column as the order total
is created for every row.
Misunderstanding the measure and trying to do the same thing as a measure causes issues
Note that Unit Price not in the list. It is not available to
the measure.
We will answer Why later
Calculated column data is stored in xVelocity in-memory
database. The calculation is made before the model is queried by the user. Calculated columns are good because there is
a smaller virtual memory requirement when the user is interacting with the
reports. However they take up more
storage in the database.
There are other advantages to the calculated column
Slicers
The cost bucket allows us to create a slicer in Power BI based on Low medium or high values. The DAX looks at each row and the new column is set before the model is queried.
Measures
Measures are used to calculated aggregates like Sum or
Average.
Measures are created at the time of the query so they are
not stored in the database.
Why could we not see Unit Price when we changed the 1st
Example to a measure? Because Unit Price is at row level. The measure needs to
be applied to aggregated values.
Example 2 – Get the order total of the low value items
First of all, notice the Sum around Order Total. The Sum
aggregates the order total and allows this item to be available to the DAX
query
Cost bucket is available because all Low cost buckets are
grouped first in order to find all the Low values.
Next Notice that the Sum of Low orders is not available in the data view pane because it doesn’t exist in the data in the way a calculated column does. We can see the Measure under fields to use within reports
Measures are evaluated in this specific way:
The evaluation context is examined. DAX is
selecting Low Priced items
The evaluation context is evaluated against the
table. Order Total is being filtered on Low Prices
The Aggregation is applied SUM
Results are returned
What to use when
Calculated columns can be used when each row needs
evaluating
Debbies Microsoft Power BI, SQL and Azure Blog 