Saturday, December 16, 2017

How to create new workspace in TFS?

You may need to create new workspace in TFS, if your current workspace is corrupted or you have some issues with where you messed up totally. I am going to guide you how to create a new workspace and load the source code in the new workspace so you can have fresh start over.

Finding out the menu item 'workspaces' could be tricky, so you go :

File->source Control->Advanced->Workspaces  as like below image.


Fig 1 : Find out Workspace for TFS

When you are at the work space window then please fill up all necessary information to add new  work space:

By following above fig 2:  You need to fill up as below:


a)       Click ‘Add’
b)      Edit name
c)       Put status as ‘Active’
d)      Put Source control folder to /FolderBI
e)      Put the local folder location where you would like to put the source ode



Fig 2: fill up info for new work space

Now you are good to go and get the latest to the new folder you specified.



Saturday, November 25, 2017

How to handle trailing space while you use LEN function in SQL


LEN function in MS SQL returns the number of characters of the specified string expression, excluding trailing blanks. [1]

It means if your data has trailing blank /space in the string then LEN function will ignore that.
e.g. 

SELECT LEN('DK123456789 ') as LengthofString

Which give you as length 11, though with space it’s 12.
Well, you can say we know it will be 11 why you should have 12? Or what is problem if it returns length as 11?

Let’s discuss the above scenario, consider above string 'DK123456789’ is a valid company VAT registration number. And your customer asked to get only the number part, so output should be ‘123456789’
So you use RIGHT function as like below:
Fig 1: RIGHT function to find number part


And you get the right result.

However, if your data has issue like trailing blank then can you expect the result`? Let’s see:

Fig 2: When you have data issue


So you lost the first digit since RIGHT function consider space/blank as character so you get 9 character from the right side of the value which include one space but discard first character ‘1’.

The above particular example we already know the length, however; when we query SQL table then we don’t know the length so we must calculate length as well.
so let’s create a table and insert some data:

CREATE Table #Table1 (
 [VATRegistration] nvarchar(50))

 insert into #Table1 values('DK123456789 ')
 insert into #Table1 values('DE7891012879')
 insert into #Table1 values('BD989741258')

 select * from #Table1


Fig 3: VAT registration data



We make the SQL query to find out only number part from the above dataset, the query is look like:


SELECT [VATRegistration],RIGHT([VATRegistration],LEN([VATRegistration])-2)  as [VATRegistrationNo]
FROM #Table1   /*Since we know first two characters need to exclude from the VAT number*/

 
Fig 4: Output with error data


Does our result correct? Look closer and will find first row has discarded first digit. Why this happened? Because the data has issue, If you look at the above insert statement you will find it has trailing space. So how do we solve this?

The main problem here is to calculate the LEN, since data LEN will not count space as length. Well can’t we try RTRIM then? Sure let’s do it.



 SELECT [VATRegistration],RIGHT([VATRegistration],LEN(RTRIM([VATRegistration]))-2)  as [VATRegistrationNo]
 FROM #Table1   /*Since we know first two characters need to exclude from the VAT number*/


Unfortunately, result did not change, well; it’s not unfortunate; rather this how it’s built, TRIM function will not have any effect under LEN function.

What can we do then? Yes…, REPLACE must be a good option here.


SELECT [VATRegistration],RIGHT([VATRegistration],LEN(REPLACE([VATRegistration],' ','_'))-2)  as [VATRegistrationNo]
 FROM #Table1   /*Since we know first two characters need to exclude from the VAT number*/


Result look fine now:

 
Fig 5: Correct output by using REPLACE

Since REPLACE make removed the space and put ‘_’ which is count as character. And RIGHT function return 10 character from right to the left which include space as well.



Fig 6: Where is the difference??

 After you get the result you can RTRIM the final value before you present to the report or sending to the business.





Thursday, October 19, 2017

Step by step guideline for installing Visual studio 2017 for BI Developer


It is easy to install Visual studio in general, you make a few clicks, follow the wizard and then you are done. However, when it comes to Microsoft BI tool installation then last couple of years it changes a few times. For example earlier (before VS 2010), the BI tool called  Business Intelligence Development Studio (BIDS) and it was part of SQL Server installation.

But when Visual Studio 2010 release, you got new name called 'SQL server data tools (SSDT)' and which you had to install top of Visual studio..
 Fig 1: SQL Server Data Tools menu

When you open SQL Server Data Tools (SSDT) you find the template like below to develop BI solutions.

Fig 2: Templates under SSDT


However, Visual Studio 2017 release you will not have fully functional SQL Server Data Tools as separate instance. The template for making BI solution (SSIS,SSAS, SSRS) will be integrated inside Visual Studio 2017. Being said so, the release version 15.3.0 preview also have possibility to install as separate instance. However, stand alone instance breaks every time if you try to connect with TFS.

Enough talking. let's start with the Visual Studio 2017 installation steps for BI Developer:

Step 1: First install the Visual Studio 2017

Run the VS 2017 setup package and you will find screen like below to choose for BI development:

Fig 3: choose what to pick

Below image will give you closer look of what have been chosen for BI development.

Fig 4: Closer look -what have been chosen for BI
After you select the boxes you follow the wizard and will be completed the VS 2017 installation.


Step 2: After completing the VS 2017 installation take the SSDT file downloaded from (https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt). If you already have the SSDT exc file then please avoid dowloading the file.

When you run the SSDT-Setup-ENU.exe file , you will find below window to choose from :

Fig 5: Option to choose for installing separate instance for BI

If you want templates for BI development should be place inside Visual studio 2017 then use the first option; but if you want separate instance then choose the second option from drop down list which is: "Install new SQL Server Data Tools for Visual Studio 2017 instance"

I have installed separate instance at first go and installation went fine, under your desktop menu you will find Visual Studio 2017 (2) as like below:

Fig 6: Separate instance only with BI Templates

Though separate instance installation went fine, however; the instance was crashing every time I have tried to connect TFS. Then I uninstalled it and installed with the option selected " Visual Studio Community 2017" (Look at the figure 5). And I get BI templates inside the Visual Studio 2017 which look like below:


Fig 7: BI templates inside VS 2017
Now you are ready to make your BI solutions by using brand new Visual studio 2017 and it also works integrating with TFS.




Saturday, September 30, 2017

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.

Saturday, August 26, 2017

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.

Saturday, July 8, 2017

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.



               

Friday, June 23, 2017

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.


Tuesday, June 20, 2017

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.







Sunday, May 7, 2017

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


Friday, April 21, 2017

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.



Sunday, April 2, 2017

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.