How to view error(s) that occur during Master data Service’s Staging Process?

To view error(s) that occur during MDS’s staging process, we have two views: stg.viw_name_MemberErrorDetails & stg.viw_name_RelationshipErrorDetails. For the purpose of this blog post, let me show you how I (as an administrator) view information from stg.viw_name_MemberErrorDetails.

1) Name of the entity: supplier & Name of model is Suppliers

2) I imported data into staging table stg.supplier_leaf via SSIS

3) Now here’s how you can see the errors that occur during MDS’s staging process:

we can go to MDS web application to initiate the staging process > start the batch > after completion you can see the status as well as if it has any errors or not:

4) Now if you see that there’s an error, then you can go to stg.viw_name_MemberErrorDetails to see what are the errors. In my case, I am going to run the query:

select * from stg.viw_supplier_MemberErrorDetails where Batch_ID=2

You can get the above query via MDS web application too:

5) And as you can imagine, you can get access to this error data via SSIS (SQL Server Integration services) too. So if you have a workflow that a. Loads data to MDS and b. initiates the Batch process via Stored Procedure; then you can program it to get access to errors from the stg.viw_name_MemberErrorDetails & stg.viw_name_RelationshipErrorDetails tables.

That’s about it for this post! I hope this helps. Your comments are very welcome!

Master Data Services: Analogy to remember what are Models, Entities and Attriutes (parasdoshi.com)
Here is the official resource: http://msdn.microsoft.com/en-us/library/ff486990.aspx
Matching activity in Data Quality Services in action! (parasdoshi.com)
Microsoft HDInsight Preview for Windows: How to use Sqoop to load data into HDFS from SQL Server? (parasdoshi.com)

How to standardize data using Data Quality Services?

I was playing with Data Quality Services when I though that it would be nice if I could implement what I had learned and create a quick demo that shows how to standardize data using the technology. So here it goes:

Note: This is not a step by step Guide – I am assuming that you are aware of different pieces of DQS. I am going to post a brief outline of how did I go about implementing a quick demo.

Technology used:

1) SQL Server 2012 Data Quality Services

2) SQL Server Integration services to show the DQS cleansing component in action.

Problem:

We want to standardize to Gender to have only two values M & F – but now data coming from two files creates data inconsistency issues:

Two files that look like this:

File 1: (Excel)

File 2: (CSV)

Solution:

Let’s see how SQL server 2012’s data quality services would help us solve this problem:

1) I created a Knowledge base with a domain Gender and created domain values that looks like this:

2) Tested the Knowledge base quickly by creating a Data Quality Project. It worked!

3) Switched to SSIS and created a Data Flow which uses following components:

What would be interesting to you to see mapping tab in the DQS cleansing transformation component, so here it is:

5) And let me show you the Target File – note that you can choose to NOT have Gender_Source, Gender_Status & Record Status. The column of interest is Gender_Output

That’s about it for this post. I hope this helps!

How to Delete a Knowledge Base in SQL Server 2012 Data Quality Services? (parasdoshi.com)
How I think of what is KnowledgeBase in SQL Server 2012 Data Quality Services (parasdoshi.com)
Guest Blog: How we use Fuzzy Lookup add-in in our company to solve data inconsistency problems: (parasdoshi.com)
Master Data Services: Analogy to remember what are Models, Entities and Attriutes (parasdoshi.com)

How to import data from Excel file to SQL Server:

In this post, we would see how to import data from Excel file to SQL Server using Import and Export Wizard, we would import data from four excel worksheets to SQL server. Here are the steps:

1) Right click the Target Database > Tasks > Import Data to open the SQL Server Import and export wizard

2) select the Data Source. In this case, Excel file. You would select the worksheets to fetch data from later, for now – select the excel file as shown below:

3) choose the Target Database:

4) Select the tables (in our case- worksheets) or specify a query. For the purpose of this demo, we would go with “copy data from one or more table or views” option:

5) Select the worksheets from the Excel file and also specify the tables in the target systems. If the tables in the target database are not already created, then would be created by this wizard:

6) You have the option of running this wizard immediately or you have the option of creating a SSIS package and then run it later. For the purpose of this post, we would select Run Immediately

7) Review the information and click Finish

8) After successful execution:

Conclusion:

In this blog post, we saw how to import data from Excel file to SQL server.

Guest Blog: How we use Fuzzy Lookup add-in in our company to solve data inconsistency problems: (parasdoshi.com)

Visualizing what Merge Transformation in SQL server Integration Services (SSIS) does:

Visualizing stuff is powerful – if you have visualized something then you can remember it for a longer period of time. So in this post, we will visualize what the Merge Transformation in SQL Server Integration services (SSIS) Does:

Note:

1) Do not Confuse it with Merge Join Tranformation. “Merge Join” is NOT same as “Merge”

2) Merge Transformation can accept only TWO (sorted) data sources. Whereas if you want to “combine” more than two data-sets, then go for Union ALL tranformation

Summary of Just Learned Tips that i have submitted for year 2011 at BeyondRelational.com site

Just learned is a section in the BeyondRelational site that is meant for members to post a nuggets (short) of knowledge. I like this section – and so time to time i open the section and browse through the posts and once in a while, contribute. Here is the list of tips that i submitted this year:

1. [SSIS] Execution of an SSIS package that has Excel Desti/Source in 64bit SQL server will throw an error and Workaround for this

2. In windows server 2008 R2, IE asks you to add each website you visit to allowed site list. I learned how to override it for my demo machine.

