SSAS Interview Questions - Part 3 of 3 (On Applications from TK 70-448)

Case Scenario 1: Building an SSAS Solution as a Prototype

The business development department at Adventure Works has asked you to develop a solution to analyze Internet and reseller sales by using Excel 2007. Given the strong support for working with cubes in Excel, you decide to create an SSAS solution that provides the requested sales data, with various dimensions, hierarchies, and attributes available to support a broad range of analysis needs.

1. How can you quickly create a prototype of your solution to gain a better understanding of the data and the end-user requirements? Answer.
To build an initial prototype, you would create a new SSAS solution and add a data source and a data source view (DSV) on top of the database whose schema defines dimension and fact tables. You could then use the Cube Wizard to generate a cube with measure groups for Internet and reseller sales data, along with dimensions for business entities such as time, products, customers, geography, sales reasons, employees, and sales territories. After processing the new SSAS database, you could use the browsers within BIDS or Microsoft Office Excel to review the resulting design, possibly even sharing it with a few of your end users, to better understand the available data and the end users’ analytical requirements.
Hide the Answer.



2. What SSAS features are you likely to take advantage of to improve the usability of the cube from an end-user perspective? Answer.


To improve the usability of the cube, you would first want to ensure that all the measures, measure groups, dimensions, and attributes have business-friendly names. Next, you would likely need to define format strings for all the measures that show currencies and other numbers appropriately. Last, within each dimension, consider adding additional attributes that create useful attribute hierarchies.
Hide the Answer.

Case Scenario 2: Extending SSAS Cubes

As it stands, the Adventure Works cube has data only for Internet sales. However, the business requirements state that the reporting solution must support consolidated reports that show both Internet and reseller sales. Adventure Works is also in the process of developing a Web- based dashboard solution, which needs to display vital business metrics in the form of KPIs.

1. What do you need to change in the dimensional model to accommodate the reseller sales? Answer.
In general, you can accommodate reseller sales by creating a new Reseller cube or by adding a Reseller measure group to the existing cube. If you need to handle large data volumes, you might find that a new cube will give you better performance. On the downside, you will not be able to author consolidated reports that draw data from separate cubes. With smaller cubes, consider adding a new measure group to the existing cube. Incorporate a performance-testing plan early in the design cycle to gauge the performance of the single-cube approach.
Hide the Answer.


2. How could you implement the Adventure Works KPIs? Answer.
As an OLAP server, SSAS is a natural choice for implementing calculated measures and KPIs. OLAP browsers and third-party applications can use the MDX KPI functions to query the cube and retrieve the KPI properties.
Hide the Answer.

Case Scenario 3: Implementing Low-Latency OLAP and Deployment Strategies

You process the Adventure Works cube on a monthly basis as a last step of the ETL process that populates the AdventureWorksDW2008 database. However, some business users have reported that they would like to analyze sales data in real time. When a new Internet order is placed in the sales system, a lightweight ETL process enters the order data into the AdventureWorksDW2008 database. You need to enhance the Adventure Works cube to support low-latency reporting. In addition, based on some preliminary testing, the QA users have reported inadequate query response times when they browse the Adventure Works cube.

1. You need to improve the cube performance. How would you implement a low-latency partition to let business users browse the daily order data? Answer.


You could partition the Internet Sales measure group and set up a low-latency partition. The definition slice of the low-latency partition could filter the sales data for the current month only. In addition, you could enable proactive caching on that partition so that you do not have to process it explicitly.
Hide the Answer.
2. For the same scenario, what can you do to optimize the cube performance? Answer.


Start by optimizing the dimensional design of your cube. Make sure that you have set up correct attribute relationships and have defined useful user hierarchies. Set the Aggregation-Usage property of infrequently used attributes to None. Run the Aggregation Design Wizard to create the preliminary aggregation design. When the cube has been deployed, you can turn on query logging and then use the Usage-Based Optimization Wizard to tune the aggregations that are designed for the cube.
Hide the Answer.
3. As an administrator, you follow an iterative design, test, and deploy cycle to implement a cube. You use BIDS to design the cube and test it locally. Periodically, you need to deploy your changes to a test server for user acceptance testing. To handle high reporting volumes, the production SSAS servers are load-balanced. Instead of processing all production cubes individually, you want to process the cube on a staging server and then deploy it to the production servers. Only new members are added to a large dimension, and you need to minimize the dimension processing time. What deployment options would you use to deploy the cube to testing and production environments? Answer.


