Return NULL value from empty table

You may come at the situation where you need to return NULL even if your table is completely empty. For example, you need to make an action depend on if the table's column has value or not., it's generally the case, when you would like to load the data in your data warehouse for the first time and then extract delta every day.

Fig 1: Make action depend on the tab'e's data

From the above case, we need to check if a column of the table has value or not. We have created a table which is empty.

Fig 2: Empty Data set

Now if you would like to produce a value when there is no data in a table, you can return NULL.

There maybe many ways to find out , but my preferable way will be

Approach 1:

select case when count(1)=0 then NULL else 1 end from dbo.[TimeStamp]

In case you need to pass parameter then:

select case when count(1)=0 then NULL else 1 end from dbo.[TimeStamp] where TableName='Table1'

Approach 2:

select
  (select [MaxTimeStamp] from dbo.[TimeStamp] ) as [MaxTimeStamp]

In case you need to pass parameter then:

  select
  (select [MaxTimeStamp] from dbo.[TimeStamp] where tablename='table1') as [MaxTimeStamp]


You can use any one of the syntax to return NULL whenever you need it.

SSIS Error: Cannot convert unicode and non-unicode string data types

At the time of data migration or at any situation you may need to add new column and assign fix value to the column. After you have added the column and put the static/fixed value, then you may receiving the error: "Cannot convert unicode and non-unicode string data types"

 Let's make an example; business delivered excel file with all data from legacy system which is already closed; and  you need to add the old data to your data warehouse. Loading data from excel file to a staging table;you built the package as like below figure 1, where you have added data flow component 'Excel file source' and you have 'derived column' to add new column and assign a hard coded value.

After you design it found the red error like below that you may hate.

fig 1: SSIS package migrated data

Well, let's look it closer and you can see the error saying  as like below fig 2: "Cannot convert unicode and non-unicode string data types". But why we see this error?

Fig 2: Unicode and non Unicode miss matched

We made a new column by using derived column component called 'Status'  and we set 'Completed' for the column Status which is extracting from the excel since all extracted data are old and transactions are completed so we set status as completed. However, the static string 'Completed' set as data type Unicode String [DT_WSTR] in SSIS which has issue with the target table column 'Status' data type. The  staging table has staus column as varchar which cause the issue here.

Fig 3: Data Type issue

If you look at the above figure 3, where string "Completed" is set under the Expression so DataType has been picked as [DT_WSTR] which is Nvarchar means Unicode. However, the staging table we have created in the database, the particular column is defined as Varchar which is DT_STR for SSIS means non Unicode. So it's clearly difference between [DT_WSTR] and [DT_STR] in SSIS.

How to solve it?

To solve the miss match of Unicode and Non Unicode issue, we need to convert unicode data type to Non Unicode. So here goes the conversion expression :(DT_STR,17,1252) "Completed". "Completed" string is considered as Non Unicode/Varchar data type. 

Fig 4: Solve issue with data conversion

If we explain the expression (from Fig 4),  where (DT_STR,17,1252) -> (DataType, length of the data, Code page), Code page 1252 means it's windows Latin 1 (ANSI).

After you add the above expression the error will disappear and you can run the package to load the data.

SSIS: Loading Flat file and issue with locale settings

Working with Flat file source in SSIS is common task and we all have experience with that. However, sometime it get tricky when it comes to localization. Some countries uses comma (,) and some countries uses dot (.) as decimal point for numerical field in the table.

If your data include decimal places  with dot (.) and you use the locale as any of the Nordic country such as Denmark/Norway/Sweden, then you are screwed. Since these countries use their decimal places as comma (,). I am going to explain a scenario where faced similar issue.

Got a file from business where a column in the file was numeric, and e.g. data look like below:

Budget	Amount
Food	229.1200
Cloth	8.4100
Travel	12.1500
Apartment Rent	271.2800

 Created the SSIS package:

Fig 1: SSIS package for loading data from Flat file

When I made the package did not look carefully the numerical field, so put the locale as Danish. If you open the connection manager you will find window like below:

Fig 2: Setting the locale

As you can see from the above Figure (Fig 2) I put Danish(Denmark) as locale, so after loading the data from the flat file to the staging table I only can see wrong data is inserted. After spending some time found the locale I have set it will make mess with dot (.) decimal place.

It means either I have to change the file replacing dot (.) with comma (,) or I can change the locale setting with English (United States). Offcourse, you will find more easy to change the local setting.

Hurrah!! Now you got correct result at your staging table.

               

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.