3. Just learned how to allow file downloads in IE in Windows server 08 R2

4. SQL Server PowerPivot for SharePoint not available with standalone sharepoint

5. SQL Azure’s next service release will take the first step towards having common feature set between cloud and SQL server Denali!

6. Deploying azure web roles just got faster with “Windows Azure Accelerator for Web Roles”

7. Windows Azure toolkit for social games – makes developing social games on windows azure platform just got easier and quicker

8. General – Lorem ipsum has no meaning and just used this phrase to redirect attention

9.[Azure] a MSDN subscriber gets an alert email in case the Azure usage crosses the allowed usage

10. Press F11 on IE and Firefox to view web pages in full screen mode

11. never let your domain name expire

12. [Azure] One can map (unfriendly) azure storage domain to a custom domain name (Easily)

13. The new recognition system on MSDN, Technet and Expression profile is powered by windows Azure!

14. Posting code in wordpress.com

15. [Azure] Charges dropped for ingress (inbound traffic)

16. [Azure] One can combine a web role and a worker role

17. SQL Azure – currently, Maximum 6 servers are supported per subscription

18. Azure will charge you until you delete your hosted service

19. Atleast 2 instances of a role are required to meet the Azure SLA requirements

20. a DMV [sys.dm_db_partition_stats] in SQL Azure to calculate size of DB

Upload CSV and excel files to SQL Azure via “Data Transfer” tool

[UPDATE 9/12/2012: The URL’s that point to the service does not seem to work. The service may be out of “lab phase” and hopefully we would see this integrated in Portal or as an Independent service.]

Microsoft recently announced a tool (which is in CTP) called “Data Transfer” on SQL Azure Labs. It let’s you transfer CSV and excel data to SQL Azure or Windows Azure blob. I think this service would make our task easier than before. Earlier, We had the option to use SQL server integration services (SSIS) or BCP to transfer excel files or CSV files to SQL Azure. It was straightforward too but what if we had the tool in which you just point it to your excel/csv file and it would do try to do the rest. wouldn’t that be easier? Yes, so Data Transfer aims to achieve exactly that. To test drive Data Transfer tool go to https://web.datatransfer.azure.com/

In this blog post, I will show you how to upload an excel file to SQL Azure via this tool:

1. After you have registered yourselves (via a Live ID) on https://web.datatransfer.azure.com/ – you will see

2. In my case, since I wish to transfer an excel file to SQL Azure, I am going to go with first option i.e. Microsoft SQL Azure

3. In the next step, provide the credentials of a SQL Azure DB (that exists already)

You have the option of saving a connection. And the drop down that you see in the above image will let you see all your saved connection.

4. In the next step, point to your file and select appropriate options via the Advanced options which has the File Encoding, Column Delimiter, Row delimiter and Text Qualifier options. So this are useful if you had a flat file with {tab} instead of {,} to separate column values. In my case, I am going to leave the advanced options to their default values. Point to the excel file and since my first row has column names – I am going to check the box that says “column names in the first data row” and then click on analyze.

5. if you had not checked the “Edit table defaults” box, then Done! you can view the status of the process from the My Data tab. It’s this easy.

If you checked the Edit table defaults, the next page would allow you to edit the data-type. The power of this tool resides in the fact that it guesses the data-type and assigns it to the data that we had specified. And if you want to over-ride the data-type chosen by the tool, you can do so here at this page:

BTW: I find this amazing!

6. Click on save and the data is ready to be uploaded. it’s that simple. And after the upload, you can view the details here on the “my data” page:

7. You can log into the SQL Azure manage portal and browse the tables. And after upload, the link to “manage” these tables will also be available in the “my data” page.

Conclusion:

As you imagine, the process is simple and “Data Transfer” tool makes it easier to upload CSV/Excel file to SQL Azure. And it also let’s you upload a file to Azure blob storage account. Give it a spin! URL: https://web.datatransfer.azure.com/

And learn more about it here: http://www.microsoft.com/en-us/sqlazurelabs/labs/datatransfer.aspx

[SSIS] unzip a file in SQL server Integration services (SSIS) package

An assignment I was working on recently required me to unzip a file in an SSIS package. I didn’t knew it – so I asked my mentor Rushabh Mehta sir about it and he was kind enough to email me a demo. Now based on the deemo, my next task was to input couple of configuration’s like location of zipped file and then i was good to go! And so i learned something new, and I just thought of documenting it.

Aim: To unzip a file in an SSIS package

SSIS Task used: I’ll be using the Execute process task to carry out the task.

Now, here is the configuration of the Execute process task.

Executable: C:Program FilesWinRARWinRAR.exe

[complete path of the executable. I have used WinRar to unzip a file – you could any valid tool you wish]

Arguments: e voila.zip -o+

[General format: e <filename> –o+]

WorkingDirectory: D:blogpsot

[Path of the working directory. The file will be unzipped at the same location. If you wish to unzip at different location, you could use something like:

e “D:blogpostvoila.zip” “D:blogpost2” –o+

and leave the WorkingDirectory blank, Then in this case the file will be unzipped at D:blogpost2 ]

Now, here is the screenshot of the configuration. Just in case:

So yeah, happy unzipping!

Insight Extractor – Blog

Paras Doshi's Blog on Analytics, Data Science & Business Intelligence.

SQL Server Integration Services

How to view error(s) that occur during Master data Service’s Staging Process?

Like this: