Featured post

Basic Concepts Used within MDX Query:-

Here you learn what I couldn‚Äôt learn in a month ūüôā

Multi-Dimensional Expression (MDX)

You can use MDX queries to get at data stored in a SQL Server Analysis Server cube by bringing back facts based on dimensions An MDX expression returns a multi-dimensional result set (dataset) that consists of axis data and cell data.

Cube:-

OLAP Cube is the basic unit of storage for Multidimensional data, on which we can do analysis on stored data and study the various patterns

Dimensions:-

Dimensions, in general, we can say are the Master entities with related member attributes using which we can study data stored in OLAP Cube Quickly and effectively, The primary functions of dimensions are to provide Filtering, Grouping, and Labeling on your data. Dimension tables contain textual descriptions about the subjects of the business.

Measure & Measure Groups:-

Metrics value stored in your Fact Tables is called Measure. Measures are used to analyze the performance of the Business. Measure usually contains numeric data, which can be aggregated against usage of associated dimensions. Measure Group holds the collection of related Measures.

1

Here Basic Terms you should know while working on Multi Dimensional Cube

Introduction to Level, Member, Hierarchy

Level

Generally, Attributes under Dimension are considered as levels, they are also called as Attribute

 Hierarchy.

Let’s take an example of Date Dimension in this we have various levels of Quarter of the Year, Semester of the Year, Week of the Year, Calendar Year, etc.

Members

A key component of the MDX query is member. Each Level contains one or more members.

e.g. Calendar Quarter of Year contains various members like CY Q1, CY Q2, CY Q3, CYQ4.

2

Now we are ready to start playing with MDX Query in our Query Editor Window.

‚ÄúI think this is the beginning of a beautiful friendship between you and MDX.‚ÄĚ

3

Introduction to Axis in MDX Query

MDX queries can have 0, 1, 2 or up to 129 query axes in the SELECT statement. Each axis behaves in exactly the same way, unlike SQL where there are significant differences between how the rows and the columns of a query behave.

Refer to the following table for Axis Numbers reserved and Alias given to them:
Axis Number Alias
0 Columns
1 Rows

Using SQL Server Management Studio (SSMS), we can only browse values on two axis, Columns (Axis 0) and Rows (Axis 1).

 

1. Start with Simple MDX Query

Syntax:  Select From [Your Cube Name] ;.

Which will give you aggregated result as shown in result pane.
4

2. Dropping Dimensions on Axis

Syntax  Select Dimension.Member on Column From [OLAPCubeName ]

Select [Customer].[Customer].[Customer] on columns From [Adventure Works]; 5

3. Using Both the Axis (Rows & Columns)

You can select Dimension or Measure on any Axis.

 Syntax Select [Measure] on Columns,

[Dimension].[Members] on Rows From [Cube Name] ;

Select [Measures].[Internet Sales Amount] on Columns,[Customer].[Customer].[Customer] on RowsFrom [Adventure Works];

6

4. Introduction to .members, and .children in MDX Query

.Members

If you will use this with hierarchy level, then it will retrieve all the values below it and also bring aggregation of that in the form of [ALL].

 Syntax Select [Dimension].[Hierarchy].members on Columns from CubeName

Select [Measures].[Internet Average Sales Amount] on Columns,[Product].[Category].members on RowsFrom [Adventure Works];

7

.Children

When we want to retrieve all members values under particular level of a dimension at that time we use .children ,This will exclude aggregation values [ALL] in your result set.

Syntax  Select [Measures].[Internet Average Sales Amount] on Columns,[Product].[Category].children on RowsFrom [Adventure Works];

8

5. Introduction to Tuple and Set

Tuple:

When we need to place more than one members of a dimension or hierarchy of that dimension on a axis at that time tuple comes into the picture, tuple is enclosed within curly bracket { }, for single tuple bracket is optional.

select {[Date].[CY 2005], [Date].[CY 2006] , [Date].[CY 2007]} on rows,[Measures].[Internet Sales Amount] on columns from[Adventure Works];

9

Set:

Combination of tuple or tuples will give you set , When You want to include range at that time you can use : instead of separating tuple members by comma if they are belonging to same dimension member.

select {[Date].[CY 2008] : [Date].[CY 2005]}  on rows,[Measures].[Internet Sales Amount] on columns from[Adventure Works];

10

6. Using CROSS JOIN

Cross Join Function returns cross product of one or more sets.

Whenever we need to combine more than one member from same or different dimension at that time we can use cross join. * sign can be use to implement cross join between dimension members.

