When we start designing our model using Master Data Services it is pertinent that we know how we will receive data from clients. A problem came before me when I had to upload a suppliers list to Master Data Services. I had created a Recursive Hierarchy with a domain based attribute of ParentSupplier as shown in the figure below.

Here I have created the Recursive Hierarchy:

Create Recursive Hierarchy

Here I have defined the entity as Recursive (ParentSupplier is domain-based attribute):

Edit Recursive Hierarchy

As during the designing of Model I had set the option of Create Explicit Hierarchy as No so in Staging schema only one table was created with the name stg.EntityName_Leaf . This is the table where we dump all our records which we want to become part of the Master Data.

Problem:

There are two problems which can arise with recursive Hierarchy.

Consider we have two rows where one Supplier is a parent and the other is a child supplier. If the Child row is the first row while the parent row is below it, then MDS has no clue that the given data has a parent record which is just below it.

If we are using Autogenerated Codes for Suppliers then there is no way to replace ParentSuppliers with these Codes as MDS will only match Codes to define relationship.

So what should we do? To overcome these limitation I had to sort all records in the Staging Table so that no child record exists before it’s parent record. Apart from that I had to let go of the Autogenerate Code feature and use the SupplierName as the code.

Problem Solved? No! The guys who had to dump the data in Staging Tables dynamically through ETL had a hard time sorting the data especially when the data was multiple level deep.

Solution:

To overcome these problems we use Explicit Hierarchies. To do this you must select Yes in Create Explicit Hierarchies option in the Entity screen. Also now you can delete the ParentSupplier domain-based attribute. Your entity should now look something like this:

Explicit Hierarchy

Now when you look at the Staging tables the Leaf table contains no ParentSupplier Column. There are two more staging tables for your entity(stg.EntityName_Consolidated, stg.EntityName_Relationship). These tables will be used to create the hierarchies now. Now follow these Steps to get your Suppliers in the Master Table.

Dump all Suppliers which are the leaf records(or in simpler words have no child records) into the stg.EntityName_Leaf table. There is no need to specify the ParentSupplier attribute here as hierarchy will now be handled separately in Relationship table. Run this batch so all leaf members are part of the MDM now. You will find these records in the MDM in the Entity Table with EN postfix.

Dump all the Suppliers which are Parent records(or in simpler words have child records) into the stg.EntityName_Consolidated table. In this table we add all the information for members which are the parent. Run this batch so all Consolidated members are now part of the MDM now. You will find these records in the MDM in the Entity Table with HP postfix.

Dump all Supplier relationships into the stg.Supplier_Relationship table. In this table give the Explicit Hierarchy name and ParentCode and ChildCode. Run this batch so all relationships are created between members. You will find these records in the MDM in the Entity Table with HR postfix.

Now you can create subscription views and use those views to get members with Parent suppliers.

I hope this post was of some help to anyone. For more information visit these links:

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section.

UDFs that return tables can be treated as another rowset.This can be used in JOINs with other tables.

Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowsetoperations.Procedure can return zero or n values whereas function can return one value which is mandatory.

Stored Procedure

A Stored Procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

User defined functions have three main categories:

Scalar-valued function – returns a scalar value such as an integer or a timestamp. Can be used as column name in queries.Inline function – can contain a single SELECT statement.
Table-valued function – can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can’t enclose the logic for getting this rowset in a single SELECT statement.

A few days back I had the weirdest problem ever. I used a thirdparty grid “Flexigrid” and hooked it up with my custom AMSX webservice. I implemented all Add/Edit/Delete functionality via webservices. It worked fantastically! I uploaded it to production server. The grid showed the data via webservice but somehow the Edit/Delete functionality would not work. No error message was visible.

After countless hours searching for a solution I found the solution to this problem. The problem was that HTTP GET and HTTP POST are disabled on Servers by default due to security reasons. To allow our application to access webservice we must enable HTTP Get and HTTP POST. To this we can either edit machine.config or update the web.config in the root folder of our webservice. Add the following lines in the web.config:

When HTTP POST and HTTP GET are disabled on a server the default service help page for the XML Web service will continue to work, but a prospective client will not be able to test the XML Web service using the Invoke button on the service help page. To debug the XML Web service in Microsoft Visual Studio .NET, you must create a test client program. For a production XML Web service, both of these drawbacks are easily overcome because Visual Studio .NET makes it easy to create a client to an XML Web service with the Add Web Reference command.

Hope this was useful to someone. Please leave your comments below and let me know if this was helpful or not.