If you want to retain the partitions and security settings on the test server, your best deployment option is to use the Deployment Wizard. Rather than processing the production cubes individually, consider processing the cube on the staging server and then using the Synchronize Database Wizard to synchronize each of the production cubes with the staging cube.
Hide the Answer.
4. For the scenario described in question 3, what processing option would you use to add only new members to a dimension? Answer.


If only new members are added to a dimension, you can process the dimension by using the Process Add option.
Hide the Answer.

Case Scenario 4: Administering and Securing SSAS Cubes

You need to set up dimension data security on an SSAS database that contains several cubes. You want all cubes in the database to inherit the dimension data security allowed set for an attribute hierarchy. In addition, you want to know which users have been authenticated successfully by the server for a given time period.

1. How can you set up dimension data security so that all cubes share the allowed set? Answer.
To propagate the allowed set to all cubes in a database, you need to set up dimension data security on the database dimension rather than on the cube dimension. To do so, expand the Dimension drop-down list on the Dimension Data tab, and then select the dimension below the database name.
Hide the Answer.


2. How can you find out which users have been successfully authenticated by the server? Answer.


To find the users who have been successfully authenticated by the server, use SQL Server Profiler to configure a trace based on the Standard template. Inspect the Audit Login/Audit Logout events to find the authenticated users. You can also query the DISCOVER_CONNECTIONS schema rowset to see a list of the current connections.
Hide the Answer.

Case Scenario 5: Working with SSAS Data Mining

The Adventure Works Sales Department wants to know the reasons for its customer churn. The department has requested that you implement one or more mining models to uncover these reasons. You have to prepare the data as well. The Adventure Works Finance Department is satisfied with the Decision Trees model it uses to predict the payment discipline of new customers. However, to better understand the reasons behind the predictions, staff members would like to see the source cases that the model used for training. The Finance Department also gets information about potential customers in the form of Excel 2007 worksheets. They would like to perform advanced checks on this data. For example, they would like to test the data for suspicious rows.

1. How would you prepare the data for finding the churn information? Answer.
Preparing the data for customer churn is not an easy task. The problem is that you typically do not have a simple Churn attribute in your source data. You should find out from the Sales Department how it wants to define churn. For example, you could create a new attribute that shows whether a customer has purchased anything from Adventure Works in the past six months and then use this as a predictable attribute. Do not forget to randomly split the existing data into training sets and test sets.
Hide the Answer.


2. Which algorithms would you use? Answer.


You can use Decision Trees, Naïve Bayes, Neural Network, Clustering, Logistic Regression, and Linear Regression algorithms for predictions.
Hide the Answer.
3. How many models would you create? Answer.


In a real-life project, you should create multiple mining models by using different algorithms and different parameters of the algorithms. Alternatively, you could try to define the attribute that measures the churn differently and apply the models on the new predictable variable. Last, you could use different input attributes for different models. You could have a single mining structure and ignore some attributes in some models. You would then deploy the model with the best performance into the production environment.
Hide the Answer.
4. How can you allow the Finance Department to see the source cases that the data mining model used for training? Answer.


To allow the Finance Department to see the source cases that the data mining model used for training, you have to enable drillthrough on your mining model. To do this, first give the Drill Through permission to the SSAS database role that includes the Finance Department users. In addition, you should give this role the Read Access permission and the Read Definition perission for the data source. Last, the Finance Department users must have permissions that allow access to the source data. For example, if the source is in a SQL Server table, the users need Select permission on that table.
Hide the Answer.
5. How can you use SSAS data mining in Excel 2007 to find the suspicious rows? Answer.


You can download the Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007 and install them on the computers in the Finance Department. Then, with the help of an ad hoc Clustering model, the Finance Department users will be able to use Excel 2007 Add-ins to analyze the worksheet data. With the Clustering model, you can easily find outliers, which are cases that do not fit well in any cluster. Outliers typically include suspicious data. Of course, you must allow session mining models on your SSAS.
Hide the Answer.