4 tools making your life easy by generating TEST Data Load

As developer /QA it is always a challenge to check the application on different parameters and Data Load is one of them.

Sometimes it is not easy to generate the load manually and we may skip this very important process to check our application on different data load which may cause extra pain to our end users, customers and to our team as well.

To avoid this either you yourself write some scripts (I will share an easy way to generate data load by SQL Script in near future) or use some outstanding tools available in the market.

In this post, I am going to share 4 best tools available in the market which I have used and my feedback according to my experience.

You can also try with trial versions and proceed further.

1. Apex SQL Data Generator:

Apex  SQL Data Generator is one the simple tool which I tried. It provides a free trial version for play.

Apex_Test_Data_Geneator

Below are some points which I like about this tool. This tool is not an open source but you can try this with the free version.

  • Easy to use
  • Can generate Unique records
  •   option to allow NULL value in columns
  • option to generate (export) test data in SQL Script, XML,  CSV & JSON
  • Can insert data in Parent-child relationship maintain foreign key
  •   You can maintain the transaction  in the script
  •   capable enough to show different dependencies
  •   Preview data feature
  •    sampling is possible based upon the table

Although it is capable enough to generate millions of records easily I faced sometimes it is slow due to a different relationship.

I would recommend this tool if you want to try.

2. Redgate data generator tool

Redgate is another one of the famous tools available in the market.

Redgate_Test_Data_Generator

Below are some points which I like about the tool. This tool is not an open source tool. Although, you can try a free version.

  • You can generate (export) CSV
  • Easily maintain the transaction
  •   can generate rows in a batch
  •   Preview data feature is available
  •   can do sampling
  •   can use a regular expression
  •   A null value option is available
  •   a unique option is available

I like this tool as well but you need some time to understand it.

3. https://www.generatedata.com/

This is another interesting tool and a bit more advanced but simple.

generate_data_Test_Data

Below are the some features

  • Very easy to operate
  • can generate unique records
  • option to select data type like Names phone, city etc (user-friendly option)
  • Export is very advance  apart from regular export like SQL Script, XML, CSV, Excel, HTML this tool provides  generate code in JavaScript  & C# as well

Although, I didn’t find the option to maintain foreign key. In trial run you can generate at max 100 records so, you can try it easily.

4. https://www.mockaroo.com/

This is one of the advanced tools which I found so far. It gives you the various option of the data type.

mockaroo

mockaroo1

  • Easy to use
  • sampling is easy
  • the various option of data types as you can see in the above image
  • can export CSV, script, excel, Firebase etc
  • Preview data feature is available

You can generate 1000 records in the free trial. Although, I didn’t found relationship (parent-child) details in this.

I hope this might help you somewhere.

Enjoy learning and exploring new tools.

How to start with Microsoft flow ?

Dear Friends,

Microsoft Flow is one of the hot product nowadays and it is not new. It is in the industry from the last few years. In this article, we will try to explore Microsoft Flow and know how it can be helpful to you with an example.

I have changed my writing style in this blog to resolve all the question which you might have if you are new to Microsoft Flow.

What is Microsoft Flow?

Microsoft Flow as the name suggests it helps in building the workflow to resolve business problems or we can say that It helps to automate workflow to resolve business needs or problems. It is basically part of the Microsoft Office 365 suites and mainly created to Business users or Business Analyst to solve or automate their business needs without having knowledge of different programming language like Java C# etc.

It makes you smarter to solve your problem with doing automating the steps. It is a graphical based tool to design flow to resolve any problem.

What are the different scenarios where I can use Microsoft Flow?

Now, you might be thinking of where you can use this. below are several business scenarios where we can use flow.

Examples :-

Email or Mobile Notification

Suppose, you are a marketing team head and you want a notification on your mobile whenever any new lead is generated, then in this case Microsoft Flow will help you.

Folder Watcher

