SSAS Expert Quiz

 



1) Multi-lingual feature support for a dimension is possible using tab

1. Perspective
2. Translation
3. Both A and B

Show Answer

 
 
2) Write-Back data is stored in a

1. File
2. Database
3. Both A and B

Show Answer

 
 
3) A cube can have multiple partitioned tables from multiple data sources is

1. TRUE
2. FALSE

Show Answer

 
 
4) How to Deploy a Cube?

1. Create XMLA Schema and Deploy
2. Backup and Restore the SSAS Database
3. Synchronize SSAS Databases
4. All of Above

Show Answer

 
 
5) Where is Cube Data stored?

1. In Server
2. In Client
3. Stored in Server , can a subset of data be taken in Client
4. All of Above

Show Answer

 
 
6) How to improve Cube Performance

1. Partitioning
2. Usage based optimization
3. Demoralizing
4. A and B

Show Answer

 
 
7) What is the Analysis Services database backup file extension?

1. bak
2. aak
3. abf
4. obf

Show Answer

 
 
8) Can you restore Analysis Services database with a different database name?

1. YES
2. NO

Show Answer

 
 
9) When a cube is Write-Back enabled, it requires what all permissions?

1. READ
2. READ/WRITE
3. DDL
4. READ/WRITE & DDL

Show Answer

 
10) MOLAP cache is available while processing new data for

1. Low Latency MOLAP
2. Medium Latency MOLAP
3. Automatic MOLAP
4. MOLAP

Show Answer

 
 
11) Notifications are not received for

1. Low Latency MOLAP
2. Medium Latency MOLAP
3. Automatic MOLAP
4. MOLAP

Show Answer

 
 
12) You are managing a Microsoft SQL Server Analysis Services (SSAS) instance. You have a
cube in an SSAS instance. You notice that the queries run slowly. You need to identify the
queries that run slowly and change the storage configuration of the cube.

1. Run the Cube Storage Wizard to analyze and design aggregations
2. Run the Usage-Based Optimization Wizard to analyze and design aggregations.
3. Use SQL Profiler to examine the execution plans to analyze and design aggregations.
4. Modify the Data Source Impersonation Info property of the Analysis Services database.

Show Answer

 
 
13) 15. Which of the following are deployment methods:

1. Synchronize Database
2. Deploy command
3. Backup and restore
4. . All of the above

Show Answer

 
 
14) Which of the following is a Data Mining Algorithm

1. Translation
2. Clustering
3. Drill-through
4. None of the Above

Show Answer

 
 
15) How partitions does a cube has by default

1. 0
2. 1
3. 2
4. 4

Show Answer

 
 
16) Which type of storage architecture gives faster performance ?

1. MOLAP
2. ROLAP
3. DOLAP
4. All of the above

Show Answer

 
 
17) Choose the best option to use bitmap index

1. Employee name
2. Employee ID
3. Employee Joining date
4. None of the above

Show Answer

 
 
18) To efficiently handle high frequency searching operation in OLAP application
what is used ?
 

1. Binary Index
2. Bitmap Index
3. Secondary Index
4. None of the above

Show Answer

 
19) What is the disadvantage of HOLAP architecture from the below options ?

1. Integration with data maintained in RDBMS
2. Support for large volumes of data by Relational Mappings
3. Complex Architecture
4. None of the Above

Show Answer

 
 
20) Your organization has decided to standardize on the SQL Server 2005 platform for business intelligence (BI). Currently, you have three SQL Server Analysis Services (SSAS) 2000 applications on the same server, and you have targeted two of them for an immed

1. Run the Analysis Services Migration Wizard against the two cubes and tune the cubes as needed.
2. Upgrade your SSAS 2000 instance to SSAS 2005.
3. Back up your SSAS 2000 cubes and restore them on SSAS 2005.
4. Rebuild your cubes from the beginning on SSAS 2005.

Show Answer

 
 
21) You need to select a data-notification option for a partition that is configured for proactive caching. The cube loads data from an Oracle database. The data-notification strategy should require minimum implementation effort.
Which data-notification strat

1. SQL Server.
2. Client initiated.
3. Scheduled polling.
4. You cannot use proactive caching with non-SQL Server data sources.

Show Answer

 
 
22) You have a Product dimension that contains a Product attribute as the dimension key. When browsing the Product attribute, you realize that it shows the primary key values from the underlying dimension table. The dimension table has a ProductName column. Y

1. Change the KeyColumns property to the ProductName column.
2. Change the DefaultMember property of Product attribute to the ProductName column.
3. Create the Product Name attribute from the ProductName column.
4. Change the NameColumn property to the ProductName column.

Show Answer

 
 
