ABOUT Microsoft Business Intelligence

MSBI Introduction

Database Introduction

https://youtu.be/NW1EeJ-yAio

TO CHECK WHETHER THE TWO TABLES HAVE THE IDENTICAL DATA OR NOT

Create Two tables with Similar Structure and same number of rows but with different values as shown below

Now to Check Whether two tables have the Same data or not am using 'UNION'

OUTPUT:

If all the Counts are Similar then we can say that the records are identical or same, if the Count  is different then two table have different data. According to the O/P Count is not Similar, so two tables are not having the identical data.

Eliminating Duplicate Records From a Table

CREATE A TABLE EMPLOYEE WITH SOME DUPLICATE RECORDS AS SHOWN BELOW

ID 2,3 and 4 are repeated Multiple times. Now let's see the different ways to delete the duplicate Records.

First Method:

Using 'Distinct' Keyword copy the distinct records to temporary place, after that delete the total records from the table. Now copy the distinct data from temporary place to the table. Here am storing the data temporarily in 'Temporary Table' as Shown below.

Second Way:

First we need to add an Identity Column to the Table. Use Rank Function with partition By EMPID and NAME.  And now if the Rank > 1 that means it's a duplicate record, So delete those records. Once the Operation is completed remove identity column from the table.

Third Way:

Using Co-related Sub-Query. In this case also we need Identity Column.

Fourth Way:

If you want to delete all the Rows if the Record is repeated more than 1 time.

Difference Between DB and DWH

MSBI DEMO

Difference Between PatIndex and CharIndex

The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. PATINDEX can use wildcard characters, but CHARINDEX cannot.

These functions take two parameters:

·     The pattern whose position you want. With PATINDEX, the pattern is a literal string that can contain wildcard characters. With CHARINDEX, the pattern is a literal string without wildcard characters.

·         A string-valued expression, generally a column name, searched for the specified pattern.

SELECT CHARINDEX('JA','VIJAY')

O/P :- 3

SELECT PATINDEX('%GMAIL%','VIJAYK0286@GMAIL.COM')

O/P :- 12

SELECT PATINDEX('%GM__L%','VIJAYK0286@GMAIL.COM')

O/P :- 12