zondag 14 mei 2017

Getting filtered data from a tabular with Excel


Building a PowerPivot model on filtered data of a table from a SQL Database is fairly simple. In the table properties you can change the table view to query view and change the query. I haven't found a similar way when using a tabular with Excel (realtime connection).

The only option I have found so far is using the CommandText in the connection properties, but that changes my look and feel of browsing the cube. The retrieved data from the tabular is just like one plain table. This blogpost will describe the way of adjusting the .odc file in Excel and getting the data as a plain table.

The goal of this blogpost is to show how to filter the factdata based on a dimension value (DimCustomer).

The Datamodel

I've created a simple model in SQL Server and imported it into the Tabular project. It is modeled as follows.

One Facttable with a key to the DimCustomer table and a measure Amount. I added a few records.

Some simple EVALUATES

In SSMS, I experimented with some EVALUATES to get some data from the Tabular. Below the result of this exercise.

--Getting the data from a table

--A simple  query (and join between tables)
,"TotalAmount", SUM(Fact[Amount])

--A simple query and a calculated measure is used
,"TotalAmount", Fact[TotalAmount]

--A calculated measure and a Filter
 ,"TotalAmount", Fact[TotalAmount]
 DimCustomer[Name] = "Hennie"

Adjusting the .odc file

From the experiments in SSMS i took one of the DAX Evaluates and inserted that in the CommandText tag in the odc file of Excel.

This is the result in the Connection properties in the Excel file. The CommandType combobox is changed to Default and in the CommandText box a DAX EVALUATE expression is presented.

The result in Excel

And below you can see the fields in the FieldList and as you can see the Look and Feel has changed of the FieldList. 


This blogpost is about how to filter the data in Excel from a Tabular Model.

Best regards,


woensdag 3 mei 2017

DAX : Selecting a measure with a Slicer


Someone asked me a question about selecting a measure in Excel with PowerPivot/Tabular and I founded a blog about selecting a measure written by James Thomas. I have made my own example and wrote this blogpost, about it. In this blogpost I'll describe the following steps:
  • Create some sample data.
  • Create a helper table.
  • Create the datamodel.
  • Create the measures.
  • Create a pivottable and add the properfields to the pivottable.
  • Testing the result.

The source data

I've created three tables: DimCustomer, FactSales and a Measure table.

I added these to the PowerPivot model and the datamodel looks like the following screenshot:

The DAX Expressions

I've created the following DAX Expression (the + 2, etc is for getting some different testdata)

MeasureA:=SUM([Amount]) +2      
MeasureB:=SUM([Amount]) + 3
MeasureC:=SUM([Amount]) +8
MeasureD:=SUM([Amount]) +4
MeasureValue:=switch (TRUE;
Min ('Measure'[MeasureID] ) = 1;
Min ('Measure'[MeasureID] ) = 2;
Min ('Measure'[MeasureID] ) = 3;
Min ('Measure'[MeasureID] ) = 4;

Configure the Pivottable

This is how the pivottable is setup. The MeasureName is added to the rows and the MeasureValue measure is in the VALUES area.

The result

Here is the result of the solution. Here you can see that the slicerbutton MeasureA and MeasureC selects only the measure A and C and that is exactly what I want.

Yet, another example of selection with the slicerbutton:

Here you can see the different measures according to the selected slicer buttons.


This blogpost is about selecting the proper measures with a slicer.


zondag 30 april 2017

Powershell : Backing up your project


In a project where people work together in a team it is advisable to use Team Foundation Server for tracking and storing your Visual Studio project. When I start a project or working on my own on a project, I normally make a backup of the Visual Studio project every day or couple of days. In this blogpost I describe a way to automate the backup of a Visual Studio project by zipping it, tagging it, storing it in a backup folder, send a email when it succeeded or when it failed.

The Script

Below an example of the Powershell script that executes a couple of steps:
  1. If the same zipped backup file exists remove it.
  2. Try to compress the project. 
  3. Copy the file to the backup folder.
  4. Send an email when it succeeds or when it fails.

# Author : Hennie de Nooijer
# Description : Script copies the folder, make a zip with a date and time tag.
# Date : 20170430
$source = "F:\Project"
$dDateTime = 
$strDate = Get-Date -format yyyyMMdd
$strTime = Get-Date -format HHmmss
$destination = "F:\Backup\Project_" + $strDate + "_" + $strTime +  ".zip"
If(Test-path $destination) {Remove-item $destination}
Add-Type -assembly "system.io.compression.filesystem"
    [io.compression.zipfile]::CreateFromDirectory($Source, $destination) 
    Send-MailMessage -From <putyouremailhere> 
                  -To <putyouremailhere> 
      -Subject "Project Backup Succeeded" 
      -SmtpServer  <SMTPServerHere> 
    Remove-Item $destination
    $ErrorMessage = $_.Exception.Message
    Send-MailMessage -From <putyouremailhere> 
                  -To <putyouremailhere> 
      -Subject "Project Backup Failed" 
      -SmtpServer <SMTPServerHere> 
      -Body "The error message was $ErrorMessage"

This is the result if you execute the script a couple of times (without _).

Schedule a windows task

In order to automate the backup of the project, a task in windows is needed. To do that, the Computer Management tool is used. There you can setup windows tasks. Below, the steps are explained step by step.

Give the task a appropriate name and press Next.

Select the frequency of the windows task. In this example we choose Daily. Press Next.

Next step is the time of execution. Select the Start moment and by how many days the task should be recurring. Press Next.

Choose start a program and press Next.

The following step is an important step. Here you enter the command that executes the script. The command is Powershell and the parameters -file "D:\ps1\BackupProject.ps1". Press Next.

Here is the script again.

powershell -file "D:\ps1\BackupProject.ps1"

A warning appears and you can press on Yes.

Finally the task is ready and now press Next.

The task below is shown and is ready or usage.

With the context menu it is possible to run the task on an ad hoc manner..

 And  you can even schedule a task every five minutes ;-) every 5 minutes.


This blogpost describes a Powershell script that automates zips and backups of a project.



donderdag 13 april 2017

Azure and AzureML : Loading data in Azure and using it in AzureML


Using a database from Azure is easy. With SSMS on your local system (on-premise) a connection with an Azure database is easy to create and to maintain. In this blogpost I'll show you a simple way how to create a connection with the Azure database and how to get this data in AzureML.

Logging into Azure

 Logging into Azure with  the following servername property : <servername>.database.windows.net.

Certain firewall settings should be set in order to get access.

Now the object explorer shows the database of Azure.

Querying the database with a simple SELECT Statement.

With Import and Export Wizard I've uploaded some data (California Dairy information) into Azure

And here a connection is made from AzureML with the Azure Database

Press on Run and with Visualize the data is presented that comes from the Azure Database.


Just a simple blogpost on how to connect SSMS (on premise) with an Azure Database and importing the data from the Azure database in AzureML with the Import Data module.



zondag 9 april 2017

Time series


A time series is a series of data points in a certain time order. It is a sequence of discrete time data. Examples of time series are heights of ocean tides, power usage, heart rate, counts of sunspots, stock market values, click data, predict the demand for powerplants usage. But think about sales forecasting, or financial analysis or future projections.

Time series data is normally plotted in line graphs.

The real goal is forecasting of timeseries.

Examples of time series

I've gathered a couples of examples of timeseries.

Here is another example of times series with trend and a seasonal effect.

The Microsoft shares that goes up and down during the year.


First, let's start with some definitions that helps understanding this topic in a more thorough way. Timeseries have certain characteristics. Time Series needs some patterns because if it hasn't  have patterns it is impossible to predict future values. So, the data should contain some certain repeat ability in order to make predictions for the future. 

The mean should be constant and not dependent on time. So, if the overall time series goes upwards or downwards in a graph we can say that this is stationary. The mean should not change over time. Also the variance shouldn't change over time. So if the variance of the data changes overtime like larger and larger peaks it will not be called stationary. Also the covariance should also be the same over time. You can find more information here.

So stationarity has:
  • a constant mean.
  • a constant variance.
  • a constanct covariance over time.

Irregular reporting
In order to build time series analysis it is important that the data points are registered at regular moments. For instance when the data is registered at monthly level and then a half year no data is registered, it's difficult to find regular patterns because of the lack of data.

Auto correlation
Auto means self and Wikpedia : "Autocorrelation, also known as serial correlation, is the correlation of a signal with a delayed copy of itself as a function of delay. Informally, it is the similarity between observations as a function of the time lag between them. The analysis of autocorrelation is a mathematical tool for finding repeating patterns, such as the presence of a periodic signal obscured by noise, or identifying the missing fundamental frequency in a signal implied by its harmonic frequencies. It is often used in signal processing for analyzing functions or series of values, such as time domain signals."

Auto Correlation Function (ACF)
One important function that is often used in time series is the "Auto correlation function". The auto correlation function defines the correlation between the data points in a time series graph. If the graph is random there would be no correlation found between the different data points.

Partial Auto Correlation Function (PACF)
Wikipedia :"In time series analysis, the partial autocorrelation function (PACF) gives the partial correlation of a time series with its own lagged values, controlling for the values of the time series at all shorter lags. It contrasts with the autocorrelation function, which does not control for other lags."

The decomposition of time series is a statistical method that deconstructs a time series into several components. There are couple of major components of decomposition of a time series: trend, the pattern and a stationary signal.

LOWESS / LOESS regression
LOWESS (Locally Weighted Scatterplot Smoothing) or LOESS (locally weighted smoothing), is a popular method used in regression analysis. It creates a smooth line through a timeplot or scatter plot to help you to see relationship between variables and foresee trends. LOESS used local polynomial modeling to find trends and you need to determine the bandwidth parameter. This can be a bit difficult to determine. More information on Loess here

Moving Average smoothing
Moving Average smoothing is used to remove the smaller period stuff in contrast with LOESS that is used for larger period patterns. Moving Average smoothing is for a more fine grain level.

With Moving Average smoothing you find trends with averaging over a cycle length. You need to know the cycle trend. This can a bit tedious and a bit difficult to see but with experimenting and trail and error it is possible to determine trends.

STL is a combination of LOESS and Moving Average Smoothing technique. First LOESS is used for finding a general trend. Then a Moving Avere Smoothing is used to find more trends. The result is a seasonal effect and the last pass is the remainder, the residu.

Use Loess to find a general trend T and subtract it (X - T) and the Moving Average Smoothing is used to find a seasonal effect. Subtract that S = X - T - C.

In the above example the original diagram is shown ("data") and in the diagram below the seasonal effect is presented without the trend and the remainder. Without the seasonal effect the trend is clearly recognized and what is left is the remainder and if this is normally distributed you're on the right track. Because if the remainder does not show a normal distribution there is perhaps a correlation between certain variables.

And the remainder is normally distributed we have removed all of the hidden relations. It is pure random.


If we know the trend and the seasonal effect we can predict the future. In the diagram below the forecast is the blue line is a predicted value of the future based on the trend and the seasonal effect.


This blogpost is an introduction on forecasting based on timeseries.