Microsoft Sql Server 2005 Interview Questions and Answers

1. What does integration of .NET Framework mean for SQL Server 2005?
This feature enables us to execute C# or VB.NET code in the DBMS to take advantage of the .NET functionality. This feature gives more flexibility in writing complex stored procedures, functions, and triggers that can be written in .net compatible language.
2. What is SSIS?
According to Microsoft SQL Server Integration Services, “(SSIS) is an effective set of tools for both the traditional demands of ETL operations, as well as for the evolving needs of general purpose data integration.” In short, it is the next version of DTS (Data Transformation Services). ETL stands for Extract, Transform and Loading. In short it is a data migration tool that is flexible, fast, and has scalable architecture that enables effective data integration in current business environments.
3. What is MARS?
In previous versions of SQL Server, applications had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 introduces a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection. Multiple Active Result Sets (MARS) is the ability to have more than one pending request under a given SQL Server connection. MARS is a programming model enhancement that allows multiple requests to interleave in the server. We need to note that it is not a parallel execution in the server. However, it may benefit us with some performance benefits if used correctly. By default, this feature is not set in SQL Server 2005.
4. What are the Security Enhancements in SQL Server 2005?
SQL Server 2005 enables administrators to manage permissions at a granular level.
· In the new SQL Server 2005, we can specify a context under which statements in a module can execute.
· SQL Server 2005 clustering supports Kerberos authentication against a SQL Server 2005 virtual server.
· Administrators can specify Microsoft Windows-style policies on standard logins so that a consistent policy is applied across all accounts in the domain.
· SQL Server 2005 supports encryption capabilities within the database itself, fully integrated with a key management infrastructure. By default, client-server communications are encrypted.
5. What is new with the Reporting services in SQL server 2005?
SQL Server 2005 Reporting Services is a key component of SQL Server 2005 that provides customers with an enterprise-capable reporting platform. This comprehensive environment is used for authoring, managing, and delivering reports to the entire organization. SQL Server 2005 reporting services have some major changes when compared with the previous version.
· Changes to the core functionality of the Reporting services in the design of the report, processing, and interactivity
· Better Integration with other components – Enhanced integration with other components within SQL Server 2005 like SSIS, SSAS and SQL Server Management studio
· Report Builder – A new reporting tool that enables business users to create their own reports
6. What is OLAP?
Online Analytical Processing (OLAP) allows us to access aggregated and organized data from business data sources, such as data warehouses, in a multidimensional structure called a cube. The arrangement of data into cubes avoids a limitation of relational databases which are not well suited for near instantaneous analysis of large amounts of data. OLAP cubes can be thought of as extensions to the two-dimensional array of a spreadsheet.
7. What is Data Mining?
According to MSDN Data, mining is “the process of extracting valid, authentic, and actionable information from large databases.” Microsoft data mining tools are different from traditional data mining applications in significant ways. Data Mining is a platform for developing intelligent applications, not a stand-alone application. You can build custom applications that are intelligent because the data mining models are easily accessible to the outside world. Further, the model is extensible so that third parties can add custom algorithms to support particular mining needs.
8. What is new with the Analysis Services (SSAS) in SQL Server 2005?
SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality through a combination of server and client technologies, further reinforced through the use of a specialized development and management environment coupled with a well-defined object model for designing, creating, deploying, and maintaining business intelligence applications. The server component of Analysis Services is implemented as a Microsoft Windows service. Clients communicate with Analysis Services using the public standard XML for Analysis (XMLA), a SOAP-based protocol. Let us see the enhancements of made to SSAS.
· Supports up to 16 instances of Analysis Services Service.
· As discussed above, the Analysis Services service fully implements the XML for Analysis (XMLA) 1.1 specification. All communication with an instance of Analysis Services is handled through XMLA commands in SOAP messages.
· Uses the Proactive caching.
9. What is Information Schema in SQL Sever 2005?
Information Schema is the part of the SQL- 92 standard which exposes the metadata of the database. In SQL server, a set of views are created in each of the databases which exposes the metadata of the database. The information schema is kept in a separate schema – information schema – which exists in all databases, but which is not included in the search path by default. For more information regarding Information schema please read this article.
10. What is Full Text Search? How does it get implemented in SQL server 2005?
Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server database. In contrast to the LIKE predicate which only works on character patterns, full-text queries perform linguistic searches against this data, by operating on words and phrases based on rules of a particular language.
11. What is integration of Microsoft Office System mean?
The integration with Microsoft Office system means the following.
· Table Analysis Tools for Excel: Provides an easy-to-use add-in that leverages SQL Server 2005 Data Mining behind the scenes to perform powerful end user analysis on spreadsheet data.
· Data Mining Client for Excel: Offers a full data mining model development lifecycle directly within Excel 2007.
· Data Mining Templates for Visio: Enables powerful rendering and sharing of mining models as annotatable Visio 2007 drawings.
12. What is the support of Web Services in SQL Server 2005?
With this feature the database engine can be directly exposed as a web service without a middle tier or even an IIS. This will enable the user to directly call a stored procedure by calling a web method. This feature is designed with well-known standards such as SOAP 1.2, WSDL 1.1, and HTTP. With this new feature we can now connect to SQL Server not only with TDS- Tabular data stream (a binary protocol for connecting to SQL Server 2005) but also over SOAP/ HTTP.
13. What is OLTP?
Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. When several users are performing transactions at the same time, OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible.
14. What is Snapshot in SQL Server 2005?
A database snapshot is a read-only, static view of a database, the source database. Each database snapshot is transaction-consistent with the source database as it existed at the time of the snapshot’s creation.
15. What is snapshot isolation in SQL Server 2005?
SQL Server 2005 introduces a new “snapshot” isolation level that is intended to enhance concurrency for online transaction processing (OLTP) applications. In prior versions of SQL Server, concurrency was based solely on locking, which can cause blocking and deadlocking problems for some applications. Snapshot isolation depends on enhancements to row versioning and is intended to improve performance by avoiding reader-writer blocking scenarios.
16. What is Database Partitioning in SQL Server 2005?
SQL Server 2005 provides a new capability for the partitioning of tables across file groups in a database. Partitioning a database improves performance and simplifies maintenance. By splitting a large table into smaller, individual tables, queries accessing only a fraction of the data can run faster because there is less data to scan.
17. What is SQL Server Agent?
SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks called jobs. SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. We generally schedule the backups on the production databases using the SQL server agent. In SQL Server 2005 we have roles created for using SQL Server agents.
· SQLAgentUserRole
· SQLAgentReaderRole
· SQLAgentOperatorRole
SQL Server Agent for SQL Server 2005 provides a more robust security design than earlier versions of SQL Server. This improved design gives system administrators the flexibility they need to manage their Agent service.
18. What is Replication? What is the need to have the replication? What are the enhancements made to SQL Server 2005 related to the replication?
“Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.” In short, replication is all about having multiple copies of the same database. We need replication when we need to distribute data to and from different locations. Generally we have a master copy of data. There will be multiple slaves (Clients) located at various locations which need to be replicated. We use replication for a variety of reasons. Load balancing is sharing the data among a number of servers and distributing the query load. Offline processing is one of the main reasons. In this scenario we need to modify the data on the database that is not connected to the network. The last reason may be to have a back-up to the database in case of failure to the existing database. Let us see the enhancements of SQL server 2005 database related to replication.
· Database Mirroring – Database Mirroring is moving the transactions of database from one SQL Server database to another SQL server database on a different SQL Server.
· Replication Management topology (RMO) – RMO is a new construct in SQL Server 2005. It is a .NET Framework library that provides a set of common language runtime classes for configuring, managing, and scripting replication, and for synchronizing Subscribers.
19. What are Business Logic Handlers?
Business logic handlers are written in managed code and allow us to execute custom business logic during the merge synchronization. We can invoke the business logic handler in case of non-conflicting data changes. Business logic handler can perform one of the following three actions.
· Reject Data
· Accept Data
· Apply Custom Data
20. What are different variants of SQL Server 2005?
There are different variants of SQL Server 2005 commercially available.
· Express – Free and only for one user
· Enterprise – 5 users apart from server
· Workgroup – 10 users apart from server
· Standard – 25 users apart from server
21. What are Various Service packs available for SQL Server 2005?
As of now there are two service packs available for the SQL Server 2005.
· Service Pack 1 – Has major changes or enhancements to SQL Server 2005 in Analysis Services, Data Programmability, SSIS, and reporting services.
· Service Pack 2 – Unlike Service Pack 2, this service pack enables SQL Server 2005 customers to take advantage of the enhancements within Windows Vista and the 2007 Office system.
22. What are the New Data types introduced in SQL Server 2005?
SQL Server 2005 has added some new data types to its existing data types.
XML Data type
· VARCHAR (MAX)
· NVARCHAR (MAX)
· VARBINARY (MAX)
As we can see, the new term MAX has been introduced in SQL Server 2005. This new specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. Varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types.
23. Does SQL Server 2005 support SMTP?
SQL Server 2005 now supports sending E-mail from the database. It is called as database mail and it uses DatabaseMail90.exe. Gone are the days when we were using a third party component for this. Receiving an e-mail was not supported in the previous versions of SQL Server.
24. What is SQL Management Object is SQL Server 2005?
These are collection of objects that are made for programming all aspects of managing Microsoft SQL Server 2005. SMO is a .NET based object model. It comes with SQL Server 2005 as a .Net assembly named Microsoft.SqlServer.Smo.dll. We can use these objects for connecting to a database, calling methods of the database that returns a table, using transactions, transferring data, scheduling administrative tasks, etc. The best part about SMO is that most of it can also be used with SQL server 2000.
25. What is SQL Service Broker in SQL Server 2005?
SQL Service broker is a new technology introduced in SQL Server 2005 for building database-intensive distributed applications. Basically, service broker has been built for developing applications that consist of individual components which are loosely coupled. Service broker supports asynchronous yet reliable messages that are passed between the components. These messages are called conversations.
SSIS ‘N’ SSRS Interview questions....with Answers