23) You have a Product dimension that has a Price Range attribute. You don’t want the users to be able to browse data by the Price Range attribute alone. The Price Range attribute participates in a Product Model hierarchy with two levels, formed by the Produc

1. AttributeHierarchyOrdered
2. AttributeHeirarchyVisible
3. AttributeHierarchyEnabled
4. AttributeHierarchyOptimizedState

Show Answer

 
 
24) You have an Internet Sales measure group that is based on a FactInternetSales fact table. Each fact record in the fact table represents a line order number. The FactInternetSales fact table has a SalesOrderNumber column. The end users have requested the a

1. Regular
2. Referenced
3. Many-to-Many
4. Fact

Show Answer

 
 
25) You have a cube that uses several dimensions, including an Employee dimension. You don’t want the user to be able to browse the cube data by the Employee dimension. However, you plan to use the Employee dimension in multidimensional expressions such as ca

1. Remove the Employee dimension from the cube.
2. Set the AllMemberAggregationUsage of the Employee cube dimension to None.
3. Use No Relationship for the Employee dimension in the Dimension Usage tab.
4. Use the Dimensions pane in the Cube Browser and change the Visible property of the Employee dimension to False.

Show Answer

 
 
26) You have a Reseller Sales key performance indicator whose trend property uses the following expression:
([Measures].[Reseller Sales Amount] – ([Order Date].[Calendar].PrevMember,
[Measures].[Reseller Sales Amount]))/[Measures].[Reseller Sales Amount]
Th

1. Acceptable performance
2. Good performance
3. Bad performance
4. Unknown performance

Show Answer

 
 
27) You have a cube that has a Product dimension. The Product dimension has a ProductName attribute. You need to define a URL action that enables the user to navigate to a Web page called ShowTransactions.asxp, which shows the orders placed for this product.

1. Set the action target type property to Cube.
2. Set the action target type property to Attribute members.
3. Set the action target object property to the dimension key attribute.
4. Set the action target object property to the measure attribute.

Show Answer

 
 
28) you have the following multidimensional expressions query:
Select [Measures].[Internet Sales Amount] on Columns,
[Dim Product].[Product].[Product].<function> on Rows
From [Adventure Works DW]
Where [Date].[Calendar Year].&[2003]
Which multidimensiona

1. Members
2. Children
3. All
4. Siblings

Show Answer

 
 
29) You have the following definition of a calculated member:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Amount]
AS [Measures].[Internet Sales Amount] +
[Measures].[Reseller Sales Amount],
FORMAT_STRING = “Currency”
You need to optimize the calculated

1. CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Amount] AS [Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount], NON_EMPTY_BEHAVIOR = {[Internet Sales Amount], [Reseller Sales Amount]};
2. CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Amount] AS NON EMPTY [Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount];
3. CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Amount] AS [Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount], NON_EMPTY {[Internet Sales Amount], [Reseller Sales Amount]}
4. CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Amount] AS [Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount], NON_EMPTY {[Internet Sales Amount] * [Reseller Sales Amount]}

Show Answer

 
30) You have deployed a cube to production. You need to optimize the cube aggregation design based on the actual queries that have been submitted to the server.
Which tool would you use?

1. Usage-Based Optimization Wizard
2. AmoBrowser
3. Aggregation Design Wizard
4. Aggregation Manager utility

Show Answer

 
 
31) You need to use SQL Server Profiler to determine whether a given query is satisfied from an existing aggregation.
Which event do you need to select to watch aggregation hits?

1. Query Subcube
2. Discover Begin
3. Query Subcube Verbose
4. Get Data From Aggregation

Show Answer

 
 
32) You need to make a change to the MDX script of a cube deployed to a production server. You don’t have the SQL Server Analysis Services project or the source files. You need to perform the change as soon as possible.
What deployment option would you use?

1. BIDS in connected (online) mode
2. BIDS in project mode
3. Deployment Wizard
4. XMLA Script

Show Answer

 
 
33) Your cube has a Geography dimension that contains a Country attribute. The Country attribute contains all countries in the world. You need to create a role that can see all countries except France.
What’s the easiest way to configure the Country dimensio

1. Use an MDX expression that filters the members of the Country attribute.
2. Enable Visual Totals.
3. Create a denied set.
4. Create an allowed set.

Show Answer

 
 
34) You want to back up an SSAS database in SQL Server Management Studio (SSMS). You want to save the backup file in the C:\Temp folder on the server on which SSAS is installed.
What do you need to do before you perform the backup operation?

1. You cannot save backup files to any location other than the default backup folder.
2. Do nothing. You can save the backup file to any folder by default.
3. Add the C:\Temp folder to the BackupDir server property.
4. Add the C:\Temp folder to the AllowedBrowsingFolders server property.

Show Answer

 
 
