Alteryx – First and Lasting impressions

If not for the new job that I have joined I would not have heard of this tool at all. Alteryx is another kid on the block that is mix of ETL / Analysis / Reporting all blended into one. The reporting part hasn’t impressed me much but some of the the ETL/ Analsyis features that it has just blew me away. In this post I would like to point out few key areas where I feel this just aces miles ahead of SSIS (or) Informatica.

Installation
You just need to go to the site, click on the Download Now. This would then prompt you to register and you MUST enable to their subscriptions. Once done, you would have 14 day trial of the product. It’s that simple.

Preview Data at every single stage post run
At each stage of transformation one can see the data before and after the transformation post the execution of the workflow. I just can’t fathom how are they even doing this. Let me show you with an example

Here is a simple workflow that is taking student data as input, doing some check on Gender (if Male/Female) and then cocatenating first name and last name for both the flows.

1_StudentImport_Overview

If you are coming with a ETL background, you would be able to quickly latch on to the transformations and what they do because they are so intuitive. Even an hours video from youtube would be sufficient to quickly scale up on transformations. If you notice each of the transformations above, there are green button like icons before and after. They are basically input and output (as if you didn’t decipher already). Now let’s say the workflow is run. Post run, I can click on any of these buttons to see the data at that particular stage.

Let me pick ‘Identifiy Gender’ transformation. Once you click on the transformation, all the inputs and outputs of that transformation are available to be previewed. Seen below is how the input data looks like. My condition was to seperate them by Gender –
2_IdentifyGender_Input
If I want to see what the ‘T’ output rows look like (i.e. Males), I just click on it –

2_IdentifyGender_True
Now let me have a look at the ‘F’ output rows –
3_IdentifyGender_False
Imagine this being the case at every stage of the transformation. It’s just incredible to be able to see how your business rules are working at every stage.
Let me just repeat one more time, if you haven’t read the sub heading, this preview is POST-RUN. I just can’t think of any alternative for this in either SSIS/Informatica.

Testing the DFT without Output-
Just scroll little back up and see the screenshot of the workflow I posted. It doesn’t contain an ‘Output’. The last transformation that you see is just a UNION ALL. Say if you are developing a POC or just testing somethings out, you avoid the necessity to create a destination and then dumping the data. Of course, Trash Destination comes quickly to mind from SSIS stack but that’s an add-on and not out-of-the-box feature. I can’t think of any in Informatica though.

Multicasting
Pretty much every single transformation’s output you can multicast and then branch off to do some entirely new logic altogether.

Dynamic column propogation-
I have been saving the best for the last. It has this incredibly advanced capability of bringing in dynamic columns just as if they have been there all along.  Let’s say in the data above, I perform two changes to the input file –
-Added new column say Location at the end
-Added new column Is Married after ‘Last Name’ column

Without doing any changes to the workflow, it just runs without throwing any error and here is my output from ‘Union’ transform –

4_ColumnAddition_WorkflowRun

HOLY COW! It’s just mind-blowing, ain’t it. In terms of data modifications, it was pretty drastic, as in new columns were added not just at the end but also in between, and Alteryx just doesn’t care about it. It just works!

I am sure, I am just scraping through the tip of the iceberg and there is HUGE amount of exploration left to do. What is also fantastic about this product is the community behind it.

Their community forums and learning channels are all free for anyone to ask and learn much like MSDN community or Informatica ones. They have weekly challenges running which are good fun to flex your muscle and give it a try. The whole interface though I feel they can improve on. I feel bit claustrophobic with all the overbearing green color theme and design but you get used to it.

All in all I am loving it. Watch out for future posts where I detail how it fares performance wise, error handling, configurability, looping, dynamic data parsing etc.

Excel Import date getting recoginzed as month

I had a really wierd error this morning when I wanted to import some sample data from CSV into excel. One of the columns in my data was a date column. Upon import here is how the data got displayed when I clicked on the ‘Filter’ column. All the data that I had in it were of October 2017 only but the filter was showing something else –

Incorrect Date Filter