Suppose, you are working on Printer server and your task is to continue watch files in a folder and if any new file arrived in that DropBox folder then you have to print it and share in the email to the XYZ person, then in this case also you can use flow.

Data collector

The other example is to dump the twitter data in your database. For example, you want to monitor what are the different tweets are coming on your newly launched product or product features so you can analyze the sentiments.

Streamline approval
your employee added a Leave request in your portal then Flow will send an email to the manager and as a manager you can approve or hold or cancel that request either directly from outlook message of from Flow itself as per your decision the result will be updated in your portal and a notification email sent to requester employee.

There might be tonnes of examples, or situations where we can use Microsoft Flow to automate and ease our work.

How it is different from Logic Apps?

Now, as you might have gone through my previous blogs and you might be thinking it is just like Logic Apps. So, my answer is yes, it is same as Logic Apps because it is created on top of  Logic Apps. but there are various different points where it differs from Logic Apps.

Below are some of them.

1. Flow is Office 365 suits part and an Individual application for which you don’t need Azure Subscription but for Logic Apps you required Azure Subscription.

2. Flow is developed for Business users /Business Analyst so, you don’t need to write complex code or modify the existing thing use Visual studio to enhance or create but Logic Apps is mainly for developers or people who have programming knowledge so if may require they can change the visual studio code.

What is the first step to start Microsoft Flow?

To start the flow you need to sign up on website https://flow.Microsoft.com. If you are office 365 users then you can sign in with that account or use any other email like Hotmail, outlook to sign up.

Now, you might be thinking about the pricing. There are 3 different plans available, free, Flow Plan1,  Flow Plan 2. depends upon your need you can choose any plan.

Indiandotnet_Microsoft_Flow_Price

Once you opted the plan and sing In after login you will get following home screen to proceed further or we can say this would be your play area where you can create different flows.

Indiandotnet_Flow_Home

Just, make you aware there are various predefined templates available which are created based upon various frequently business needs

Indiandotnet_Flow_Template

How does it work?

Now,  as mentioned earlier it is most likely Logic Apps. So, similar to Logic Apps it has the various connector.

Just think if the concept “if something happened then we have to do something”. So, basically this is based on Trigger and then Action. concept.

Example, An email came to your inbox (Trigger) then (Action) send a mobile notification.

There are more than 200+ connectors available to help you and ease your work.

Indiandotnet_Flow_Connection

Can we have a step by step example to try it out?

Yes, and believe me this is one of the important examples for all the blogger and they will surely, like it.

For a blogger the challenge is promoting his/her post and for which he/she needs to create a shorten URL either on (goo.gl or bitly.com)  and then share on Facebook, Twitter or other social media channel.

Now, this is again a time-consuming process. Now, what you think if we can automate this process.

So, our challenge is whenever a new post is added in the blog then our flow identify that trigger and then provide that link to bitly.com and create a short bitly link and then post the generated link to social media (but in this example I created the notification to make it short and sweet choosing existing template ).

Now with the assumption, you already created your account in the Flow and logged in successfully. we proceed further.

Let’s follow the step here

Indiandotnet_Flow_WordPress_Step_1

Step 1: A an existing template “When a post is created in ”WordPress create a Bitlink URL and send the notification” as shown in below figure.

Indiandotnet_Flow_Wordpress_2

Step 2: Now, as you can see we have to configure the btily authentication and allow Microsoft Flow to access bitly.com to create URL. as shown in the figure.

Indiandotnet_Wordpress_step_3

Step 3:- Once you allowed it.  in the next step, we have to give authentication and authorization to access our WordPress blog. So, whenever a new post came flow will identify. so, just provide access as shown in the below figure.

Indiandotnet_wordpress_Step_4

Step 4:- so far so good we have given authorization for bitly & our WordPress blog.  we don’t need to configure the next step because it would be automatically configured. Now, we are good to go. we have configured perfectly in a few clicks, just save the Flow and check the flow using flow checker option.Indiandotnet_flow_test_Step