35) You want to back up an SSAS database in SQL Server Management Studio (SSMS). You want to save the backup file in the C:\Temp folder on the server on which SSAS is installed.
What do you need to do before you perform the backup operation?

1. Add the C:\Temp folder to the BackupDir server property.
2. Add the C:\Temp folder to the AllowedBrowsingFolders server property.
3. Do nothing. You can save the backup file to any folder by default.
4. You cannot save backup files to any location other than the default backup folder.

Show Answer

 
 
36) You generate a backup script file in SQL Server Management Studio (SSMS). You want to use SQL Server Agent to schedule the backup operation.
What type of SQL Server Agent job step do you need to choose to execute the script?

1. Click the Script button in the Backup Database dialog box in SQL Server Management Studio (SSMS).
2. Get the script from the database definition file in BIDS.
3. Right-click the database in Business Intelligence Development Studio (BIDS) and choose Back Up.
4. Obtain the backup script from the Deployment Wizard.

Show Answer

 
 
37) You are testing a cube. You use Microsoft Excel to browse the cube. Without performing any management tasks, you need to find what Multidimensional Expressions (MDX) queries Excel sends to the server.
Which tool would you use to see the MDX queries sent

1. SQL Server Profiler
2. Query Log table
3. Windows Performance Monitor
4. Windows Event Log

Show Answer

 
 
38) You deploy a cube to production. An end user reports that the server has not responded for the past 15 minutes. You need to identify the activity that caused the server to crash.
Which tool would you use?

1. Windows Performance Monitor
2. Flight Recorder
3. Windows Event Log
4. SQL Server Profiler

Show Answer

 
 
39) You need to load test a SQL Server Analysis Services (SSAS) server as part of a capacity planning effort. You use Visual Studio 2005 Test Edition to prepare test scripts. You need to monitor the server usage, such as server CPU and memory use.
Which tool

1. Windows Performance Monitor
2. Flight Recorder
3. SQL Server Profiler
4. Windows Event Log

Show Answer

 
40) One of the responsibilities you have as an administrator for SSAS is to monitor SSAS querying so that you can optimize aggregation for usage-based optimization of the measure group partitions.
What do you need to do to capture the queries for usage-based

1. Turn on the QueryLog settings in the SSAS server properties.
2. Run SQL Server Profiler and capture the SSAS MDX queries.
3. Use Windows Performance Monitor to capture SSAS:Query counters.
4. Turn on the aggregation optimization setting in the Measure Group partition properties.

Show Answer

 
 
41) What type of relationship could exist between Fact and Dimension?

1. Fact Relationship
2. Regular Relationship
3. a or b
4. a & b

Show Answer

 
 
42) A member of a dimension whose value is calculated at run time using an expression

1. True
2. False

Show Answer

 
 
43) In OLE DB for OLAP, the set of multidimensional data that is the result of executing a MDX SELECT statement is

1. Datapump
2. Data Scrubbing
3. DataMember
4. Dataset

Show Answer

 
 
44) An ordered collection of members from different dimensions

1. Flagged Dimension
2. Virtual Dimension
3. Tuples
4. Virtual Cube

Show Answer

 
 
45) A dimension used to filter multidimensional data

1. Slice
2. Slicer
3. Dice
4. Dicer

Show Answer

 
 
46) Data mining Commonly involves

1. Regression
2. Clustering
3. Classification
4. All the above

Show Answer

 
 
47) Which is a datamining technique that involves finding Existing pattern in data

1. Pattern Mining
2. Subject based data mining
3. Spatial data Mining
4. Previous Datamining

Show Answer

 
 
48) data mining technique involving the search for associations between individuals in data

1. Pattern Mining
2. Subject based data mining
3. Statistical Mining
4. Previous Datamining

Show Answer

 
 
49) Data clustering can also be used to automatically discover the segments or groups within a customer data set

1. True
2. False

Show Answer

 
50) The views available in Calculation Expressions pane are

1. KPI View & Measure View
2. Form View and Script View
3. Both A & B
4. None of the above

Show Answer

 
 
51) Calculated member values are calculated at ___________

1. Query time
2. Design Time
3. Both A & B
4. None of the above

Show Answer

 
 
52) which of he following in not a correct relationship in ‘Dimension Usage’ tab in SSAS

1. Regular
2. No-Regular
3. Fact
4. Reference

Show Answer

 
 
53) Which of the below type of contents present in SQL Server Analysis Services  database backup that uses the Hybrid OLAP (HOLAP) storage mode?

1. Metadata, aggregations, and source data
2. Metadata and source data
3. Metadata and aggregations
4. Metadata

Show Answer

 
 
54) You noticed that execution of MDX queries are taking more time than you expected. You want to capture the full MDX query and analyze it. What should you do?