The date value in the column is in the format – dd/mm/yyyy but clearly excel was thinking otherwise i.e. mm/dd/yyyy. I knew right away I had to do some settings change but number of changes I had to do was a lot and I thought it’s better to document it.

Click on Start and go ‘Date & time Settings’. On the far right-hand side, under ‘Related settings’ click on ‘Additional date, time, & regional settings’ as shown below –
AdditionalDateTimeSettings
Under Region, click on ‘Change date, time, or number formats’ as shown below –

Change date,time or number formats

Here are two changes that you would need to do –
1. Under Formats tab, set ‘Format:’ to ‘English (Australia)’ as shown below –

RegionFormats.png
2. Under Location tab, set ‘Home location:’ to ‘Australia’ as shown below –

RegionLocation

This step would necissitate restarting the system. In this example I have shown ‘Australia’ as locale as that’s where I am currently resisding. Do change it to relavent locale.

That’s it. Post restart you would start seeing the way it is expected as shown below –

FinalResult

SSIS vs Informatica – Error Handling

A close friend of mine who also has been, like me, until recently working on MS-BI all through his career has moved on to different stream for performing ETL i.e. using of Informatica.

Having been relatively new to Informatica (with only couple of years and few projects here and there), we had many a arguments on which is better and why. As I stated out in my first post about Informatica,  I didn’t have good feelers with it when starting out. There were too many IDE’s to grapple with, transformations do not look intuitive, simple tasks look very hard to perform etc. One of the arguments he did make is Informatica is a market leader and is lot more powerful owing to it’s code re-usability, out-of-the-box connectivity across varied sources, error handling etc and SSIS is doing now a lot of catch-up.

I decided to dig deep and validate my own findings on the ‘out-of-the-box connectivity and error handling’. This is one of such posts and more to come.

Task –

Load million records from a source table to a destination table. Out of the million records, there are about 10 error records. Source Table would be a constant. There would be two runs – first run with table in Oracle database as destination, second run with table in a SQL Server database as the destination.

I will use both SSIS and Informatica to accomplish this task and see how best both accomplish the task.

Setting up of Source –

I have Oracle 11g express set-up on the machine. My source table is simple table consisting of Id, Name columns with about a million records in it. Id is basically a auto-incrementing column. After it’s populated I am running a script to  update 10 random records to null. Below SQL code performs this –


--Source table to hold the million records
create table SrcEmployeeData
(
 Id int
 ,Name varchar(50)
);

--Insert million records in it. The value level auto-increments
insert into SrcEmployeeData
select level, 'test' from dual connect by level <= 1000000;

--Update 10 random records with Name as NULL
update SrcEmployeeData
set Name = null
where Id in
 (
 select Id
 from (
 select *
 from SrcEmployeeData
 order by DBMS_RANDOM.VALUE
 )
 where ROWNUM <=10
 );

--Commit the data
commit;

Once done, confirm to check there are 1 million records with about 10 records having NULL in Name column.

Task 1 – Tool – Informatica, Destination – Oracle
Here is the definition of destination table in Oracle

create table HR.EmployeeData(
    Id int
   ,Name varchar(50) not null
);

 

 

I created a mapping called m_Load_EmployeeData_Oracle which contains just two  transformations – Source and Destination as shown below –

1_1M_OracleDestinationLoad_Mapping

I then developed a workflow for it wf_m_Load_EmployeeData_Oracle. I then set the Connections in the ‘Config’ section of the session as appropriate and started the workflow. Here is the result –

2_1M_OracleDestinationLoad_Error.png

So right-off the bat, without any tweaking, the workflow got successfully executed and correctly captured the 10 error records with a execution time of 1m 33sec.

Task 2 – Tool – Informatica, Destination – SQL Server

I created destination table in SQL Server with same table definition as in Task 1. I then created a new mapping m_Load_EmployeeData_SQL with same mapping i.e. source connected to destination as shown below –

3_1M_SQLDestinationLoad_Mapping

Created the wofklow wf_Load_EmployeeData_SQL followed by setting appropriate ‘Connections’ in the session properties. Ran the workflow and here is the result –

3_1M_SQLDestinationLoad_Error

