SSAS::::MDX Optimization Techniques: Segregating DISTINCT COUNT

Initial Approach via MDX

Let’s initialize the MDX Sample Application, the platform from which we perform many practice exercises within the articles of our series. (We choose it because any organization that has installed MSAS has access to the Sample Application). We will create our initial query by taking the following steps:

1. Start the MDX Sample Application.

We are initially greeted by the Connect dialog, shown in Figure 1.

Figure 1: The Connect Dialog for the MDX Sample Application

Creating Cube with SQL Server 2005 Analysis Services

Definitions::::
Before we dive into building a cube, we need to review some basic definitions. This will help to ensure that at the end you not only have a working cube but an idea of what the cube represents, as well as how to use it. The first two items we will define are Facts and Dimensions.

In the AdventureWorksDW database you will find tables prefixed with the letters ‘Dim’ and ‘Fact’. Ideally, any time you build something to be called a data warehouse you would be building your tables with these two concepts in mind. But what are they? Well, the simplest explanation is to think of them as nouns and verbs, and try not to reflect back to those frustrating hours spent in grammar class. Instead, try to forge ahead with a “Schoolhouse Rock” version of grammar by looking at an example of each.

MS SQL Server interview questions

This one always gets asked. For a while the database interview questions were limited to Oracle and generic database design questions. This is a set of more than a hundred Microsoft SQL Server interview questions. Some questions are open-ended, and some do not have answers.

1. What is normalization? - Well a relational database is basically composed of tables that contain
related data. So the Process of organizing this data into tables is actually referred to as
normalization.

2.What is a Stored Procedure? - Its nothing but a set of T-SQL statements combined to perform a

single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure,

you actually run a set of statements.

3. Can you give an example of Stored Procedure? - sp_helpdb , sp_who2, sp_renamedb are a set of system defined stored procedures. We can also have user defined stored procedures which can be called in similar way.

4. What is a trigger? - Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.

SSIS common Interview Questions

1) What is the control flow
2) what is a data flow
3) how do you do error handling in SSIS
4) how do you do logging in ssis
5) how do you deploy ssis packages.
6) how do you schedule ssis packages to run on the fly
7) how do you run stored procedure and get data
8) give a scenario: Want to insert a text file into database table, but during the upload want to
change a column called as months - January, Feb, etc to a code, - 1,2,3.. .This code can be read from another database table called months. After the conversion of the data , upload the file. If there are any errors, write to error table. Then for all errors, read errors from database, create a file, and mail it to the supervisor.
How would you accomplish this task in SSIS?
9)what are variables and what is variable scope ?

SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

This is very interesting question I have received from a new developer. How can I insert multiple values in table using only one insert? Now this is interesting question. When there are multiple records are to be inserted in the table following is the common way using T-SQL.

USE YourDB
GO
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('First',1);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Second',2);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Third',3);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Fourth',4);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Fifth',5);
GO

The clause INSERT INTO is repeated multiple times. Many times we use copy and paste it to save time. There is another alternative to this, which I use frequently. I use UNION ALL and INSERT INTO … SELECT… clauses. Regarding performance there is not much difference. If there is performance difference it does not matter as I use this for one time insert script. I enjoy writing this way, as it keeps me focus on task, instead of copy paste. I have explained following script to new developer. 

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

The effective result is same.