SIMPLE SSAS CUBE and DIMENSIONS

SIMPLE SSAS CUBE with default  DIMENSIONS created

Step 1: Open MSBI-->GO TO file-->select NEW PROJECT-->select ANALYSIS SERVICE as shown in figure-->NAME it as your choice (my self it is MySimpleCube)-->then click OK

then the wizard will be like this

Step 2:
Right click on data Sources -->select new (see below image)

Step 3: Drop down at provider select micro soft OLEDB Provider for sql server --> then OK(See below image)

Next select Server name -->Select Data base name-->Test connection --> click OK

Step 4: Select use the service account--> click NEXT

Step 5: Then click FINISH

 

Step 6: Right click on data source view --it shows the data source view wizard--> Click Next

Step 7: Select Use the service account--> click next

 Step 8: Select Any one of the table then click on Add related tables--> then click next

Step 9: Then Click on Finish

Step 10: Now the wizard will appear like below

 

Step 11:Now right click on cube--> select New Cube-->click next -->select use existing tables-->click Next

 Step 12: Directly click on Suggest (Default it will select the tables) --> click Next

Step 13: click Next

Step 14: Again Click next

Step 15: Before going to finish observe there is no underlying cubes or dimensions respectively-->next click finish

Step 16: now you will see the cube and dimensions generated automatically

Step 17: now Click on Process(Rounded symbol with red mark)--> then click Yes

Step 18:Click on Run

Step 19: After finishing process progress click on close

Step 20: Now click on Browser(Marked with red color)

 

Step 21: Drag and drop the measures at middle,Except that remaining you can drag and drop on Row filters and column filters and filter fields

 Finally solution will be like this (Observe above image)

CTE & RECURSIVE CTE(Common Table Expression)

Common Table Expression

Simple CTE

Print 1 to N numbers

;
WITH CTE AS
(
    SELECT NoM=1
    UNION ALL
    SELECT NoM+1 FROM CTE WHERE NOM+1<=10
) SELECT * FROM CTE

Ans:

Find the Employees under manager 

;WITH X AS
(
    SELECT * FROM EMP WHERE mgr=7788
    UNION ALL
    SELECT e.* FROM x  INNER JOIN emp e ON e.empno=x.mgr
)
SELECT * FROM X

Ans:

Find Cumulative Salary of employees 

select SAL,(select SUM(sal) from emp e where e.empno<=a.empno)as CUM_SAL from emp a

FUNCTION TO concatenate all COLUMNS OF a TABLE

FUNCTION TO concatenate all COLUMNS OF a TABLE 

USE AdventureWorksDW2008
GO

CREATE FUNCTION fn_columns( @tblname varchar(50))
returns varchar(MAX)
as
BEGIN
DECLARE @name VARCHAR(500)=''
SELECT @name=name+', '+@name FROM SYS.columns WHERE OBJECT_ID=OBJECT_ID(@tblname)
RETURN left(@NAME,len(@NAME)-1)
END

 SELECT NAME,dbo.fn_columns(name)as COLUMNS from sys.tables

Script Task For Pickup txt file From Folder

Step 1:
Create Two variables as shown in figure
One for Fullpath STRING
Another for Validate INT

Step 2:
In script task Editor Select valiadate variable as a Readonly variable and other for Readand write variable

"D:\\ExcelDestination\\ExcelWorkBook_"+ (DT_STR, 4, 1252) DATEPART("yy" , GETDATE())  +
RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)  + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)+".xlsx"

Step 3:
Click on edit script then write like as shown in last image
Save then click OK

using System.IO;

public void Main()
{
// TODO: Add your code here
            String str = Dts.Variables["User::FullPath"].Value.ToString();
            if (File.Exists(str))
            {
                Dts.Variables["User::Validate"].Value = 1;
            }
            MessageBox.Show(str);
            MessageBox.Show(Dts.Variables["User::FullPath"].Value.ToString());


Dts.TaskResult = (int)ScriptResults.Success;
}

Step 4:
Go to properties of Fullpath by selecting that variable and click F4---->Expression--->Write expression shown in image Expression Builder--->Evaluate expression

Step 5:
Click on presidence constraint--> Evaluation and Constraint-->@validate ==1
in other line keep @validate==0

Create FUNCTION TO find TAX in sqlserver