A common misconception about DELETE and TRUNCATE statements is that they do the same thing. This is incorrect. In fact, there are many differences between the two.

DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted. You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your datarows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the contraints, TRUNCATE the table, and reapply the contraints.TRUNCATE will reset any identity columns to the default seed value. This means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity columns. After truncating your table, when you insert a new record into the empty table, the identity column will have a value of DELETE will not do this. In the same scenario, if you deleted your rows, when inserting a new row into the empty table, the identity column will have a value of 265.

DBCC CHECKIDENT can reset the identity value of a table. Lets say tblUser has 5 rows with last identity as 5, if we want the next identity to be 20 we just need to write

DBCC CHECKIDENT (tblUser, reseed, 20)

If table has to start with an identity of 1 with the next insert then table should be reseeded with the identity to 0. If identity seed is set below values that currently are in table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.

These questions which I found over the internet are some real brain teasers. Have a go at them😉

Given a rectangular (cuboidal for the puritans) cake with a rectangular piece removed (any size or orientation), how would you cut the remainder of the cake into two equal halves with one straight cut of a knife ?

You’re given an array containing both positive and negative integers and required to find the subarray with the largest sum (O(N) a la KBL). Write a routine in C for the above.

Given an array of size N in which every number is between 1 and N, determine if there are any duplicates in it. You are allowed to destroy the array if you like.

Given an array of characters which form a sentence of words, give an efficient algorithm to reverse the order of the words (not characters) in it.

How many points are there on the globe where by walking one mile south, one mile east and one mile north you reach the place where you started.

In a X’s and 0’s game (i.e. TIC TAC TOE) if you write a program for this give a fast way to generate the moves by the computer. I mean this should be the fastest way possible.

A version of the “There are three persons X Y Z, one of which always lies”..

There are 3 ants at 3 corners of a triangle, they randomly start moving towards another corner.. what is the probability that they don’t collide.

If you are on a boat and you throw out a suitcase, Will the level of water increase.

There are 4 men who want to cross a bridge. They all begin on the same side. You have 17 minutes to get all of them across to the other side. It is night. There is one flashlight. A maximum of two people can cross at one time. Any party who crosses, either 1 or 2 people, must have the flashlight with them. The flashlight must be walked back and forth, it cannot be thrown, etc. Each man walks at a different speed. A pair must walk together at the rate of the slower mans pace.Man 1:1 minute to crossMan 2: 2 minutes to crossMan 3: 5 minutes to crossMan 4: 10 minutes to cross

You have 5 jars of pills. Each pill weighs 10 gram, except for contaminated pills contained in one jar, where each pill weighs 9 gm. Given a scale, how could you tell which jar had the contaminated pills in just one measurement?

One train leaves Los Angeles at 15 MPH heading for New York. Another train leaves from New York at 20mph heading for Los Angeles on the same track. If a bird, flying at 25mph, leaves from Los Angeles at the same time as the train and flies back and forth between the two trains until they collide, how far will the bird have traveled?

Imagine that you have 26 constants, labelled A through Z. Each constant is assigned a value in the following way: A = 1; the rest of the values equal their position in the alphabet (B corresponds to the second position so it equals 2, C = 3, etc.) raised to the power of the preceeding constant value. So, B = 2 ^ (A’s value), or B = 2^1 = 2. C = 3^2 = 9. D = 4^9, etc., etc. Find the exact numerical value to the following equation:

(X - A) * (X - B) * (X - C) * ... * (X - Y) * (X - Z)

You have 12 balls. All of them are identical except one, which is either heavier or lighter than the rest – it is either hollow while the rest are solid, or solid while the rest are hollow. You have a simple two-armed scale, and are permitted three weighings. Can you identify the odd ball, and determine whether it is hollow or solid.

WordPress

Allows the professional design of your blog

Very large selection of features (e.g. plug-in)

Many advertising partner marketplaces do not support Blogspot.

My conclusion

The blogger can fully concentrate on the heart of the matter: the blog!This requires the blogger to take into account that there are some limitations. WordPress blog is full of design and marketing possibilities for making money and therefore are much more diverse.In WordPress, the blogger must be technical to deal with issues related to technical updates or installs. Blogger on the other hand is much easier to maintain. For starters, a blog at Blogspot is sufficient.It was a real joy on blogging and would like to perhaps earn some money too, should a change to be considered in WordPress drawn. Fire free I’m curious about your criticism and additions in the comments!