26. What is the control flowANS: In SSIS a workflow is called a control-flow. A control-flow links together our modular data-flows as a series of operations in order to achieve a desired result.A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, you use precedence constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow.SQL Server 2005 Integration Services (SSIS) provides three different types of control flow elements: containers that provide structures in packages, tasks that provide functionality, and precedence constraints that connect the executables, containers, and tasks into an ordered control flow.27. what is a data flowANS: A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. Before you can add a data flow to a package, the package control flow must include a Data Flow task. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package.SQL Server 2005 Integration Services (SSIS) provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.28. how do you do error handling in SSISANS: When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values.For example, a data conversion fails because a column contains a string instead of a number, an insertion into a database column fails because the data is a date and the column has a numeric data type, or an expression fails to evaluate because a column value is zero, resulting in a mathematical operation that is not valid.Errors typically fall into one the following categories:-Data conversion errors, which occur if a conversion results in loss of significant digits, the loss of insignificant digits, and the truncation of strings. Data conversion errors also occur if the requested conversion is not supported.-Expression evaluation errors, which occur if expressions that are evaluated at run time perform invalid operations or become syntactically incorrect because of missing or incorrect data values.-Lookup errors, which occur if a lookup operation fails to locate a match in the lookup table.Many data flow components support error outputs, which let you control how the component handles row-level errors in both incoming and outgoing data. You specify how the component behaves when truncation or an error occurs by setting options on individual columns in the input or output.For example, you can specify that the component should fail if customer name data is truncated, but ignore errors on another column that contains less important data.29. How do you do logging in ssis.ANS: SSIS includes logging features that write log entries when run-time events occur and can also write custom messages.Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files.Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.To customize the logging of an event or custom message, Integration Services provides a schema of commonly logged information to include in log entries. The Integration Services log schema defines the information that you can log. You can select elements from the log schema for each log entry.To enable logging in a package1. In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.2. On the SSIS menu, click Logging.3. Select a log provider in the Provider type list, and then click Add.30.what are variables and what is variable scope ?ANS: Variables store values that a SSIS package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions.Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.Scope : A variable is created within the scope of a package or within the scope of a container, task, or event handler in the package. Because the package container is at the top of the container hierarchy, variables with package scope function like global variables and can be used by all containers in the package. Similarly, variables defined within the scope of a container such as a For Loop container can be used by all tasks or containers within the For Loop container.31. True or False - Using a checkpoint file in SSIS is just like issuing the CHECKPOINT command against the relational engine. It commits all of the data to the database.ANS: False. SSIS provides a Checkpoint capability which allows a package to restart at the point of failure.32. True or False: SSIS has a default means to log all records updated, deleted or inserted on a per table basis.ANS: False, but a custom solution can be built to meet these needs.33. What is a breakpoint in SSIS? How is it setup? How do you disable it?ANS: A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an opportunity to review the status of the data, variables and the overall status of the SSIS package.10 unique conditions exist for each breakpoint.Breakpoints are setup in BIDS. In BIDS, navigate to the control flow interface. Right click on the object where you want to set the breakpoint and select the 'Edit Breakpoints...' option.34. How do you eliminate quotes from being uploaded from a flat file to SQL Server? ANS: In the SSIS package on the Flat File Connection Manager Editor, enter quotes into the Text qualifier field then preview the data to ensure the quotes are not included.Additional information: How to strip out double quotes from an import file in SQL Server Integration Services35. Can you explain how to setup a checkpoint file in SSIS?ANS: The following items need to be configured on the properties tab for SSIS package:CheckpointFileName - Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks. Rather than using a hard-coded path as shown above, it's a good idea to use an expression that concatenates a path defined in a package variable and the package name.CheckpointUsage - Determines if/how checkpoints are used. Choose from these options: Never (default), IfExists, or Always. Never indicates that you are not using Checkpoints. IfExists is the typical setting and implements the restart at the point of failure behavior. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task. The Always choice raises an error if the Checkpoint file does not exist.SaveCheckpoints - Choose from these options: True or False (default). You must select True to implement the Checkpoint behavior.36. How do you upgrade an SSIS Package? ANS: Depending on the complexity of the package, one or two techniques are typically used:Recode the package based on the functionality in SQL Server DTSUse the Migrate DTS 2000 Package wizard in BIDS then recode any portion of the package that is not accurate.

