Power BI Connecting to a Sharepoint Document Library

I have some folders in Sharepoint and I want to be able to connect to these in Power BI

The folders are in

Get Data

There is no specific connector to Sharepoint Document Library at present

Choose Sharepoint Folder. Add in the Sharepoint Location. The route URL is required here

You may then need to authenticate your Sharepoint connection

When you get to the next screen notice that the content that is shown are files and we don’t want this. Click Edit

Simply Remove Files and change it to Contents

Click the tick and your contents will be refreshed to show Files

In this case click on Table against the Uploads folder which takes the user down to the next level.

Here are the 3 files that I want to append with all the data

Hopefully Power BI will introduce a better way of adding in Sharepoint Document Library soon

Power BI Forecasting

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.

Azure Fundamentals training (Quick Notes)

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

Compliance

http://servicetrust.microsoft.com – Compliance manager URL

  • 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
  • Respond to planned Service outages
  • Implement a web hook to display health incidents

Power BI Measures vs Calculated Columns

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

Measures are used for Aggregation.

Create your website with WordPress.com
Get started