10 records successfully captured without any ado and in the same time. Absolutely no difference in time.

Task 3 – Tool – SSIS, Destination – SQL Server

Source still remains the same. Here comes the real kicker even before we get into comparison. If we set-up the DFT with a simple ‘OLE DB Source’ and ‘OLE DB Destination’ components and start the package, it is bound to fail as shown below –

5_1M_SQLDestinationLoad_Package_Error.png

Out-of-the box error capturing just doesn’t exist. Here is what I have done to capture the error records using the 3 DFT technique. This is how it works. Create three copies of your ‘OLE DB Destinations’. Connect your source to the first DFT. Set the properties ‘Rows per batch’ and ‘Maximum insert commit size’ values to 50000 as shown below –

7_1M_SQLDestinationLoad_Package_OLEDST_Setting

Under mappings, the columns would then be auto-mapped by name. Connect the ‘Error Output’ of the first DFT to the second DFT copy. Set the two values for that as 10000. Connect this second DFT’s error re-direction to the third DFT copy. For the final one set, the two values as 1. Rename the tasks appropriately.

Now drag in another DFT, and plug in the third copy of the DFT error output on to this new one. This time we are going to create a new destination table for capturing the error say ‘dbo.EmployeeData_Error’. The table definition is as listed below –


CREATE TABLE [dbo].[EmployeeData_Error](
[ID] [numeric](38, 0) NULL,
[NAME] [nvarchar](50) NULL,
[ErrorCode] [int] NULL,
[ErrorColumn] [int] NULL,
[DateInserted] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmployeeData_Error] ADD CONSTRAINT [DF_EmployeeData_Error_DateInserted] DEFAULT (getdate()) FOR [DateInserted]
GO

At the mapping level, ensure to connect the ErrorCode and ErrorColumn of the third copy of DFT wherein you are setting both values to 1, to the same columns of the table EmployeeData_Error as shown below –

10_1M_SQLDestinationLoad_Package_OLEDST_ErrorCapture.png

With this being set-up, the package is executed with no problem whatsoever as shown below-

11_1M_SQLDestinationLoad_Package_Execution

Looks hale and hearty. How about the speed of execution –

11_1M_SQLDestinationLoad_Package_Execution_Speed.png

It completed in just 14 seconds. The fastest so far in the tasks we performed. Now let’s go to the final task.

Task 4 – Tool – SSIS, Destination – Oracle

Before we get into the package set-up, one needs to make sure the Oracle Provider for OLE DB is present. If not, get the latest stable version from the following link. The package set-up would be very simple this time around. In total there would be three tasks – OLE DB Source for fetching the data, 2 OLE DB Destinations (1 for the destination and the other for capturing the error).

The OLE DB Destination for storing the target data will need to have the ‘Data access mode’ set to – ‘Table or View’ and one cannot use ‘Fast Load’ option. Below image shows the set-up that needs to be done –

12_1M_OracleDestinationLoad_Package_OLEDTSSetting.png

With that set, here is the snapshot of the package run –

16_1M_OracleDestination_Package_Execution

And the execution speed –

16_1M_OracleDestination_Package_ExecutionResults

Almost an Hour for just a million records. One whole hour for a dataset containing only two columns. I had a job wherein I was transferring about 2 million records from a much larger table and we had similar requirement to capture the error records. That package ran for almost 3 hours to complete. Anything that is not set to SQL Server as destination, SSIS performs horribly.

Conclusion-

SSIS offers very poor performance for data loads which differs from SQL Server. If you want more robustness, one needs to look outside for any third-party tool. Informatica on other hand just doesn’t care what the source and destinations are. It just works as expected giving uniform performance.

 

Visual Studio – “Build must be stopped before the solution can be closed” error

Time and again I have been getting this nasty error for no obvious reason as seen below –

BuildMustBeStopped

The first step I tried is to stop the build by going to – Build-> ‘Cancel Build’. That just doesn’t work. So here are the scripts I run to close my session –

$p = get-process devenv
Stop-Process $p -WhatIf
Stop-Process $p