select {[Product].[Category].children * [Product].[Subcategory].children} on rows,[Measures].[Internet Sales Amount] on columns from[Adventure Works];

11

7. Using NonEmpty

NonEmpty function evaluated first so it will remove rows if there was no data in first measure.

8. Apply Slicing using Where Clause

Select [Measures].[Internet Sales Amount] on columns,[Product].[Product].[Product].members on rows from [Adventure Works]where [Date].[Calendar Year].[CY 2007];

Looks like now you know what is mdx at least; Let’s put a smile on face.

SSRS Report Server to be Accessed from local Machine

Prerequisites:-

  • The user should have access to report server.
  • The user should have access to the Database server.
  • Last but not the least:- Good internet connection ūüėõ

Then follow below steps to create a rule on windows firewall.

In Start, you can type Windows Firewall; which opens the Control Panel -> System and Security -> Windows Firewall. Click Advanced Settings:

WindowsFirewall.png

. In Advanced Settings in the left-hand pane, click Inbound Rules. In the Actions pane on the right, click New Rule:

In the wizard, select Port and click next:

Check TCP, check Specific local ports, enter 80, and click next:

Click allow the connection and click next:

Check your networks. In my environment, I checked all options. Click Next:

Enter a rule name and click Finish. My rule name is Report Server Name (Server-177):

Enjoy:-

Hope; this blog will help you, let me know in case of any disconnect or how better I can help you.

Join conditons generally asked during interviews

So everybody know joins in SQL but there are some game of null and repeated values which people generally miss so here i am demonstrating all those condition.¬†we’ll go step by step,lets create tables then play with joins. Before running any query think twice what will be the output.

Create table JoinA (col int),Create table JoinB (col int)

Insert into JoinA values(1)
Insert into JoinA values(1)
Insert into JoinA values(1)
Insert into JoinA values(2)
Insert into JoinA values(2)
Insert into JoinA values(null)

Select * from JoinA

Insert into JoinB values (1)
Insert into JoinB values (1)
Insert into JoinB values (2)
Insert into JoinB values (2)
Insert into JoinB values (2)
Insert into JoinB values (null)
Insert into JoinB values (null)

Select * from JoinB

–Inner Join

Select * From JoinA Join JoinB On JoinA.col=JoinB.col 

–Left Join

Select * From JoinA Left Join JoinB On JoinA.col=JoinB.col

–Right Join

Select * From JoinB Right Join JoinA On JoinB.col=JoinA.col 

–Outer Join

Select * From JoinA Full Join JoinB On JoinA.col=JoinB.col

if you people want to add into this please welcome.

Sub-query

  • A sub-query is a query that is nested is nested inside a SELECT, INSERT, UPDATE or DELETE statement or inside another sub-query.

Sub-query Guidelines:-

