SSAS

1. What is SQL Server 2005 Analysis Services (SSAS)?

SQL Server 2005 Analysis Services is a service that provides to view business data by unifying and integrating. 

It is the foundation for traditional reporting, OLAP analysis, data mining and Key Performance Indicator scorecards.

2. What are the new features with SQL Server 2005 Analysis Services (SSAS)?

Interoperability with 2007 office system: This feature enables optimized interoperability, delivery of powerful and affordable business insight for information user.

MS SQL Server Data Mining Add-ins for MS Office 2007: With this release, data mining became easier. It has add-ins for data mining which enables enterprises for making predictive analysis which are accessible to wider audience base by empowering harness the sophisticated data mining algorithms to end users.

MS SQL Server 2005 Analysis Services Performance Guide: This feature provides instant access to accurate information. It enables the end users to obtain answers to complex questions, surprisingly, at the speed of thought for OLAP.

The following are the new tools of SQL Server 2005 Analysis Services:

Unified Dimensional Model: The entities, business logic used for implementation, metrics and calculations are defined in this model. This model is utilized for different analytical applications, spreadsheets for reports data verification.

Data Source View: The data source view us used by UML for mapping to a wide array of back end data sources. This model provides the picture of the business irrespective of the data location.

New aggregate functions: The functions like FIRST CHILD, LAST CHIL, FIRST NON-EMPYT AND NON-EMPTY functions are available. These functions can be used in addition to basic aggregate functions, as these are not suitable for every business need.

Querying tools: Dimensions and measures can be dragged and dropped to viewing pane of a cube using query and browsing tool. Writing data mining extensions, MDX queries are the additional features. Syntax errors are automatically alerts with this tool 

3. What are SQL Server Analysis Services cubes?

SQL SAS cube is a data structure for fast analysis of data. 

Using cubes to arrange data is a solution to overcome the limitations in relational databases. RDBMSs are not suited well for display of large amounts of data and instantaneous analysis. Cubes can be thought as an extension for spreadsheet. A company’s financial data by product, time-period, city etc., are additional dimensions for business data. SQL SAS cubes are used to represent this type of data.

4.Explain the purpose of synchronization feature provided in Analysis Services 2005.?

Synchronization feature is utilized in Analysis Services 2005 for copying database from one source server to the destination server.

Users can browse cubes, while the process of synchronization in progress. Once the synchronization process is completed, users are redirected another synchronized database.

5.MDX in SQL Server 2005 Analysis Services brings exciting improvements including query support and expression/calculation language, Explain.

MDX in SQL server 2005 Analysis services offers CASE and SCOPE statements. CASE returns specific values based upon its comparison of an expression to a set of simple expressions. It can perform conditional tests within multiple comparisons. SCOPE is used to define the current subcube. CALCULATE statement is used to populate each cell in the cube with aggregated data. 

6. Can you explain the difference between the INTERSECT and EXCEPT operators?

EXCEPT: Two query expressions are evaluated and the difference between the results will be returned by EXCEPT operator.

The result set will have rows returned from the first set of rows except the rows those are also returned from the second set of rows.

INTERSECT: The result of two queries is evaluated and returns the rows which are common to each.

7. What is the new error handling technique in SQL Server 2005?

SQL Server 2005 introduces a new exception handling paradigm using TRY / CATCH blocks in T-SQL.

The errors are well trapped in the try block and the execution is transferred to the catch block. 

The circumstances like using cursors… the cursor is allocated for opening in the TRY block at the time of occurring an error is thrown to CATCH block to determine whether it is open. 

If it is open, it should be close and deallocate.

SQL Server 2005 still supports the @@ERROR function, but TRY / CATCH is a much better option. 

8. How do you ensure that January, February, March, etc will be in the correct sequence when the user browse the cube? 

Order by key and put month number in the key column.

9.In SSAS, how do you design currency conversion for

a) from many transaction currencies to 1 reporting currency, 

b) from 1 transaction currency to several reporting currencies and 

c) from many transaction currencies to many reporting currencies?

It’s best you can explain about the currency conversion BI wizard.

But if  you explain the “traditional way” it’s quite good too: using calculated measure and many-to-many relationship.

10.A user reported that a Panorama report that she uses every day is very slow today. Normally it opens in 1 or 2 seconds, but today it’s didn’t open at all (she has waited for 4 minutes).

Explain the approach how you are going to handle this case systematically.

As with any support call, we need to find out which report. 

Try to reproduce what the user did in NovaView. 

Find what the MDX is (Tools menu, Direct MDX, ctrl-alt-V), execute in SSMS. 

If this is slow then find out which part of the MDX makes it slow by building the query step-by-step. 

Then consider: a) aggregation, b) block computation, c) convert to base measure (do calculation in the relational/ETL), d) improve the MDX if it’s a calculated measure, e) partitioning.

Most of the time, when a user says that usually it is 2 seconds but today it timed out, it is not the MDX or the cube structure. It’s usually either the server or the connection.

So check that a) the server is up and running and b) from Panorama you can “open” the cube in that server.

11.We have a risk analysis cube. This cube is 500 GB in size, containing 20 billion fact rows. Explain how we: a) process this cube efficiently; b) ensure a good query performance.

Incremental processing (IP). 

If you have huge cubes and the candidate has never done IP before, end the interview. If you have huge cubes in your company (100 GB), your questions should be focused on performance (processing & query). 

The job spec should clearly say: wanted: SSAS performance tuning specialist (not a “normal” AS developer) and prepare to pay 20% premium.

SQL QUERIES (Most releated to Oracle)

1. How to find 2 nd Maximum salary?

  we can find by Using SubQueries, Set Operations, RANK etc

  

Subquery: 

select max(partyincome) from party where partyincome not in(select max(partyincome)  from party);

 

Corelated Subquery:

                      Select a.partyincome from party a where 1=(select  count(distinct(b.partyincome)) from party b where          

                      a.partyincome<b.partyincome)

Note:For n th max salary need to place n-1 in place of 1

2.How to Find Duplicate records in a Table?

Select Partyid from party

groupby partyid

Having count(*)>1

3.  How to Eliminate duplicates rows in a table ?

A:DELETE FROM table_name A    WHERE ROWID > (  SELECT min(ROWID) FROM table_name B  WHERE A.col = B.col);

   (OR)   

DELETE FROM table_name A     WHERE ROWID < (  SELECT max(ROWID) FROM table_name B  WHERE A.col = 
             B.col);

4.Query for deleting alternate even rows FROM a table :

DELETE FROM   EMP WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,2) FROM   EMP);

5.Query for deleting alternate odd rows FROM a table :

 DELETEFROM   EMP WHERE  (ROWID,1) IN (SELECT ROWID, MOD(ROWNUM,2) FROM   EMP); 

6.Top N rows FROM a table : (Displays top 9 salaried people)

SELECT ename, deptno, sal FROM (SELECT * FROM emp ORDER BY sal DESC)  

    WHERE ROWNUM < 10; 

SSAS MDX Query

Hi 

This post covers entire SSAS MDX Query Interview Q & A

1.How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?

  Simply using bottom count will return customers with null sales. 

  You will have to combine it with NONEMPTY or FILTER.

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,
BOTTOMCOUNT(NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer] ), ( [Measures].[Internet Sales Amount] ) ), 10
, ( [Measures].[Internet Sales Amount] ) ) ON ROWS FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;

2. How in MDX query can I get top 3 sales years based on order   quantity?

 

   By default Analysis Services returns members in an order specified during attribute design. 

  Attribute properties that define ordering are "OrderBy" and "OrderByAttribute".

  Lets say we want to see order counts for each year. In Adventure Works MDX query would be:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, [Date].[Calendar].[Calendar Year].Members ON 1 FROM [Adventure Works];

Same query using TopCount:
SELECT {[Measures].[Reseller Order Quantity]} ON 0,
TopCount([Date].[Calendar].[Calendar Year].Members,3, [Measures].[Reseller Order Quantity]) ON 1 FROM [Adventure Works];

3.How do you extract first tuple from the set?

A: Use could usefunction Set. Item(0)

Example:
SELECT {{[Date].[Calendar].[Calendar Year].Members }.Item(0)} ON 0 FROM [Adventure Works]

4: How do you compare dimension level name to specific value?

A: Best way to compare if specific dimension is at certain level is by using 'IS' operator:
Example:

WITH MEMBER [Measures].[TimeName] AS IIF([Date].[Calendar].Level IS [Date].[Calendar].[Calendar Quarter],'Qtr','Not Qtr') SELECT [Measures].[TimeName] ON 0
FROM [Sales Summary] WHERE ([Date].[Calendar].[Calendar Quarter].&[2004]&[3])

5: MDX query to get sales by product line for specific period plus number of months with sales

A: Function Count(, ExcludeEmpty) counts number of non empty set members. So if we crossjoin Month with measure we will get set that we can use to count members.

Query example:

WITH Member [Measures].[Months With Non Zero Sales] AS COUNT(CROSSJOIN([Measures].[Sales Amount] , DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}, [Date].[Calendar].[Month]))
, ExcludeEmpty )
SELECT {[Measures].[Sales Amount], [Measures].[Months With Non Zero Sales]} ON 0
, [Product].[Product Model Lines].[Product Line].Members on 1 FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])

6. How can I setup default dimension member in Calculation script?

A: You can use ALTER CUBE statement. 

Syntax:

ALTER CUBE CurrentCube | YourCubeName UPDATE DIMENSION , DEFAULT_MEMBER='';


7.I would like to create MDX calculated measure that instead of summing children amounts,uses last child amount

A: Normally best way to create this in SSAS 2005 is to create real measure with aggregation function LastChild.
If for some reason you still need to create calculated measure, just use fuction .LastChild on current member of Date dimension, and you will allways get value of last period child.

Example:
We want to see last semester value for year level data. Lets first see what data values are at Calendar Semester level:

SELECT {[Measures].[Internet Order Count]} ON 0 , DESCENDANTS([Date].[Calendar].[All Periods],[Date].[Calendar].[Calendar Semester] ) ON 1 FROM [Adventure Works]

8.How to calculate YTD monthly average and compare it over several years for the same selected month?

A: MDX Query:

WITH MEMBER Measures.MyYTD AS SUM(YTD([Date].[Calendar]),[Measures].[Internet Sales Amount])

MEMBER Measures.MyMonthCount AS SUM(YTD([Date].[Calendar]),(COUNT([Date].[Month of Year])))

MEMBER Measures.MyYTDAVG AS Measures.MyYTD / Measures.MyMonthCount

SELECT {Measures.MyYTD, Measures.MyMonthCount,[Measures].[Internet Sales Amount],Measures.MyYTDAVG} On 0, [Date].[Calendar].[Month] On 1
FROM [Adventure Works] WHERE ([Date].[Month of Year].&[7])

 

SSRS