SSAS-MDX Interview questions - Time based function-II

Some of the important time related functions are available in this crb sheet. Query refers to [Adventure Works] cube that comes along with SSAS installation.
How do you get Last month in the time dimensionSELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0FROM [Sales Summary]WHERE ([Measures].[Sales Amount]);
I Need an MDX statement to show the first day of the last month in the cubeSELECT OpeningPeriod([Date].[Calendar].[Date], ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ) ON 0FROM [Sales Summary]
I Need an MDX statement to get the last Month loaded into a cubeSELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0FROM [Sales Summary];
How do you write MDX query that uses execution date/time as a parameter?
SELECT {[Measures].[Internet Order Count]} ON 0, {StrToMember("[Date].[Date].[" + Format(now(), "MMMM dd, yyyy") + "]")} ON 1FROM [Direct Sales];
Need MDX Query to get latest months and previous years same months data
SELECT {ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember), ParallelPeriod([Date].[Calendar].[Calendar Year] , 1, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ) } ON 0FROM [Sales Summary];
How to create calculated member for AVG sales over last 3 years based on NOW()?
CREATE MEMBER CurrentCube.Measures.[Avg3Years] AS Avg( {ParallelPeriod( [Date].[Date].[Date Yr], 3, StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")): StrToMember("[Date].[Date].&[" + Format(now(), "yyyyMMdd") + "]")}, [Measures].[Sales Qty]) ;
How can I get Last (Previous) Year to Date (YTD) values?
WITH MEMBER [Measures].[Current YTD] ASSUM(YTD([Date].[Calendar].CurrentMember), [Measures].[Internet Order Quantity])MEMBER [Measures].[Last YTD] AS SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year], 1 , [Date].[Calendar].CurrentMember)), [Measures].[Internet Order Quantity] )SELECT {[Measures].[Current YTD], [Measures].[Last YTD] } ON 0FROM [Adventure Works]WHERE ([Date].[Calendar].[Date].[March 22, 2004])
How do you calculate monthly average of a year?
WITH MEMBER [Measures].[AvgVal] AS Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month]), [Measures].[Internet Order Count] )SELECT {[Measures].[AvgVal]} ON 0FROM [Adventure Works]WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])
Need a MDX query that returns list of months from start of year up to specified month.
SELECT YTD([Date].[Calendar].[Month].&[2003]&[8])ON 0FROM [Sales Summary];
How in the report can I order date dimension members in descending order?
SELECT {[Measures].[Reseller Order Quantity]} ON 0 , ORDER(Tail([Date].[Calendar].[Calendar Year].Members, 3), [Date].[Calendar].CurrentMember.Member_Key, DESC ) ON 1 FROM [Adventure Works]
I Need an MDX statement to get the first month of the last year loaded into a cube
SELECT OpeningPeriod([Date].[Calendar].[Month], ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember) ) ON 0FROM [Sales Summary];
I need an MDX query to show year level data for all years except the last one, and month level data for the last year.
SELECT {NULL:ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember).PrevMember, DESCENDANTS(ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember), [Date].[Calendar].[Month]) } ON 0FROM [Sales Summary];
How do I calculate sales for 12 Month to date in MDX?
WITH MEMBER [Measures].[Last 12 Mth Order Count] AS SUM( ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12): ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]), [Measures].[Order Count])SELECT [Measures].[Last 12 Mth Order Count] ON 0FROM [Adventure Works]
MDX query to get count of months with sales amount > 0 in defined period
WITH Member [Measures].[Months With Above Zero Sales] AS COUNT(FILTER( DESCENDANTS({[Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]}, [Date].[Calendar].[Month]) , [Measures].[Sales Amount] > 0 ) )SELECT {[Measures].[Sales Amount], [Measures].[Months With Above Zero Sales]} ON 0, [Product].[Product Model Lines].[Product Line].Members on 1FROM [Adventure Works]WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004]);
How do you calculate monthly average of a year including empty months?WITH MEMBER [Measures].[AvgVal] AS Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month]), CoalesceEmpty([Measures].[Internet Order Count], 0))SELECT {[Measures].[AvgVal]} ON 0FROM [Adventure Works]WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])