There are some guidelines to consider when using subqueries :
– A sub-query must be enclosed in parentheses.
– A sub-query must be placed on the right side of the comparison operator.
– Sub-queries cannot manipulate their results internally, therefore ORDER BY clause cannot be added in to a sub-query. You can use a ORDER BY clause in the main SELECT statement (outer query) which will be last clause.
– Use single-row operators with single-row sub-queries.
– If a sub-query (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.

Types of Sub-query:-

A single row sub-query returns zero or one row to the outer SQL statement. You can place a sub-query in a WHERE clause, a HAVING clause, or a FROM clause of a SELECT statement.

  1. SELECT agent_name, agent_code, phone_no
  2. FROM agents
  3. WHERE agent_code =
  4. (SELECT agent_code
  5. FROM agents
  6. WHERE¬†agent_name¬†=¬†‘Alex’);

Multiple row sub-query returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a sub-query that returns multiple rows.

  1. SELECT ord_num,ord_amount,ord_date,
  2. cust_code, agent_code
  3. FROM orders
  4. WHERE agent_code IN(
  5. SELECT agent_code FROM agents

Co-related Sub-query is a inner sub-query which is referenced by main outer query such that inner query is considered as being executed repeatedly.

SELECT e.EmployeeID
FROM HumanResources.Employee e
WHERE e.ContactID IN
(
SELECT c.ContactID
FROM Person.Contact c
WHERE MONTH(c.ModifiedDate) = MONTH(e.ModifiedDate)
)

Adding Subqueries to the SELECT Clause

You can add a subquery to a SELECT clause as a column expression in the SELECT list. The subquery must return a scalar (single) value for each row returned by the outer query. For example, in the following SELECT statement, I use a subquery to define the TotalQuantity column:

SELECT  SalesOrderNumber,  SubTotal,  OrderDate,  (

SELECT SUM(OrderQty)    FROM Sales.SalesOrderDetail    WHERE SalesOrderID = 43659  ) AS TotalQuantity FROM  Sales.SalesOrderHeader WHERE   SalesOrderID = 43659;

Adding Subqueries to the FROM Clause

The subquery examples in the previous section each return a single value, which they must do in order to be used in the SELECT clause. However, not all subquery results are limited in this way. A subquery can also be used in the FROM clause to return multiple rows and columns. The results returned by such a subquery are referred to as aderived table. A derived table is useful when you want to work with a subset of data from one or more tables without needing to create a view or temporary table. For instance, in the following example, I create a subquery that retrieves product subcategory information from the ProductSubcategory table, but only for those products that include the word ‚Äúbike‚ÄĚ in their name:

SELECT   p.ProductID,  p.Name AS ProductName,  p.ProductSubcategoryID AS SubcategoryID,

ps.Name¬†AS¬†SubcategoryName¬†FROM¬†¬†Production.Product p¬†INNER¬†JOIN¬†¬†(¬†¬†¬†¬†SELECT¬†ProductSubcategoryID,¬†Name¬†¬†FROM¬†Production.ProductSubcategory¬†¬†¬†¬†WHERE¬†Name¬†LIKE¬†‘%bikes%’¬†¬†)¬†AS¬†ps¬†ON¬†p.ProductSubcategoryID¬†=¬†ps.ProductSubcategoryID;

Adding Subqueries to the WHERE Clause

SELECT¬†¬†BusinessEntityID,¬†¬†FirstName,¬†¬†LastName¬†FROM¬†¬†Person.Person¬†WHERE¬†¬†¬†BusinessEntityD¬†=¬†¬†(SELECT¬†BusinessEntityID¬†¬†FROM¬†HumanResources.Employee¬† WHERE¬†NationalIDNumber¬†=¬†‘895209680’¬†¬†);

SQL Data Type overview for beginers

  1. CHAR (n), TEXT (n), VARCHAR (n).

Alphanumeric data either fixed at n symbols or up to n symbols.  It’s not possible to do arithmetic on this data.

  1. REAL, FLOAT, NUMBER, NUMERIC and DECIMAL.

These are numbers with decimal places.

  1. INTEGER, LONG, INT, SMALLINT.

These are all whole numbers.  They vary in the how big a number they can hold.

  1. MONEY, CURRENCY.

These are numeric types with decimal places for holding monetary values.

  1. BINARY, LONGBINARY, GENERAL, IMAGE, OLEOBJECT.

These can hold complete files, such as pictures or media.  They are not of a fixed in size, and the upper limit on their size is large.

  1. DATE, TIME, DATETIME.

These can hold date and time values.  These are hybrid types.  Although you think of them as text type fields, it is possible to do arithmetic and numeric comparisons on them.

Difference between Persisted and Non Persisted Computed Columns in SQL Server.

Computed Column are derived column based on other existing column in the same table.

There are two types of Computed column namely Persisted and Non-Persisted.

1. Non-Persisted column are calculated on the fly(ie when the select Query executed) whereas Persisted column
are calculated as soon data is stored in the table.

2. Non-Persisted columns do not consume any space as they are calculated only when select the column.
Persisted column consume space.
3. When you SELECT data from these columns Non-persisted columns are slower than Persisted columns.

Example:-

CREATE TABLE AA(COL1 INT, COL2 AS COL1*0.20)
INSERT INTO AA (COL1)
SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY S1.NAME) FROM SYS.OBJECTS AS S1 CROSS JOIN SYS.OBJECTS AS S2

CREATE TABLE B(COL1 INT, COL2 AS COL1*0.20 PERSISTED)
INSERT INTO B (COL1)
SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY S1.NAME) FROM SYS.OBJECTS AS S1 CROSS JOIN SYS.OBJECTS AS S2

RUN THE FOLLOWING CODE TO UNDERSTAND THAT TABLE WITH PERSISTED COMPUTED COLUMNS CONSUMES MORE SPACE WHEN COMPARED TO A TABLE WITH NON-PERSISTED COMPUTED COLUMNS. REFER THE COLUMN NAMED DATA

EXEC SP_SPACEUSED AA
EXEC SP_SPACEUSED B