How to handle NULL in SSAS Tabular by using DAX

As new in DAX area, I found a few useful DAX function that can ease your BI life while you are working with SSAS Tabular model. To handle null values and undefined values were challenging at the begining, however: found two useful function called ISBLANK and IF that you can use to handle those challenges.

Let's discuss with example: a table named Dim_Account which has data like below (In real life you may not have this type of data, however, you get problem when your data is corrupted)

Fig 1: Account data (corrupted)

Now I would like to count number of rows from the above dataset where AccNo is not NULL,and AccTypeName='Savings'

So the DAX look like: COUNTAX(FILTER(Dim_Account;[AccTypeName]="Savings");[AccNo]))

Here, COUNTAX calculate only non empty rows so I got number of row return =1; however; I would like see how many rows also have NULL, and would like to replace the NULL value with 0.

How do you do? There is a function in DAX which is ISNULL. So lets handle the NULL value in DAX:

IF(ISBLANK(COUNTAX(FILTER(Dim_Account;[AccTypeName]="Savings");[AccNo]));0;COUNTAX(FILTER(Dim_Account;[AccTypeName]="Savings");[AccNo])))

If you are coming from SQL background then surely you say NULL and BLANK are not same, they are completely different.. However; here is the reference from Microsoft DAX:

"A blank is a data type in DAX that represents and replaces SQL nulls. You can create a blank by using the BLANK function, and test for blanks by using the logical function, ISBLANK."


However, Lets look into the details regarding function IF(ISBLANK(A);0;A), it means if A is NULL then return 0 and all other cases get the value of A.

At the same way, If we explain DAX for accountype NULL handles:

Fig 2. DAX Null handles

From the above fig 2: first part of the DAX query is traverse through each row of the table and find if any NULL value and then replace that with 0 and the last part is execute if first part is FALSE, it means get all the value than NULL.

Missing Report Data Pane in SSRS

One of the reporting tool is used in BI called SSRS.  And when you create a SSRS project by using SQL Server Data Tools, the most important pane you use to create report is ReportData. Literally, you can't make SSRS report without using Report Data Pane, Now think about suddenly you can't find Report Data pane which was just there before. How you gonna find that?

This post will cover how to find report data when you just miss it.

When you create SSRS project, you will find report data pane under view as like below screenshot.

Fig 1: Report Data in Visual studio

And the report data pane look like below:

Fig 2: Report Data Pane

Now you start making report as like below figure:

Fig 3: Design the report

After working a little bit then you may suddenly don't find the Report Data Pane under 'View', And list under 'view' menu will look like below:

Fig 4: missing Report Data

Now you  are puzzled, how come the pane is not there??  How to get it back:

The reason for missing the 'Report Data' is you must click some where else than the design layout; you may click at the project file (as like below diagram)

Fig: Click at the right place

As long as you click at the design layout then you will find the 'Report Data' pane.

Exporting data as flat file by SSIS: Step by step guideline

Exporting data from database and convert that into comma seperated file(.csv) is pretty straight forward and easy task by using SSIS,

As an example, I need to export ResellerInfo from AdventureWorksDW2012 and generate .csv file by using SSIS.

In SSIS Toolbox you have transformations called a) Data flow Task b) OLE DB source and c) Flat File Destination , to complete the excercise we need those two transformations and one Task.

Fig 1: SSIS Toolbox (Data flow Task)

Fig 2: SSIS Toolbox

In OLE DB Source you should write your SQL query to populate the result, in this case, I have below SQL:   SELECT

       [ResellerName]

      ,[NumberEmployees]

      ,[OrderFrequency]

      ,[OrderMonth]

      ,[FirstOrderYear]

      ,[LastOrderYear]

      ,[ProductLine]

      ,[AddressLine1]

      ,[BankName]

      ,[MinPaymentType]

      ,[YearOpened]

  FROM [AdventureWorksDW2012].[dbo].[DimReseller]

 

At your package you need drag a data flow task and then double click the data flow task, now under the data flow drag and drop the two transformations OLE DB Data source and Flat File Destination.

Fig 3: Under Data Flow task

In the above design, 'Populate Data' is a dataflow task and 'destination CSV' is Flat file destination transformation.

While we connected from 'Populate data' to the 'Destination CSV', we had to configure the 'Destination CSV' which is Destination Flat file Transformation. Destination Flat File configuration must connect with Flat file connection manager as like below:

Fig 4: Flat file destination is conneted with Flat file connection manager

Flat File connection manager should include, connection manager name, file destination and name at least. You can have options to choose from different delimeter like comma, semicolon etc.

Fig 5: Configure flat file connection

Now, from your package you can run the data flow task "Generate CSV for Reseller" by right click.

Fig 6: Execute Data flow task

and then you should have the .csv under the location that you have already put in the flat file connection manager.

Fig 7: CSV result set at the specified location

SSIS Tricks: Control executing the next SSIS tasks

Your SSIS package may requrie to have control that will make decision if the next SSIS tasks will execute or not. I have an example where 'Data Flow Task' for loading the fact table will start if 'Execute SQL  Task' return TRUE.

Here as an example; I have a sequence container that look like below:

Fig 1: Execute SQL task and  Data flow task

Let's look at the code in SQL Execute task named "Control Fact Load":

Fig 2: SQL query for SQL Execute Task

The SQL code in the above diagram:

SELECT case when max([TimeKey])<convert(varchar(8),getdate(),112)

                      Then 'Y' 

                    Else 'N'

         END  as GoToNextStep

                FROM [dbo].[Fact_XXX]

The SQL code in the above diagram intend to find if there is any Date exist in the fact table bigger than today. It means if the fact table's date "TimeKey" is less than today's date then we retutn 'Y' ; so, we did not load any data for today yet. And if today's date is already exist it means data is uploaded to the fact already then we return 'N' hence next SSIS task will not execute.

The return value of the SQL need to save in a variable. So user variable GoToNextStep is created and assigned the value after the Execute SQL task as like below:

Fig 4: User variable created

And then assigned the value after SQL execute task:

Fig 5: Binding value to the variable

The way we control between two tasks is precedence constraint.

Fig 6: Prcedence constraint editior

You need to open the prcedence constraint editior (right click the connector between two tasks) and then set the value as like below:
Evaluation operation: Expression and Constraint
Value=Success
Expression: @[User::GoToNextStep]=="Y"

Now, Data Flow Task (Load Data To Target table) will only execute if prior Exceute SQL task (Control Fact Load) return "Y". If 'N' is returned via Exceute SQL task (Control Fact Load) then next data flow task (Load Data To Target table) will not execute at all.

How to atuomate SFTP file transfer in Windows

When it comes to loading file to the FTP server then we use different free FTP/SFTP client like WinSCP, FileZilla etc. The process of uploading files is completely manual in this way. If your business needs to upload files repeatedly then the process should be automated.

My writing will cover how this can be done in Microsoft windows.
For example you have a file called UploadMe.txt which is located in C:\temp. Now we would like to upload this file to the SFTP Server. Before we start using  script, let's see how we do in WinSCP.

Fig 1: WinSCP  User Interface

As we can see, we need following information to connect the FTP/SFTP server, I have put the information as an example:

Host name: ftp.myftp.com

Port number: 22

User bane: myuser

Password: Password

And when you are connected to the SFTP server then you drag files from your local machine and drop the files to the server.

Fig 2: Moving file from local PC to SFTP Server

How do we do the same thing by using script?

Step 1: You need to install WinSCP, so go to the : https://winscp.net/eng/download.php and download it.

       

Fig 3: Download WinSCP 

Step 2: Script for establishment of connection to the SFTP server and move the file from your machine to the Server. So the script should cover at least below:

a) Opening a connection the SFTP server, below syntax will open the connection:

open sftp://myuser:Password@ftp.myftp.com:22