CREATE FUNCTION F_Get_IncommTaxx(@sal money) -- 2500000 RETURNS money -- SELECT DBO.F_Get_IncommTaxx(300000) AS BEGIN       DECLARE @R_sal MONEY=@sal-150000 -- 350000       DECLARE @tax MONEY =0        IF(@R_sal>0 AND @R_sal<=100000)        BEGIN             SET @tax=(@R_sal *10)/100             SET @R_sal=@R_sal-100000        END        ELSE IF(@R_SAL>0)        BEGIN             SET @tax=(100000 *10)/100 -- 10000             set @R_sal=@R_sal-100000  -- 250000        END        -- 20% slab        if(@R_sal>0 AND @R_sal<=200000)        BEGIN             SET @tax=@tax+((@R_sal *20)/100)             SET @R_sal=@R_sal-200000        END        ELSE IF(@R_SAL>0)        BEGIN             SET @tax=@tax+((200000 *20)/100) -- 50000             SET @R_sal=@R_sal-200000          END                -- 30% slab        IF(@R_sal>0 )        BEGIN             SET @tax=@tax+((@R_sal *30)/100) -- 65500        END                      RETURN @tax; END

SET NUMBER of RECORDS per PAGE in SQLSERVER

PAGING in SQL SERVER

USE AdventureWorksDW
GO
CREATE PROC usp_paging(@NOOFRECORDS INT,@PAGENO INT)
AS
--EXEC usp_paging 10,2
BEGIN

DECLARE @STING INT=((@PAGENO-1)*@NOOFRECORDS)+1
DECLARE @ENDING INT=@NOOFRECORDS*@PAGENO
PRINT @STING
PRINT @ENDING
;WITH X AS(
SELECT *,DENSE_RANK() OVER (ORDER BY TIMEKEY)AS DENSERANK
,RANK() OVER (ORDER BY TIMEKEY) AS RANK,
ROW_NUMBER() OVER(PARTITION BY TIMEKEY ORDER BY TIMEKEY)AS ROWNUMBER  FROM FactFinance)
SELECT * FROM X WHERE ROWNUMBER=1 AND DENSERANK BETWEEN @STING AND @ENDING
ORDER BY TimeKey
END

Results:

IGNORE Duplicates while inserting NEW Records

SQL Constraint IGNORE_DUP_KEY on Update

The below example gives you a better identification on IGNORE_DUP_KEY.

      Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

IMPORTANT Note:

         IGNORE_DUP_KEY cannot be set to ON  for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.

In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.

ON
A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail.

OFF
An error message will occur when duplicate key values are inserted into a unique index. The entire INSERT operation will be rolled back

CREATE TABLE #Big (pk int NOT NULL CONSTRAINT PK_Big PRIMARY KEY)
GO
-- Start with values 1 & 5 in the table

INSERT #Big (pk) VALUES (1), (5);

-- Our batch transaction
BEGIN TRANSACTION;
    -- Insert a batch containing some pre-existing rows
    INSERT #Big (pk) VALUES (1), (2), (3), (4), (5);

    -- Show the contents of the table after the insert statement
    SELECT pk FROM #Big;

    -- Show the transaction count
    SELECT tran_count = @@TRANCOUNT;

 -- Rollback
ROLLBACK TRANSACTION;

-- Final table contents
SELECT pk FROM #Big;
GO
-- Tidy up
DROP TABLE #Big;

Result:

CREATE TABLE #Big (pk int NOT NULL CONSTRAINT PK_Big PRIMARY KEY) WITH (IGNORE_DUP_KEY = ON));
GO
-- Start with values 1 & 5 in the table


INSERT #Big (pk) VALUES (1), (5);

-- Our batch transaction
BEGIN TRANSACTION;
    -- Insert a batch containing some pre-existing rows
    INSERT #Big (pk) VALUES (1), (2), (3), (4), (5);

    -- Show the contents of the table after the insert statement
    SELECT pk FROM #Big;

    -- Show the transaction count
    SELECT tran_count = @@TRANCOUNT;

 -- Rollback
ROLLBACK TRANSACTION;

-- Final table contents
SELECT pk FROM #Big;
GO
-- Tidy up
DROP TABLE #Big;

Result:

If we want to add IGNORE_DUP_KEY on primary key existing, first drop the CONSTRAINT then recreate along with WITH(IGNORE_DUP_KEY=ON)
Drop and alter of a table are the examples shown below

CREATE TABLE #Big (pk int not null CONSTRAINT pk_key primary key WITH(IGNORE_DUP_KEY=ON))
INSERT INTO #Big VALUES(1),(2),(3)
INSERT INTO #Big VALUES(2),(5),(6)
ALTER TABLE #BIG DROP CONSTRAINT PK_KEY
alter table #big add constraint pk_key primary key(PK) WITH(IGNORE_DUP_KEY=ON)