SSAS - MDX Query Interview Questions and Answers-I

Hi All,

Collection of some of the important type of MDX queries which you should be prepared with. The queries refer to sample SSAS database that comes with SSAS installation. Some of the queries used here link back to examples mentioned in Microsoft msn forums.

 

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

 

A: Simply using bottomcount 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 ROWSFROM [Adventure Works]WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;

 

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

 

A: 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 1FROM [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 1FROM [Adventure Works];

 

Q: 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 0FROM [Adventure Works]

 

Q: 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] ASIIF([Date].[Calendar].Level IS [Date].[Calendar].[Calendar Quarter],'Qtr','Not Qtr')SELECT [Measures].[TimeName] ON 0FROM [Sales Summary]WHERE ([Date].[Calendar].[Calendar Quarter].&[2004]&[3])

 

Q: 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] ASCOUNT(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 1FROM [Adventure Works]WHERE ([Date].[Calendar].[Calendar Year].&[2003]: [Date].[Calendar].[Calendar Year].&[2004])

 

Q: 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='';

 

Q: 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 1FROM [Adventure Works]

 

Q: 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.MyMonthCountSELECT {Measures.MyYTD, Measures.MyMonthCount,[Measures].[Internet Sales Amount],Measures.MyYTDAVG} On 0,[Date].[Calendar].[Month] On 1FROM [Adventure Works]WHERE ([Date].[Month of Year].&[7])

 

Q: MDX query to get sales by product line for specific period plus number of months with non empty sales.

 

A: You can use COUNT() function with ExcludeEmpty option. For count function you specify set that is corssjoin of Date members at the month level and measure that you are interested in.

 

 

 

 

 

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

 

Q: How do I group dimension members dynamically in MDX? Source: MSDN SSAS Newsgroup.

 

A: You can create calculated members for dimension and then use them in the query. Example below will create 3 calculated members based on filter condition:

 

WITH MEMBER [Product].[Category].[Case Result 1] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") < "3"))'MEMBER [Product].[Category].[Case Result 2] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") = "3"))'MEMBER [Product].[Category].[Case Result 3] AS ' Aggregate(Filter([Product].[Category].[All].children, [Product].[Category].currentmember.Properties("Key") > "3"))'SELECT NON EMPTY {[Measures].[Order Count] } ON COLUMNS, {[Product].[Category].[Case Result 1],[Product].[Category].[Case Result 2],[Product].[Category].[Case Result 3] } ON ROWSFROM [Adventure Works]

 

Q: How can I compare members from different dimensions that have the same key values?Lets say I have dimensions [Delivery Date] and [Ship Date]. How can I select just records that were Delivered and Shipped the same day?

 

A: You can use FILTER function and compare member keys using Properties function:

 

SELECT {[Measures].[Internet Order Count]} ON 0, FILTER( NonEmptyCrossJoin( [Ship Date].[Date].Children, [Delivery Date].[Date].Children), [Ship Date].[Date].CurrentMember.Properties('Key')= [Delivery Date].[Date].Properties('Key')) ON 1FROM [Adventure Works]

 

Q: How can I get attribute key with MDX

 

A:To do so, use Member_Key function:

 

WITHMEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Member_KeySELECT {Measures.ProductKey} ON axis(0),[Product].[Product Categories].Members on axis(1)FROM [Adventure Works]

 

Q: How do I create a Rolling 12 Months Accumulated Sum (InternetSalesAmtR12Acc) that can show a trend without seasonal variations?

 

A: Here is query example

 

WITH MEMBER [Measures].[InternetSalesAmtYTD] AS SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]), Format_String = "### ### ###"MEMBER [Measures].[InternetSalesAmtPPYTD] AS SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember)),[Measures].[Internet Sales Amount]), Format_String = "### ### ###"MEMBER [Measures].[InternetSalesAmtPY] AS SUM(Ancestor(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),[Date].[Calendar].[Calendar Year]),[Measures].[Internet Sales Amount]),Format_String = "### ### ###"MEMBER [Measures].[InternetSalesAmtR12Acc] AS ([Measures].[InternetSalesAmtYTD]+[Measures].[InternetSalesAmtPY] )- [Measures].[InternetSalesAmtPPYTD]Select {[Measures].[Internet Sales Amount], Measures.[InternetSalesAmtYTD], [Measures].[InternetSalesAmtPPYTD],[Measures].[InternetSalesAmtR12Acc]} On 0,[Date].[Calendar].[Month].Members On 1From [Adventure Works]Where ([Date].[Calendar Year].&[2004]);

 

Q: How to setup calculated measure as default measure for a cube?

 

A: Use ALTER Cube statement on measures dimension. Example:

 

ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER=[Measures].[Profit]

 

Q: How can I write MDX query for the count of customers for whom the earliest sale in the selected time period (2002 and 2003) occurred in a particular Product Category

 

A: Example of such query:

 

WITH SET [FirstSales] ASFILTER(NONEMPTY( [Customer].[Customer Geography].[Customer].MEMBERS* [Date].[Date].[Date].MEMBERS, [Measures].[Internet Sales Amount])AS MYSET,MYSET.CURRENTORDINAL = 1 orNOT(MYSET.CURRENT.ITEM(0) IS MYSET.ITEM(MYSET.CURRENTORDINAL-2).ITEM(0)))MEMBER [Measures].[CustomersW/FirstSales] ASCOUNT(NonEmpty([FirstSales], [Measures].[Internet Sales Amount])),FORMAT_STRING = '#,#'SELECT {[Measures].[Internet Sales Amount],[Measures].[CustomersW/FirstSales]} ON 0,[Product].[Product Categories].[Category] ON 1FROM [Adventure Works]WHERE ({[Date].[Calendar].[Calendar Year].&[2002], [Date].[Calendar].[Calendar Year].&[2003]}, [Customer].[Customer Geography].[City].&[Calgary]&[AB]);

 

Q: How do you write MDX query that returns measure ratio to parent value?

 

A: Below is example on how is ratio calculated for measure [Order Count] using Date dimension. Using parent function, your MDX is independent on level that you are querying data on. In example below, if you query data at year level, ratio will be calculated to level

 

[All]:WITH MEMBER [Measures].[Order Count Ratio To Parent] ASIIF( ([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent) = 0, NULL, [Measures].[Order Count]/([Measures].[Order Count], [Date].[Calendar].CurrentMember.Parent)), FORMAT_STRING = "Percent"SELECT {[Measures].[Order Count], [Measures].[Order Count Ratio To Parent]} ON 0, {DESCENDANTS([Date].[Calendar].[All Periods], 1), [Date].[Calendar].[All Periods]} ON 1FROM [Adventure Works]

SSIS – TOP 10 Best Practices

Guys!!!
Read It.
http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

SSAS - Analysis Services interview questions

Dave Rodabaugh's shared his interview questions.
Have a look.Really Good.

http://www.ssas-info.com/analysis-services-implementations/69-experience/1352-dave-rodabaughs-analysis-services-interview-questions


Original broken links for reference:
My Analysis Services Interview Questions
Part II of My Analysis Services Interview Questions: Cool Business Problems
Part III of My Analysis Services Interview Questions: Architectural Philosophy
Part IV of My Analysis Services Interview Questions: Technical Features
Part V of My Analysis Services Interview Questions: The Most Common MDX Functions

SSIS - XML Destination using Script Component

Hi,In MSBI-SSIS,we have XML Source but not destination.By using Script Component we can achive this.
Here are the clear explanation about XML Destination Using Script Component.
Rav's :-)
http://agilebi.com/cs/blogs/jwelch/archive/2007/06/02/xml-destination-script-component.aspx