b) Syntax for copying data from local machine to FTP server:
put -nopermissions -nopreservetime "C:\temp\test_data.txt"  test_script.txt

Lets make full script in one place and save the file as 'uploadscript.txt'

option echo off
option batch on
option confirm off
open sftp://myuser:Password@ftp.myftp.com:22
#Change LOCAL directory
lcd "C:\temp\"
#copy an individual file
put -nopermissions -nopreservetime "C:\temp\test_data.txt"  test_data.txt
exit

Before we run the above script through .bat file, we would like to test if we can connect with FTP server and load the file by using command windows.

Fig 4: windows Command line

Change the path to WinSCP so that we can use WinSCP resources:

Fig 5: Accessing WinSCP file

Run the script which will make connection with the SFTP Server and copy the file to the server.

Fig 6: Make connection and move file

As you can see from the above screenshot (fig 6), SFTP server connection is made and started loading the file.

However, above process is done through windows command line to see if we can establish connection and transfer file. Since we would like to make it automated so we need to make a batch file and run the script ('uploadscript.txt') that we just run through windows Command line. And last step is to schedule the task via windows Task Schedular, hurrah, now your file will be automatically upload to the SFTP server as schedule them.

How to fix your corrupted SSAS database?

If your SSAS database is corrupted, then you can either 1) restore the database or 2) delete the existing SSAS database and then process the cube to load the fresh data. 

I would like to share the second one: Delete the existing database and then process the SSAS cube to load the fresh data.

For example, my SSAS database 'HelloWorld' got corrupted and now you would  like to fix the SSAS database, so following steps should be taken to fix it.

Step 1: Delete the existing database

Fig: Find your corrupted database

Hit the delete button

Fig 2: Delete the database

Choose any one of the options that you prefer:

Fig 3: 

Since database is deleted, now we can open SSAS package and hit the process button to deploy and process it as like below:

Fig 4: Deploy the SSAS Cube

You may unfortunate:

However, if we may unfortunate sometime when you will find error like :

Fig 5: Deployment error

Step 2:  Delete the .xml file from data directory

To fix the error we need to delete .xml file from the data directoy, Depend on your Windows version (32/64 bit), you can go to data dictionary and delete the xml file

C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\Data

The XML file format will look like: <CubeID>.<VersionNum>.db.xml, e.g. this case:  HelloWorld 1.0.db.xml . You can also delete the folder as like same name:  HelloWorld 1.0.db

Fig 6: Deletete the .xml file

 Step 3: Restart the SSAS 

Restart the SSAS 

Fig 7: Restart the SSAS

Step 4: Run the SSAS package

You are all set to process the cube. So open your SQL Server Data Tools and SSAS solution, process the cube and you will be able to load fresh data structure and the data, it means your databse is back again and functional as like before.

Measures(Calculation) in SSAS Tabular

While you work with SSAS Tabular or OLAP you have option to create measures. As found calculation in SSAS OLAP is easier than Tabular. However, I have recently made SSAS Tabular cube and going to share how to create Measures/Calculation in SSAS Tabular model.

SSAS tabular project look like below, I have marked the area for the calculation/measures.

Fig 1: Measures/Calculation area.

How do we make Measures?

Select any cell from above fig 1, and then as like excel you  have option to put the calculation or formula at the formula bar as like below fig 2 (yellow shaded).

Fig 2: Calculation bar

Here, I am going to calculate how many product is sold in Canada region, So I have calculated number of rows where productno are not empty and filter the salesTerritory with Canada.

The measure name is: Prod_Cnt_Canada (product count in the canda region)

Prod_Cnt_Canada:=COUNTAX(FILTER(FactInternetSales;[SalesTerritoryKey]=6);[ProductKey])

The above example counts the number of nonblank rows in the column, ProductKey, using the table FactInternetSales that results from filtering the SalesTerritoryKey=6 (means canada).

COUNTAX function is to return the number of rows for non empty rows. And FILTER function is to do the filter.

Result after the calculation is show in the below figure: 3

Fig 3: Measure after the calcuation