If there is no error you found in Flow Checker just click the Test option as shown in below figure.

Indiandotnet_Flow_Office_Step_8

Step 5: Now, go to your blog and create a post and publish it. You will be surprised that your flow automatically identified the new post and created a short URL and notify you as well.

You can see all the flow steps are checked it means the flow is successful.

indiandotnet_Flow_WordPress_11

Step 6: Now, you can check bitly.com and you can find the URL is created and also you got the notification.

Indiandotnet_Flow_Wordpress_12

Step 7: You can check each step detail as well by clicking the object

Indiandotnet_Flow_WordPress_Step_13

So we are good with the flow example.

Please, share your input on how you like the flow and this post  Smile

Happy learning

web scraping for analysis using the Microsoft Power BI tool

Whenever we talk about web scraping or web data extraction we always think it would be a difficult part and trust me to exact HTML code and fetch what is needed is a bit complicated not an easy task.

But Power BI made it very easy to extract data on which we can do the analysis.

Let me take an example here, suppose we want to create a tool which extracts data from different shopping site and compare them to get the best deal of particular product.

This extraction of the data and comparison can be easily done in a few steps in Microsoft Power BI.

Let’s understand it more using hands on using few steps as mentioned below in this post.

In this example, we are going to extract data from Amazon.in web site

Step 1:-  Open web data source and provide the URL of the web page from where you want the data. As shown in the below figure we are going to extract data from below URL

https://www.amazon.in/gp/goldbox?ref=nav_topnav_deals

import_data_from_Site_1

Step 2:  When you click on OK button you will get a new popup windows in which power BI tried to extract the data and provided you tables as you can see in below image there are two tables which Power BI provided Document & Table 0.

import_data_from_Site_2

but when you view those table you didn’t get any fruitful information. So , Power BI has a feature  to extract the data as per your requirement for that you have to click on the Button “Add table using Example”

Step 3:- After clicking the button you will get a new popup screen as shown in below image.

You can see there are 2 parts in the popup the first part is the web part view and you can see  the page from where we want to extract data is visible here.

The second part is table structure which we are going to create by giving a few example to Power BI.

import_data_From_Site_3

Step 4:- Now, Here we have to extract the Product Name, Price and percentage offer. So we are going to train the Power BI with 1 or 2 example. So , Let suppose Column1 will hold the Product name . As we can see the product Name in the web page is “Kevin 80 Cm”, “WOW Raw apple” etc. So , in the first column, we will try to write Kelvin  and what you will see a list of data which Power BI extracted. Just select exact name, just follow this step for 2 or 3 rows and you will be surprised to see that rest other row data will fill automatically.

import_data_from_Site_4

Step 5:- Now add another column in the below table  where we will keep the price of the items.

so as you can see for the Kevin 80 cm TV the price is 8,499. So, when you click and try to write the amount of 8499. Power BI is intelligent enough to provide the list of extracted data with similar value. Just try the same step for next 2 or 3 rows and you will find all the prices against the product name.

import_data_from_Site_5

Step 6:- Now, we will fetch data of percentage off . As you can see for kevin TV price is 39% off. so , to achieve this just add another column in the grid and try to type 39% . As you can see in the below image. Just repeat the step for few other rows and  Power BI will do for rest other rows.

import_data_From_Site_6

Step 7 : Wow, you have extract the data which you want. so far as you can see in below image.

import_data_from_Site_7

Step 8: Now, Just click on Load button to proceed further you will get the following screen.  Proceed further by selecting Custom Tables .

import_data_from_Site_8

Step 9: Now, great news you got the data whatever you want as you can see the grid

import_data_from_Site_9

Step 10 : The only thing which might be bother you  that in Percentage Off column you were seeing brackets and % Off  extra text as well. Now, to avoid this just add another column by example as you can see below screen.

import_data_from_site_10

Step 11:  That’s great now you have the data which you want.

