SQL SERVER -- DDL Commands -- Part-3

DDL Commands:-
                                               DDL Stands for Data Definition language and these commands are used to CREATE, ALTER and DELETE database Objects.

CREATE – This command is used to create database objects

                   Like Database and Table.

-- How to Create New DataBase

CREATE DATABASE SQLSERVER

GO

-- How to go inside to the Database

USE SQLSERVER

GO

-- CREATE

-- How to Create New Table

CREATE TABLE EMP

(

       EMPID INT

      ,EMPNAME VARCHAR(50)

      ,SAL MONEY

      ,DOJ DATE

      ,LOC VARCHAR(50)

      ,GENDER CHAR(6)

      ,MagrID INT

      ,DEPTNO INT

)

ALTER – This command is used to Alters database objects
              Like adding new column to the table        

-- ALTER

-- How to Add New Column to the Table

ALTER TABLE EMP

ADD COUNTRY CHAR(100)

-- How to Drop the Column the Table

ALTER TABLE EMP

DROP COLUMN COUNTRY

TRUNCATE – This command is used to delete all records from a table and resets table identity to initial value.

DROP – This command is used to delete objects of the database

-- How to Drop the Table from the Database

DROP TABLE EMP

-- How to Drop the Column the Table

ALTER TABLE EMP

DROP COLUMN COUNTRY

SQL SERVER -- SQL Commands -- Part-2

Types of SQL Commands?
Whenever user interacts with a database, the software translates your commands (whether they are mouse clicks or form entries) into SQL statement that the database knows how to interpret. SQL has five major components that means five most commands.
There are 5 types of commands:

1.     DDL(Data Definition Language)

§  CREATE

§  ALTER

§  DROP

2.     DML(Data Manipulation Language)

§  INSERT

§  UPDATE

§  DELETE

§  MERGE

3.     DRL (Data Retrieval Language)

§  SELECT

4.     TCL  (Transaction Control  Language)

§  COMMIT

§  ROLLBACK

5.     DCL (Data Control Language)

§  GRANT

§  REVOKE

SQL SERVER -- Introduction to SQL -- Part-1

What is SQL Server?

1.    SQL Stands for Structured Query Language.

2.     It is the secret of instructions used to interact with a relational database.

3.     In fact SQL is the only language that most databases actually understand.

4.     SQL is a computer language for storing, manipulating and retrieving the data from relational database.

5.     Product of Microsoft

6.     Enables user to write queries and other SQL statements and execute them

Why SQL?

1.     Allows users to access data in relational database management systems.

2.     Describe the data.

3.     Define the data in database and manipulate that data.

4.     Allows embedding within other languages using SQL modules, libraries &  pre-compilers.

5.     Create and Drop databases and tables.

6.     Create View, Stored Procedure, and Functions in a database.

MSBI -- SSAS -- Part-13

Partitions:-

In general we Use cube partitions to improve Analysis Services performance and it also reduce downtime.

Ø A partition is a container for a portion of the measure group data. 

Ø Partitions are used by Microsoft SQL Server Analysis Services to manage and store data and aggregations for a measure group in a cube.

Ø Every measure group has at least one partition; this partition is created when the measure group is defined.

Ø Partitions are a powerful and flexible means of managing cubes, especially large cubes.

For example:-

----------------

 A cube that contains sales information can contain a partition for the data of each past year and also partitions for each quarter of the current year. Only the current quarter partition needs to be processed when current information is added to the cube; processing a smaller amount of data will improve processing performance by decreasing processing time.

NOTE:- In the above scenario, instead of storing whole measure group data into a single physical block, we can create the partitions for each year.

STEPS TO CREATE THE PARTITIONS

For creating partitions, Launch Microsoft Visual Studio and select SSAS project.

1.     In SQL Server Data Tools, in Solution Explorer, double-click the cube to open it in Cube Designer, and then click the Partitions tab.

2.     Expand the measure group for which are adding partitions. By default, each measure group has one partition, bound to a fact table in the DSV.

3.     In the Source column, click the browse (. .) button to open the Partition Source dialogue box. 

4.     In Binding Type, select Query Binding. The SQL query that selects the data appears automatically.

5.     Then select the data source (say AdventureWorksDW2012).,Type in the Select Query with the where condition ( Say If we need only 2006 year data, accordingly we need to provide the SQL Command).

6.     Click Check to check for syntax errors, and then click OK.

 CREATION OF NEW PARTITION:-

1.     Click New Partition to start the Partition Wizard 

Then click Next button. You would be able to see Partition Wizard Window.

2.     In Specify Source Information, Choose the Available tables and click next.

3.     Then Restrict Rows pop up appears. We need to choose “Specify a query to restrict rows “.

Once we provide the Query, need to click on check button .Then you should be able to see the below pop up.

Click Next, then we get the below window.No need to change settings here above, just click next.

4.     Then we get “Completing the wizard”, where it allows us to choose the Aggregation options and also we can give the partition a descriptive name. Select the option “Design Aggregations Later” and click Finish.

5.     Deploy the solution or process the partition to load the data.

6.     Browse the cube to verify the correct data is reflected.

Thank you Chaitanya for clean documentation.

MSBI -- SSAS -- Part-12

Aggregations:

                               In SQL Server Analysis Services (SSAS), each measure group by default has at least one partition. When a cube is created, this partition would not have any aggregation schemes defined, so these partitions are not optimized for performance. As you know, aggregations are pre-calculated sets of data which improve query response time and calculations during query evaluation.

Follow the below steps to create Perspectives:-

Step-1:-

Step-2:-

Step-3:-

Step-4:-

Step-5:-

Step-6:-

Step-7:-

Step-8:-

Step-9:-

Step-10:-

MSBI -- SSAS -- Part-11

Partitions:

                      By Default all the Measure Group data will be stored in a Single Physical Block.

Whenever user queries the data for a particular year, it needs to search all the years’ data and may get the data at particular year which may take time.

If we create partitions, the cube data will be split into multiple physical blocks.

So, whenever user queries the data for particular year, it will directly goes to respective physical block & get the data for user query.

Advantages:-

1.     It will decrease the Cube Processing time.

2.     And it will increase the cube performance.

Follow the below steps to create Perspectives:-

Step-1:

Step-2:

Step-3:

Step-4:

Step-5:

Step-6:

Step-7:

Step-8:

Step-9:

Step-10:

Step-11:

Step-12:

Step-13:

Step-14:

Step-15:

MSBI -- SSAS -- Part-10

Translations:

                       Translations are used to provide multi language support for SSAS Cube Objects.

                       In simple terms, Translations allow us to display the Labels and Captions of various SSAS objects in different languages. In SSAS both metadata and data can be translated. The objects which support Translations in SSAS include databases, cubes, dimensions, attributes, hierarchies, measure groups, calculated members, KPIs, and various other objects.

                        In today's world, business are growing and expanding to a very large extent and tend to have presence internationally. In such situations, it would be essential that, SSAS objects support localization and people from different geographical locations be able to see the information in their local language.

Follow the below steps to create Perspectives:-

Step-1:

Step-2:

Step-3:

Step-4:

Step-5:

Step-6:

Step-7:

Step-8:

Step-9: