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.