import_data_from_site_11

Now, we have data so we can apply any analysis which we want.

Hope this feature might help you.

Happy learning !

Surprise ! Visual Studio 2017

Isn’t it surprising ? Microsoft announced Visual studio 2017 RC on 16-Nov-2016.

You can get download the RC version from following link

Download Link

Now , your question will be What’s new in Visual studio 2017 ?

To know more about Visual Studio 2017 check the release note of Visual studio 2017 with following link

Release note Link

I am pretty much sure you will enjoy this latest release.

Please, share your thought for same.

RJ

Here Comes New Idea of Split String in SQL SERVER 2016 #3

In the Series of SQL SERVER 2016, this is another post. Before Jumping in detail just think if you have a comma or other separator string and if you have to split it by separator field then for such task  in previous SQL SERVER versions either you will write a function which split the string and return desire values in a column  or

you will use XML function or  might be different custom functions.

Let me explain this with below example. Suppose you have a string like below

DECLARE @FriendList AS VARCHAR(1000)

SET @FriendList =’Ravi,Suyash,Vaibhav,Shyam,Pankaj,Rajul,Javed’

 

Now you want output like below

output_of

 

Then in such cases, you will  follow 2 approaches (their might be other as well)

Approach 1:- Write  a function like below  and use it.

Traditional_way_Split_String_Indiandotnet

And once this function is created you can use like below

use_of_Split_Function_in_SQL_Indiandotnet

Approach 2 :- You can use XML option in SQL SERVER as  shown in below

String_split_by_XML_SQL_SERVER

So, the good news is now in SQL SERVER 2016 you don’t need to write  so many lines to split any string. In SQL SERVER 2016 a new string function is Introduced which is

STRING_SPLIT

The use of this function is very easy and below is the syntax

STRING_SPLIT (string, separator)

Now, let me show you same output using STRING_SPLIT function

string_Split_Function_sql_server_2016_Indiandotnet

Isn’t it easy ?

I hope you will like this easy way to split the string.

Provide your feedback.

RJ !!!

Compress & Decompress–new Feature in SQL SERVER 2016 #2

This is another article in the series of SQL SERVER 2016 Journey . I am pretty much sure you might aware of Gzip Compression algorithm. If not then try  this link.

 

So, SQL SERVER 2016 introduce this two awesome functions for Compress & Decompress the data.

Before SQL SERVER 2016 version we have data compression feature like Page & Row compression (check Previous post for it Link )which is different then this column value compression.

 

In SQL SERVER 2016 Compress function,  data compression is done via GZIP algorithm and return VARBINARY(MAX).

 

Below is the simple syntax of Compress function

 

Compress (Expression)

Here Expression can be nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n)

 

Decompress function is just opposite of  compress function. It is used to decompress the value of VARBINARY which is converted using Compress function. The only tweak is you need to cast the output of Decompress function  in specific data type to make it readable (if using varchar ,nvarchar compression) .

 

below is the simple syntax of Decompress
Decompress (Compressed string)

 

Let’s understand this via an example as shown below .

 

Indiandotnet_Compress_Decompress_Feature_SQL

In this example I have taken 3 tables with exact same schema & data

 

  1. 1) IndiandotnetFriends
  2. 2) IndiandotnetFriends_Compress
  3. 3) IndiandotneFriends_Decompress

 

You can see  snap in which we are inserting same data.

As the name suggested in first table normal data from Adventureworks’s person table.

In second table we are inserting compressed value of first Name  and in 3rd table we are inserting decompress value of First Name from the Compressed table.

Now, let’s check compress  & decompress table data

Check_Compressed_Decompress_Datais

 

Now, Your might thinking that the output of both compress and decompress is not readable.

So you are right to make data readable of Decompress table we need to type cast.

See below snap for same.

 

Decompressed_value

 

Till now we know how to use this Compress & Decompress function. Now, let me share the benefit of using Compress. if you see below snap you will find that data length of compress is comparatively less than normal and decompressed data length .

 