I first get the process id in the variable. Check to see if what I need to close is what I intend and then proceed to stop it. That’s all there is to do. You now need to open a new instance of Visual Studio and get cracking on whatever it is that you are working on.

SSRS Report Migration 2012+ – Method 1

As Microsoft goes on rampage shortening their release time and sending not one but 3 versions in the last 6 years (2012, 2014, 2016!), it is time for us to have a look at various methodologies available to migrate reports from one ReportServer to another ReportServer. The reason I have brought this up is because for all my working career, the only tool that I had used to migrate reports is RSSScripter.exe. The original link is archived and is available from the following link – RS Scripter. This one works good for till SQL Server 2008 R2. What about the versions after that?

That’s the answer I want to solve through this post. In my quest to find out how to build a a package ready for deployment and further automating it, one of the first that I have got hands on is this – SSRS Powershell Deploy.

It’s bunch of powershell scripts that can be utilized to deploy the reports. The biggest downside to this method though is you can’t pick and chose the reports to deploy. You either deploy all or none at all. If the solution can be analyzed well enough, we may come up with a workaround for that. For now, let’s deep dive into, how to set the solution up and steps involved in deploying –

Once you open the link, click on ‘SSRS-1.3.0.zip’ folder. Follow the below steps –

1. Download the .zip from https://github.com/timabell/ssrs-powershell-deploy/releases/latest
2. Right-click the zip file in windows explorer, click “properties”, and then click “Unblock”.
3. Create folder ‘Documents\WindowsPowerShell\Modules\’
4. Open up the zip file, copy the SSRS folder, paste it into
`Documents\WindowsPowerShell\Modules\`. (Or somewhere on your
`$env:PSModulePath`)

You can test if you are having the modules imported or not by running a simple powershell command as shown below –

1_SSRSPowershellImport.png

As the names imply, if you want to deploy the SSRS Project (i.e. .rptproj file), then use Publish-SSRSProject else if you want to deploy the SSRS Solution (i.e. .sln file) then use Publish-SSRSSolution.

Using this let’s do one sample report deployment. I will first show a simplest way to deploy i.e. by using pre-filled Configuration data.. I have a project OpenDataReports containing one report ‘Top 10 Products.rdl’.

Once the solution is opened, go to Project-><<ProjectName>> Properties. The properties page opens up.
Perform the following changes as shown below –
Configuration – Set it to Release
TargetServerURL – http://<<MachineName>>/ReportServer<<_InstanceName>&gt;
In addition to that the other settings can be set as well, i.e. TargetDatasetFolder, TargetDataSourceFolder etc.

2_SSRSProjectProperties.png

With the properties set, now right-click on the Project and click on ‘Rebuild’ as shown below. Ensure it succeeds. –

3_SSRSProjectRebuild.png

Go to the project path and /bin/Release folder to ensure all the required reports and Data Sources are present. In my case here are the contents in the folder –

4_SSRSProjectbinReleaseContent.png

Finally the next step is to run the powershell command as shown below –

The command to run is a simple one –

Publish-SSRSProject -Path "<<FilePath>>\OpenDataReports.rptproj" -Configuration Release -Verbose

5_SSRSProjectDeploy.png

That’s the simple way to go about deploying reports. Of course, one need not do the pre-config settings, if you look at all the parameters provided for the script, each of the project parameters can be set at run time. I will posting a example of that in the next post.

Meanwhile, do leave a comment on how deployments are happening in your environments.

 

 

Bye Bye SQL Profiler – Welcome Extended Events

SQL Profiler for long has been THE go to tool for tracing the queries and if you are a SQL Developer it would be a miracle if you haven’t used it at all. In every facet of BI stack, this comes into play be it a SSIS package that is currently run, or in understanding a blank SSRS report or a Cube that is getting processed at the background one can hook up a trace as the first line of debugging.

The biggest problem with this though is that it ALWAYS had to be used within a limited time. You extend it longer than intended and all the activities on the SQL Server tend to slow down as it is a resource-intensive operation.

Sensing this I believe, Microsoft first came out with Extended Events with SQL Server 2008 version. It was horrendous to say the least. At that time this had to be entirely done through bunch of scripts, joining multiple tables with addition of XQuery to grab the actual data that we need. The learning curve to get this done was huge. I admit I had read various tutorials, did some practice but when I really wanted to use it I used to get cold feet. Without googling at least twice, this was a no-go and I used to fall back on SQL Profiler.

SQL Server 2012 onward, Microsoft has introduced GUI for Extended Events making it now a real easy breeze to work with. This has now been the de-facto tool that I use for tracing the queries now.

Here is how one can go about setting one up and using it to trace your SQL queries –

Log on to SQL Server and go to Management->Extended Events->Sessions. Right-Click on ‘New Session Wizard’ as shown below –

1_Opening_NewSessionWizard.png

Click ‘Next>’ on the Introduction screen. In the ‘Set Session Properties’ tab give a name to the Session say – All SQL Queries as shown below and click on ‘Next>’

2_SetSessionProperties.png

In ‘Choose Templates’ tab click on ‘Use this event session template:’ and in the drop down select ‘Query Batch Tracking’ as shown below and then click on ‘Next >’ –

3_ChooseTemplate.png

In the ‘Select Events to Capture’ tab across the ‘Selected events’: you can remove error_reported, rpc_completed and only have sql_batch_completed in it as shown below and click on ‘Next >’. You can remove the other two that come by default by just clicking on them and using the ‘<‘ arrow button –

4_SelectEventsToCapture

Keep the defaults as they are for the subsequent screens and click on ‘Finish’ in the ‘Summary’ page. In the ‘Create Event Session’ page that pops-up, to start seeing your results immediately, you are provided with two options. Enable them and click on ‘Close’ as shown below –

5_CreateEventSession.png

Upon doing that the trace is now up and running waiting for the queries to start. Here is the output of showing one query that was run against one database in the server –

6_QueryResult

As can be seen this one is more cleaner and more easier to read without the unnecessary redundant information that used to come along from SQL profiler.

This is just a tip of the iceberg from what we can achieve using Extended Events. There is an ocean out there to explore but for those who are seeking to trace out a query this should be a good start.

 

 

 

 

SSIS Lookup Gotcha – Test Fully

The task I had on my hands was simple. I had to perform lookup on a target table with my source data and get all non matched data. The data that I was fetching for the lookup from target had additional string manipulation done to obtain the required lookup data. I then went on to write a query to fetch that data and testing on a sample subset (using TOP 10) to check for it’s correctness and went on to implement.

I set everything up and when I ran the package the lookup was just now working. The rows that I expected to have a match were simply getting redirected to no-match. When I started debugging, it then became apparent that the query that I had used to retrieve the dataset in the lookup was an incorrect one. This time around I ran the query without the subset and the query failed to get executed showing me the actual error.

Moral of the story is one should not rely on ‘Preview’ results offered from the ‘Lookup’ transform or use a subset when sampling the data. It should be checked to see if the query works for the entire set.

Let me illustrate this with an example.

Below is a sample of Source Data –

 

FileName
Test1.txt
Test3.txt

Here is the data that I am doing a lookup on from a table say dbo.LookupTable (a simple Id and FilePath column) –

1_TargetDataSample
The string manipulation query for this is as you might have already guessed, to get FileName from the ‘FilePath’.

select Id,RIGHT(FilePath, CHARINDEX('\', REVERSE(FilePath)) -1) as 'FileName'
from dbo.LookupTable;<strong>
</strong>

I have a simple Data Flow Task which does the following –

  1. OLE_SRC – Get Source Data – Gets the source data as shown above.
  2. LKP – Get Id – Using the query above, it fetches the Id and FileName. Note that the moment you put that query and go to ‘Columns’, it will throw up an error. For the purpose of this illustration just ignore it and do the mapping.
  3. Trash Destination – A priceless open-sourced transformation from Konesans and a must  have development aid.

Here is how the package looks like after running it-

2_DataFlowTaskContents

As can be seen only 1 row gets shown as matched even though there are 2 matching rows.

The data in this sample illustration is very small but the original data that I had about 100k records and it was difficult to debug on why this error occurred.