Tuesday 22 March 2016

XQuery Made Simple

Introduction
As I understand with my development career the XQuery is very important and Lot of Developer just not using it as they don’t like the complexity of XQuery. Here in this article I am trying to make it so simple that everyone can use it.
Hope it will be interesting.
The XML String that I Used
<Contact>
<Names>
<Name type="Good">
<First>Joydeep</First>
<Middle />
<Last>Das</Last>
</Name>
<Name type="Common">
<First>Joy</First>
<Middle />
<Last />
</Name>
</Names>
<Addresses>
<Address type="Office">
<Street>21 R Street</Street>
<City>Kolkata</City>
<State>West Bangal</State>
<Zip>799008</Zip>
</Address>
<Address type="Home">
<Street>SRR Street</Street>
<City>Kolkata</City>
<State>West Bangal</State>
<Zip>700016</Zip>
</Address>
</Addresses>
<Phones>
<Phone type="Mobile">8085554422</Phone>
<Phone type="Home">8085553399</Phone>
</Phones>
</Contact>


How to Learn it
Step – 1 [ Create the Base Table and Insert XML String ]
CREATE TABLE tbl_Employee
(EmpId      INT NOT NULL IDENTITY PRIMARY KEY,
 EmpDetails XML NOT NULL);
GO

INSERT INTO tbl_Employee
(EmpDetails)
VALUES('<Contact>
<Names>
<Name type="Good">
<First>Joydeep</First>
<Middle></Middle>
<Last>Das</Last>
</Name>
<Name type="Common">
<First>Joy</First>
<Middle></Middle>
<Last></Last>
</Name>
</Names>
<Addresses>
<Address type="Office">
<Street>21 R Street</Street>
<City>Kolkata</City>
<State>West Bangal</State>
<Zip>799008</Zip>
</Address>
<Address type="Home">
<Street>SRR Street</Street>
<City>Kolkata</City>
<State>West Bangal</State>
<Zip>700016</Zip>
</Address>
</Addresses>
<Phones>
<Phone type="Mobile">8085554422</Phone>
<Phone type="Home">8085553399</Phone>
</Phones>
</Contact>');


Step – 2 [ Understanding the Value() Method ]
SELECT EmpDetails.value('(Contact/Names/Name)[1]', 'VARCHAR(50)')
       AS EmpName
FROM   tbl_Employee;

EmpName
JoydeepDas


(Contact/Names/Name)[1]
<Contact>
<Names>
<Name type="Good"> à 1
<First>Joydeep</First>
<Middle />
<Last>Das</Last>
</Name>
<Name type="Common"> à 2
<First>Joy</First>
<Middle />
<Last />
</Name>
</Names>


SELECT EmpDetails.value('(Contact/Names/Name)[2]', 'VARCHAR(50)')
       AS EmpName
FROM   tbl_Employee;

EmpName
Joy


<Contact>
<Names>
<Name type="Good">
<First>Joydeep</First>
<Middle />
<Last>Das</Last>
</Name>
<Name type="Common"> à 2
<First>Joy</First>
<Middle />
<Last />
</Name>
</Names>


Check with others in same way.
SELECT EmpDetails.value('(Contact/Addresses/Address)[1]',
                        'VARCHAR(50)') AS Addresses
FROM   tbl_Employee;

Addresses
21 R StreetKolkataWest Bangal799008

SELECT EmpDetails.value('(Contact/Addresses/Address)[2]',
                        'VARCHAR(50)') AS Addresses
FROM   tbl_Employee;

Addresses
SRR StreetKolkataWest Bangal700016

SELECT EmpDetails.value('(Contact/Phones/Phone)[1]', 'VARCHAR(50)')
       AS Phone
FROM   tbl_Employee;

Phone
8085554422


SELECT EmpDetails.value('(Contact/Phones/Phone)[2]', 'VARCHAR(50)')
       AS Phone
FROM   tbl_Employee;

Phone
8085553399

SELECT EmpDetails.value('(Contact/Names/Name/First)[1]',
                        'VARCHAR(50)') AS EmpName,
       EmpDetails.value('(Contact/Names/Name/Middle)[1]',
                        'VARCHAR(50)') AS MiddleName,
       EmpDetails.value('(Contact/Names/Name/Last)[1]',
                        'VARCHAR(50)') AS LastName
FROM   tbl_Employee;

EmpName      MiddleName        LastName
Joydeep                                          Das

SELECT EmpDetails.value('(Contact/Names/Name/First)[2]',
                        'VARCHAR(50)') AS EmpName,
       EmpDetails.value('(Contact/Names/Name/Middle)[2]',
                        'VARCHAR(50)') AS MiddleName,
       EmpDetails.value('(Contact/Names/Name/Last)[2]',
                        'VARCHAR(50)') AS LastName
FROM   tbl_Employee;

EmpName      MiddleName      LastName
Joy


Step – 3 [ Understanding QUERY() Method ]
It not returns the Values but returns the XML portion of string.
SELECT EmpDetails.query('Contact/Names/Name[@type="Good"]/First')
       AS EmpName,
       EmpDetails.query('Contact/Names/Name[@type="Good"]/Middle')
       AS MiddleName,
       EmpDetails.query('Contact/Names/Name[@type="Good"]/Last')
       AS LastName
FROM   tbl_Employee;

EmpName                        MiddleName     LastName
<First>Joydeep</First>  <Middle />          <Last>Das</Last>

Contact/Names/Name[@type="Good"]/…

<Contact>
<Names>
<Name type="Good">
<First>Joydeep</First>
<Middle />
<Last>Das</Last>
</Name>
<Name type="Common">
<First>Joy</First>
<Middle />
<Last />
</Name>
</Names>



SELECT EmpDetails.query('Contact/Names/Name[@type="Common"]/First')
  AS EmpName,
       EmpDetails.query('Contact/Names/Name[@type="Common"]/Middle')
       AS MiddleName,
       EmpDetails.query('Contact/Names/Name[@type="Common"]/Last')
       AS LastName
FROM   tbl_Employee;

EmpName                 MiddleName      LastName
<First>Joy</First>    <Middle />          <Last />


Step – 4 [ Understanding Text() Method ]
SELECT CONVERT(VARCHAR(50),  EmpDetails.query('Contact/Names/Name[@type="Good"]/First/text()')) 
AS EmpName,
CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Good"]/Middle/text()')) 
AS MiddleName,
CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Good"]/Last/text()')) 
AS LastName
FROM tbl_Employee;


EmpName            MiddleName           LastName
Joydeep Das


SELECT CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/First/text()')) AS EmpName,
CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/Middle/text()')) AS MiddleName,
CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/Last/text()')) AS LastName
FROM tbl_Employee;

EmpName          MiddleName         LastName
Joy


Step – 5 [ Understanding Exist() Method ]
SELECT 

CASE WHEN EmpDetails.exist('Contact/Names/Name[@type="Common"]/First') = 1
THEN
CONVERT(VARCHAR(50),        EmpDetails.query('Contact/Names/Name[@type="Common"]/First/text()'))
ELSE 'Not Found' END AS EmpName,
CASE WHEN 
EmpDetails.exist('Contact/Names/Name[@type="Common"]/Middle') = 1
THEN
CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/Middle/text()'))
ELSE'Not Found ' END AS MiddleName,
CASE WHEN EmpDetails.exist('Contact/Names/Name[@type="Common"]/Last') = 1
THEN
CONVERT(VARCHAR(50), EmpDetails.query('Contact/Names/Name[@type="Common"]/Last/text()'))
ELSE 'Not Found' END AS LastName
FROM tbl_Employee;


In our case we not find any ‘Not Found’ as the Tag Exists.
EmpName    MiddleName      LastName
Joy


Hope you like it.


Posted by: MR. JOYDEEP DAS


Tuesday 15 March 2016

How to make the DE-Normalized DB for OLAP from OLTP

Introduction
In this article we are trying to convert Normalized architecture to DE-Normalized forms that support OLAP. We are trying to say to convert OLTP Data base to OLAP data base. To understand it properly we are taking a simple domain example like Sales Domain.
Hope it will be informative and it put some value in your professional career.


Understanding the Sales Domain in OLTP Environment
The Entity Details:
Entity: Sales Order Header

Attributes

[PK]
Order Id


Order Date


Location Id
[FK].[Location Details]

Customer Id
[FK].[Customer Details]


Entity: Sales Order Detail

Attributes

[PK]
Order Id
[FK].[Sales Order Header]
[PK]
Item Cd
[FK].[Item Details]
[PK]
Srl No


Quantity


Rate


Amount



Entity: Customer Details

Attributes

[PK]
Customer Id


Customer Name


Customer Address


Customer Group Cd
[FK].[Customer Group ]


Entity: Customer Group

Attributes

[PK]
Customer Group Cd


Group Name


Group Description



Entity: Location Details

Attributes

[PK]
Location Id


Location Name


Location Description



Entity: Item Master

Attributes

[PK]
Item Cd


Item Name


Item Description


Item Group Cd
[FK].[Item Group Details]


Entity: Item Group Details

Attributes

[PK]
Item Group Cd


Item Group Name


Item Group Description



ERD Diagram:

How to convert it into De-Normalized OLAP DB


Step-1 [ Fact Table ]
It contains
  1. Facts or Numbers which can be used in aggregate function
  2. The Foreign Key references Key which is needed to connect with Dimension Table.
In our Example we can do like this
Fact-Customer Order Details

Attributes

[PK]
Location Id
[FK].[Location Details]
[PK]
Order Id

[PK]
Item Cd
[FK].[Item Master]
[PK]
Srl No

[PK]
Order Date
[FK].[Dimension-Date]
[PK]
Customer Id
[FK].[Customer Details]

Quantity
[Fact]

Rate
[Fact]

Amount
[Fact]


Step – 2 [ Making Dimensional Table ]
Dimensional table is just like a master table and it related with Fact Table by foreign key relation.
Dimension-Date
It a special dimension contains the Hierarchy of Year à Month à Quarter à Day etc. It basically slices your data according to calendar.

Attributes

[PK]
Order Date


Month


Quarter


Year



Dimension-Customer Details

Attributes

[PK]
Customer Id


Customer Name


Customer Address


Customer Group Cd
[FK].[Customer Group]


Dimension-Location Details

Attributes

[PK]
Location Id


Location Name


Location Description





Dimension-Item Master

Attributes

[PK]
Item Cd


Item Name


Item Description


Item Group Cd
[FK].[Item Group Details]


Step - 3 [ Making Lookup Dimension Table ]
Lookup Dimension is used to support a specific Dimension table. It is not connected with Fact but connected with another dimension table.
Dimension-Customer Group

Attributes

[PK]
Customer Group Cd


Group Name


Group Description



Dimension-Item Group Details

Attributes

[PK]
Item Group Cd


Item Group Name


Item Group Description





Making the STAR Schema

Making Snow flex Schema



Hope you like it.


Posted By: MR. JOYDEEP DAS