DataLength_Indiandotnet

 

Obviously, compression helps you somewhere in the overall performance of your application.

The good point is  you can pass the compress data to your .net application and decompress using GzipStream as well.

 

The only thing which we need to take care is type casting. Suppose your base column which compressed is VARCHAR then you need to typecast again in VARCHAR.

 

Now, next question is where we can use this functions. So,  we can use in compressing large object like binary data in which we save jpg, pdf , word document etc..

 

I hope you will be excited in using this function.

 

Please, share your input.
RJ!

DROP IF Exists- A cool feature in SQL SERVER 2016

In the Series of SQL SERVER 2016 journey, this is our new article. In this article, we are sharing a new cool feature which introduced in SQL SERVER 2016 which is DROP IF EXISTS (DIE) .

In our development many times it happens that we need to drop a table and as a best practice we write the following syntax as shown in below figure

TraditionalWay_Indiandotnet

Now, in SQL SERVER 2016 the same task is super easy. You can write the following syntax to drop the table object

DROP_TABLE_IF_EXISTS_INDIANDOTNET

DROP TABLE IF EXISTS TABLENAME

The best part is if suppose the object does not exist then  here will be no error execution will continue.

Let me share one more example of Dropping a stored procedure.

DROP_PROCEDURE_IF_EXISTS_INDIANDOTNET 

Similar, way we can write for following data objects and with the following syntax

Procedure:-
DROP PROCEDURE IF EXISTS Procedure Name

Assembly:-
DROP ASSEMBLY IF EXISTS Assembly Name

ROLENAME :-
DROP ROLE IF EXISTS ROLENAME

TRIGGER :-
DROP TRIGGER IF EXISTS Trigger Name

VIEW:-
DROP VIEW IF EXISTS View Name

RULE:-
DROP RULE IF EXISTS RULENAME

Type:-
DROP TYPE IF EXISTS Type Name

Database:-
DROP DATABASE IF EXISTS Database Name

Schema:-
DROP SCHEMA IF EXISTS Schema Name

User:-
DROP USER IF EXISTS Username

SECURITY POLICY:-
DROP SECURITY POLICY IF EXISTS Policy Name

View :-
DROP VIEW IF EXISTS View Name

FUNCTION:-
DROP FUNCTION IF EXISTS Function Name

SEQUENCE:-
DROP SEQUENCE IF EXISTS Sequence Name

INDEX :-
DROP INDEX IF EXISTS Index Name ON Table Name

Synonym:-
DROP SYNONYM IF EXISTS Synonym Name

I like this feature I am sure you will also like this.

Please, do share your feedback for blog post.

Enjoy !!

Unbelievable SQL SERVER 2016 Feature you should aware.

 

Although, I know I am bit late to share this thing on our blog but it says in Indian proverb “Der aai durust aai” means it’s OK you came late but you came that is more important.

Anyways, so you all might aware that Microsoft launched SQL SERVER 2016 officially in June 2016.

You can download the SQL Server 2016 via Link.

Obviously, this is a new revolution in SQL SERVER series. You will find many great features in this version.

This post is beginning to explore all those great features and we will do deep dive in all those features. In this, post we briefly introducing those features. so, without wasting time let me share a brief introduction.

 

1) JSON in SQL SERVER :-

Is this surprising to you ? Obviously, yes. As you might aware that most of the NO SQL database use either JSON or XML. As XML feature already exists in SQL SERVER so this was time for JSON. You can play with JSON in SQL SERVER 2016.

 

2)Always Encrypted :-

If you talk about security this one the best feature. Now, you are thinking what it means. So, It means that the data in the SQL SERVER reside always in encrypted format and SQL server can perform the operation on the encrypted data without decrypting it. The encryption key can be exist in some other system. With this, feature you can secure you ensure that your data is secure from the person like DBA / Developer as well. These guys also can’t see the actual data. Isn’t it neat ?

 

3) Row Level Security :-

This is another interesting feature which helpful especially to the developers  who needs to write extra code to check this. Let me explain this with an example suppose you have a sales team who do market research and you want to restrict that each sales manager can see only those data which entered by him only in such cases you don’t need to write specific condition in your code. It can be achieved by Row Level Security.

 

4) “R” in SQL SERVER :-

For the data scientist, it is a great NEWS. As Resolution Analytics is purchased by Microsoft and It is incorporated in SQL SERVER. You can run R analysis query in SQL Server.

 

5) Temporal Table:-

The Temporal table holds the old version of ROWS of a table. It means that it maintain a copy of the old rows in the table whenever there is an update on the main table.

 

6) PolyBase :-

With the help of this feature, you can access data which exist in Azure Blob or Hadoop cluster using the same SQL server. In the nutshell, we can say this is the technology which combines both relational & non-relational database in a single umbrella which is SQL SERVER. You can run the query directly on external data like Hadoop or Azure blob storage.

 

7) Stretch Database:-

I am pretty much sure by the name you can guess this feature. So, with the Stretch database you have can store your part of the data in the cloud which depends upon your need. You can say most recent transactional data you can store in your local environment and other old data you can store in Azure.

 

8)Query store :-

Another interesting feature to help you in identifying  performance drag using Query store. When you enable this feature it automatically captures a history of queries , plans,  and statics and retain them for review and resolve the performance issues.

 

9) Mobile report:-

As mentioned earlier this is the revolution in SQL SERVER 2016. In SSRS there are many important changes introduced. Now we can import Power BI report in SSRS and apart from this you can create a mobile report which you can run on Mobile.

 

Now, we started officially SQL SERVER 2016 tutorial series.

 

Moving forward we are going to discuss all these features in details and also the couple of new things which introduced in SQL SERVER 2016.

 

enjoy.

Happy VijayDashmi.

Step by Step SSIS Multicast Transformation TIP #121

Dear Friends,

This is another post in the series of step by step SSIS ,Zero to hero series. In this post we will discuss another transformation control which is Multicast transformation.

Multicast is a way which help us to provide multiple copy of same source data for different transformation. Or in other words we can say multicast transformation create multiple pipelines for the same data.

Lets understand this by an example. Suppose, we are product developer and selling product in different state in India. Now ,the problem is same product may have different selling price due to different tax system. So, we have to use same data of product but there will be different discount according to sate tax.

Now, here we go step by step. for your information in this example we are going to use similar example which we did in last post of Derived column Transformation tip #120

Step 1:- Add a package and drag drop data flow task and configure source database. I am taking Adventurework2012 database and using product &  productCategory tables for source data query below is simple snap of data

Data1

Step 2:- Once the data is configured. Drag drop Multicast control  as shown in below figure and give output of source object to Multicast and try to configure it.

DragDropMulticast

Step 3:- Now, when we have configured Multicast we can get multiple output from Multicast control. Suppose , One state is taking 10% WAT tax, another state taking 20% WAT tax and last one is taking no tax (wow that’s great). So we can drag drop derived column and configure then according to 10% ,20% WAT tax for reference you can take a look of tip #120 

I configured the the 3 different output in same way and adding the respective flat files. As shown in below figure you will see we added 3 files

1) with 10% ,with 20 % and without and tax

FileDestination

After configuration you will get structure something like as shown in below image. As a result we will get 3 flat files with 10% ,20% and without any change.

finalDesign

Step 4:- If you face any difficulty in Derived column configuration and moving the data to file please follow earlier post. Now once everything is configured run the package by pressing F5 or hitting run button you will get following screen. So , if you see Multicast providing similar row count to different output which is 295 rows.

Final_Result

We , can use this Multicast where we need multiple copy of same source data.

I hope this article might help you somewhere.

Enjoy !!!

RJ !!