1. On the SSAS Server Logging Properties page, set the Log\Query Log\CreateQueryLogTable to True.
2. On the SSAS Server Logging Properties page, set the Log\QueryLog\QueryLogSampling to 5.
3. Use SQL Profiler
4. Use Performance Monitor.

Show Answer

 
 
55) You open the Dimension key errors tab in the Change Settings dialog box of a dimension and you want to ensure that when the number of errors exceeds a specified threshold, the errors are no longer logged, but the processing operation continues . Which opt

1. Key error action
2. On error action
3. Number of errors
4. Ignore errors count

Show Answer

 
 
56) Which of the following is a correct Time function in SSAS?

1. COUNT
2. MAX
3. STDEVP
4. PARALLELPERIOD

Show Answer

 
 
57) You want to get the best performance from your calculations in SSAS. What best practice you follow while writing the MDX code?

1. Use the NonEmpty function to reduce set size.
2. Convert all CASE statements to IIF statements.
3. Assign values of 0 and Null to empty cells
4. Use the LookupCube function.

Show Answer

 
 
58) Reference dimensions let you create a relationship between

1. Parent dimension and child dimension
2. No such kind of dimension exist in SSAS
3. Two measure groups using an intermediate dimension
4. A measure group and a dimension using an intermediate dimension

Show Answer

 
 
59) The relationships between members of  attributes can be

1. Rigid or Non-Rigid
2. Flexible or Non-flexible
3. Flexible or Rigid
4. None of the above

Show Answer

 
60) A role-playing dimension is a dimension that is used in a cube more than one time, each time for a different purpose

1. May be True
2. May be False
3. TRUE
4. FALSE

Show Answer

 
 
61) Built-in time dimension does not support multiple calendars

1. TRUE
2. FALSE
3. Cant determine
4. None

Show Answer

 
 
62) Each role-playing dimension is joined to a fact table on

1. Primary key
2. Foreign key
3. Different foreign keys
4. None

Show Answer

 
 
63) In SSAS, balanced hierarchies are supported in _____________ dimensions

1. Parent-child dimensions
2. Regular and Virtual dimensions
3. Reference dimensions
4. All of the above

Show Answer

 
 
64) Description of the order of levels in a dimension is called

1. Hierarchy
2. DrillDown
3. Junk Dimension
4. None of The Above

Show Answer

 
 
65) How many no of axes can be defined to get the query result using MDX

1. 1
2. 2
3. 3
4. 5

Show Answer

 
 
66) “AVG” is an aggregate function of  a cube. State whether true or false

1. True
2. False
3. Can’t determine
4. May be True

Show Answer

 
 
67) In which tab in BIDS environment user can create a relation between a dimension and a measure group?

1. Dimension Usage Tab
2. Action Tab
3. Perspective Tab
4. Translation Tab

Show Answer

 
 
68) Default member of a dimension attribute hierarchy is used to evaluate expressions when an attribute hierarchy is not included in a query.

1. True
2. False
3. May be True
4. May Be False

Show Answer

 
 
69) You can set the usage for an attribute by using the ____________________ view in Dimension Designer

1. Dimension Grid
2. Dimension Script
3. Dimension Usage
4. Dimension Structure

Show Answer

 
70) You are managing a SQL Server 2008 Analysis Services (SSAS) database which includes a Sale dimension that includes the Category and Subcategory properties and the relation is rigid type for properties. The data source for the Sale dimension alters the rel

1. You should utilize the ProcessDefault command.
2. You should utilize the ProcessClear command.
3. You should utilize the ProcessIndexes command.
4. You should utilize the ProcessDefault and the ProcessClear commands.

Show Answer

 
 
71) You have a requirement to include parent-child hierarchy for a dimension. There is children attribute named EmployeeTest and a parent attribute named EmployeesTest.
you should make sure that the topmost employee is included. Chose a correct method from

1. Configure the IsAggregatable property of the Employee attribute to default.
2. Configure the IsNotAggregatable property of the Employee attribute to false.
3. Configure the IsNotAggregatable property of the Employees attribute to true.
4. Configure the IsAggregatable property of the Employees attribute to false.

Show Answer

 
 
72) What is the file extension of a dimension

1. .dimf
2. .dim
3. .dimx
4. No extension exist

Show Answer

 
 
73) You need to add a new dimension to an existing cube. You have taken care of adding the dimension view to DSV, changing the dimension properties, establishing relationships with measure groups. What is the next step to follow in order to browse the dimensi

1. Deploy and Process Full
2. Unprocess
3. Do nothing, simply browse
4. None of the above

Show Answer

 
 
74) What is the output of the below MDX query:
SELECT
FROM [Adventure Works]

1. Value something like ‘$80,450,596.98’
2. An Error showing syntax error.
3. An Error saying select Rows and Columns.
4. No Output

Show Answer