SSAS Quiz

Posted on: | By: – Comments Off

SSAS Intermediate Quiz

SSAS Quiz : This SSAS Intermediate Quiz contains set of 90 SASS MCQ which will help to clear any any exam which is designed for mid level exam..

1) You will use only 10columns out of 100columns from a table sourced from a database where you do not have access to create any new objects. Which method do you use to display only 10columns in your Data Source View.

  1. New Named Query
  2. New Named Calculation
  3. Database View
Answer : A

2) How many fact tables can be used in a cube in SQL Server 2005 onwards?

  1. Only One able
  2. Many tables
Answer : B

3) Which is a conformed dimension for any BI application

  1. Warehouse
  2. Time
  3. Account
Answer : B

4) Department data of an organization is maintained in two separate tables as MainDepartmentDim and SubDepartmentDim. Employee table contains employee data along with SubDepartmentID. What kind of schema is this?

  1. Star Schema
  2. Snow-flake Schema
Answer : B

5) Department data of an organization is maintained in two separate tables as MainDepartmentDim and SubDepartmentDim. Employee table contains employee data along with SubDepartmentID. What kind of dimension relation do you create to retrieve the employee data at Main department level from OLAP Cube?

  1. Regular
  2. Referenced
  3. Many-to-Many
  4. Fact
Answer : B

6) A dimension contains attributes. You want to group them based on the nature of the data. What property do you use to achieve this?

  1. AttributeHierarchyDisplayGroup
  2. AttributeHierarchyDisplayFolder
Answer : B

7) Cube partitions data can be stored in multiple files on

  1. Only one physical drive
  2. multiple drives of a system only
  3. multiple servers
Answer : C

8) Cube partitions can use multiple

  1. tables
  2. views
  3. Both A and B
  4. Either tables or Views
Answer : C

9) Process Update option in cube Processing does

  1. add newly available fact data
  2. Re-read data and update dimension
  3. Re-read data and update all partitions with latest data
Answer : B

10) A measure can be aggregated across couple of dimensions only but not for all dimensions. Such dimension is called

  1. Additive
  2. Semi-Additive
  3. Non-Additive
Answer : B

11) Default property of Write-Back option

  1. Create
  2. Create Always
  3. Use Existing
Answer : B

12) A cube can have multiple partitions and partitions can be stored on

  1. Same kind of OLAP storage modes
  2. Multiple kinds of OLAP storage modes
Answer : B

13) In which tab do you create a NamedSet

  1. Perspective tab
  2. Actions tab
  3. Calculations tab
  4. Translation tab
Answer : C

14) How to Process a Cube?

  1. Right Click and Process in SSAS
  2. Create SSIS package to process
  3. ALL of A and B
  4. Any of A or B

Answer : D



15) Which OLAP Storage mode should be used to reflect transaction immediately without processing the cube

  1. MOLAP
  2. ROLAP
  3. HOLAP
  4. All of Above
Answer : B

16) How to employ Cube Security?

  1. By Employing Role Based
  2. Dimensional Security
  3. By Creating Application security that connects to the Cube
  4. None
Answer : A

17) How many measures can be created with same name In a Cube, when cube has two measure groups?

  1. One measure
  2. two measures
  3. many
Answer : B

18) A cube contains Time and Department dimensions along with Salary, DA measures. How many number of dimensions will that cube contain?

  1. 2
  2. 3
  3. 4
  4. 5
Answer : B

19) Which is not an Analysis Services security role?

  1. Server
  2. Database
  3. Cube
Answer : C

20) What is the default authentication mode for SQL Server Analysis Services?

  1. Windows Authentication
  2. SQL Authentication
  3. Mixed Mode Authentication
Answer : A

21) At what object level security permissions cannot be granted?

  1. Cube-Level
  2. Cell-Level
  3. Measure-Level
  4. Dimension-Level
Answer : C

22) Which property do you change to grant access on a dimension to the role?

  1. Dimension Meta Definition
  2. Read Definition
  3. Dimension Definition
Answer : B

23) Analysis Services Processing Task from SSIS is used to

  1. Process objects
  2. create and process objects
  3. create objects
Answer : B

24) You are developing a Microsoft SQL Server Analysis Services (SSAS) project. The project
contains a cube named Finance. The Finance cube contains the following objects:
A measure group named SalesMeasures.
A measure named SalesAmount.
A time dimensio

  1. Case When [SalesMeasures].[SalesAmount]> [SalesMeasures],[SalesAmount],[DimTime].[Calendar].PrevMember Then 1 When [SalesMeasures].[SalesAmount]< [SalesMeasures].[SalesAmount][DimTime].[Calendar].PrevMember Then -1 End
  2. Case When [SalesMeasures].[SalesAmount][DimTime].Calendar]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].PrevMember Then 1 When [SalesMeasures].[SalesAmount].[DimTime][Calendar]> [SalesMeasures].[SalesAmount].[DimTime].[Calendar].PrevMember Then -1
  3. Case When [SalesMeasures].[SalesAmount]> [SalesMeasures].[SalesAmount],[DimTime],[Calendar].NextMember Then -1 When [SalesMeasures].[SalesAmount]< [SalesMeasures].[SalesAmount].[DimTime].[Calendar].NextMember Then -1End
  4. Case When [SalesMeasures].[SalesAmount]< [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then -1 When [SalesMeasures].[SalesAmount]> [SalesMeasures].[SalesAmount],[DimTime].[Calendar].NextMember Then -1 End
Answer : A

25) You are developing a Microsoft SQL Server Analysis Services (SSAS) project.
You create a cube that contains the following objects:
A time dimension named Time that has an attribute named Timekey.
A fact table named FactOrders.
The Time dimension cont

  1. Create a single dimension usage relationship between the Time dimension and the FactOrders fact table by defining a regular relationship.
  2. Create a single dimension usage relationship between the Time dimension and the FactOrders fact table by defining a regular relationship.
  3. Create a single dimension usage relationship between the Time dimension and the FactOrders fact table by defining a regular relationship.
  4. Create two dimension usage relationships between the Time dimension and the FactOrders
  5. fact table by defining a many-to-many relationship for both the dimension usages.
Answer : C

26) You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a
cube that uses a dimension named DimEmployee. The dimension contains an attribute named
Employee. You also host a Microsoft SQL Server 2005 Reporting Services (S

  1. Create a URL action on the Employee attribute.
  2. Create a Rowset action on the Employee attribute.
  3. Create a URL action on the DimEmployee dimension..
  4. Create a Rowset action on the DimEmployee dimension.
Answer : A

27) You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a
dimension that contains a parent-child hierarchy. The dimension has a key attribute named
Employee and a parent attribute named Employees. You need to ensure tha

  1. Set the IsAggregatable property of the Employee attribute to true.
  2. Set the IsAggregatable property of the Employee attribute to true.
  3. Set the IsAggregatable property of the Employees attribute to true.
  4. Set the IsAggregatable property of the Employees attribute to false.
Answer : D

28) You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a
cube named ExpenseData that uses a dimension named Expense.
The Expense dimension is a parent-child dimension with:
a key attribute named Expense.
a parent attri

  1. Change the access to Read/Write for dimension Expense for the ExpensesData cube.
  2. On the Cell Data tab, select the Enable read permissions check box. Enter the [Expense].[Expenses].Parent.Name<>”Managerial Expenses” expression
  3. On the Cell Data tab, select the Enable read permissions check box. Enter the [Expense].[Expenses].Parent.Name=”Managerial Expenses” expression.
  4. On the Dimension Data tab, select the dimension Expense in the ExpensesData cube in the Dimension drop-down box, and then cancel the selection of all the members under the member Managerial Expenses.
Answer : B

29) You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project.
You create a cube with the following objects:
A time dimension named Time.
A fact table that contains two columns named Order Date Key and Numbers of Orders.
A Number of Order

  1. SUM([Order Date}.[Calendar].CurrentMember, [Measures] [Number of Orders])
  2. SUM( PeriodsToDate([Order Date].[Calendar].CurrentMember.Parent.Level, [Order Date] [Calendar] .CurrentMember), [Measures].[Number of Orders])
  3. SUM( PeriodsToDate([Order Date].[Calendar].CurrentMember.Parent, [Order Date] [Calendar].CurrentMember), [Measures].[Number of Orders])
  4. SUM([Order Date] .[Calendar].CurrentMember,SUM ([Order Date].[Calendar].CurrentMember.Parent, [Measures].[Number of Orders]))

Answer : B



30) You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. The project
has a fact table named FactOrders that contains columns named Order Amount, Country, and
Order Date Key. You create the first partition for the fact table with th

  1. Create a partition with the Source properly set to “Select * from FactOrders where [Order Date Key]<=354 and Country<>”USA”.
  2. Create a partition with the Source property set to “Select * from FactOrders where [Order Date Key]<=354”
  3. Create a partition with the Source properly set to “Select * from FactOrders where Country<>”USA”
  4. Create a partition with the Source properly set to “Select * from FactOrders”.
Answer : A

31) You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project.
Your data source contains the following tables:
Products table with a column named Productcategories_ID
ProductCategories table with a column named ID
The ProductCategories I

  1. (none)
  2. Same name as primary key
  3. Same name as destination table name
  4. Same name as destination table name + primary key name
Answer : A

32) You are managing a Microsoft SQL Server 2005 Analysis Services (SSAS) instance. You deploy
a data mining structure that includes the Microsoft Clustering mining model. You need to change
the properties of the mining model from Scalable K-Means to Non-scal

  1. MODELLING CARDINALITY
  2. CLUSTERING_METHOD
  3. CLUSTER_COUNT
Answer : B

33) You are managing a Microsoft SQL Server 2005 Analysis Services (SSAS) instance. You want to
change the design of the data mining structure to test different data mining theories. You add
columns of the Ordered content type in the data mining model and the

  1. ProcessFull
  2. ProcessData
  3. ProcessStructure
  4. Processlncremental
Answer : A

34) You are designing a Microsoft SQL Server 2005 Analysis Services (SSAS) solution. The SSAS
database includes a data mining model that uses the Microsoft Neural Network algorithm. You
use Prediction Query Builder in Microsoft SQL Server Management Studio (S

  1. A PredictAssociation field that uses a Prediction Function source.
  2. A PredictHistogram field that uses a Prediction Function source.
  3. A PredictAssociation field that uses the data mining model source.
  4. A PredictHistogram field that uses the data mining model source.
Answer : B

35) You are designing a data mining structure for a Microsoft SQL Server 2005 Analysis Services
(SSAS) solution. The data mining structure must forecast the day of the month on which a
combination of products are most likely to sell. You need to configure pro

  1. Set the Content type of the column as Cyclical in the data mining structure and the column as Input in the data mining model.
  2. Set the Content type of the column as Key Time in the data mining structure and the column as Input in the data mining model.
  3. Set the Content type of the column as Cyclical in the data mining structure and the column as Predictable in the data mining model.
  4. Set the Content type of the column as Key Time in the data mining structure and the column as Predictable in the data mining model.
Answer : C

36) You are managing a Microsoft SQL Server 2005 Analysis Services (SSAS) instance. The
instance contains cubes that use the HOLAP storage mode. The retrieval of data from the cubes
is slow. You need to monitor the frequency with which the users access a memb

  1. In the Storage Engine Queries object, monitor the Current measure group queries counter.
  2. In the Storage Engine Queries object, monitor the Current dimension queries counter.
  3. In the Connection object, monitor the Current user sessions counter.
  4. In the Connection object, monitor the Current connection counter
Answer : A

37) You are designing Microsoft SQL Server 2005 Analysis Services (SSAS) cubes for two business
units. Each business unit has the following requirements:
Full administrative access only to its own cubes and not to the cubes of the other unit.
Ability to run i

  1. Two clustered instances
  2. A single clustered instance
  3. Two nonclustered instances
  4. A single nonclustered instance
Answer : A

38) You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) solution by using
Business Intelligence Development Studio (BIDS). The SSAS instance runs under a local system
account. A trust relationship exists between the machine that hosts the

  1. Default
  2. Service account
  3. Credentials of the current user
  4. Specific user name and password
Answer : D

39) You are designing a Microsoft SQL Server 2005 Analysis Services (SSAS) solution. The SSAS
database includes a data mining model that uses the Microsoft Neural Network algorithm. You
use Prediction Query Builder in Microsoft SQL Server Management Studio (S

  1. A PredictAssociation field that uses a Prediction Function source.
  2. A PredictHistogram field that uses a Prediction Function source.
  3. A PredictAssociation field that uses the data mining model source.
  4. A PredictHistogram field that uses the data mining model source.
Answer : B

40) You are designing a data mining structure for a Microsoft SQL Server 2005 Analysis Services
(SSAS) solution. The solution requires the use of relational data. You need to design the data
mining structure so that the data mining models can use the data fro

  1. Set the case table column as a key.
  2. Set the nested table column as a keyt
  3. Set the case table column as a key and an input, and then set the nested table column as a
  4. Set the case table column as a key, and then set the nested table column as both a key and an input.
Answer : B

41) You are managing a Microsoft SQL Server 2005 Analysis Services (SSAS) instance. You have a
machine named COMPANYCUBE for a SSAS instance. The COMPANYCUBE machine hosts a
SSAS database that has a cube named HR. You need to restrict the local administrators

  1. Remove all the local administrators from the membership in the server role.
  2. Change the Security\BuiltlnAdminsareServerAdmins property of the SSAS instance to false.
  3. Create a role called AdminRole. Set the Access to the HR cube to None. Add the local administrators to the role.
  4. Remove the local administrators from the SQLServer2005MSOLAPUser$COMPANYCUBE$MSSQLSERVER local group.
Answer : B

42) You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) solution. A database
named Call Center Metrics contains a data mining structure named Call Center Forecasting. The
data mining structure implements the Microsoft Neural Network mining

  1. Increase the MAXIMUM_STATES parameter for each mining model.
  2. Decrease the MAXIMUM_STATES parameter for each mining model.
  3. Increase the SAMPLE_SIZE parameter for each mining model.
  4. Decrease the SAMPLE_SIZE parameter for each mining model.
Answer : B

43) You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project.
Your data source view has a table named Products. The Products table contains the following
three columns:
ProductKey
Cost
Discount
The table also contains a named calculatio

  1. Create a named calculation column named DiscountedCost with the following expression: CosrDiscount/100
  2. Create a named calculation column named DiscountedCost with the following expression: Select CosrDiscount/1 00 from Products
  3. Convert the table to a named query with the following query definition: Select ProductKey, Cost, Discount, PercentageCost, CosrDiscount/100 as DiscountedCost from Products
  4. Use the Execute Convert the table to a named query with the following query definition: Select ProductKey,Cost, Discount, PercentageCost from Products. Add a named calculation column named DiscountedCost with the following expression: Select CosrDiscount
Answer : A

44)
You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. The cube
must store details of the data in a relational format and the aggregations in a multidimensional
format. Which of the predefined Proactive Caching values property s

  1. Real-time ROLAP
  2. Real-time HOLAP
  3. Automatic MOLAP
  4. Scheduled MOLAP

Answer : D



45)
You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project.
Your data source has the following tables:
Scenarios table that contains columns named Scenario Key and ScenarioName.
Fact Finance table that contains a foreign key column n

  1. Select ScenarioName as ScenarioCalc where ScenarioKey=FactFinance.ScenarioKey
  2. (Select ScenarioName from Scenarios where ScenarioKey=Scenarios.ScenarioKey)
  3. (Select ScenarioName from Scenarios where ScenarioKey=FactFinance.ScenarioKey)
  4. ScenarioCalc=Select ScenarioName from Scenarios where
  5. ScenarioKey=FactFinance.ScenarioKey
Answer : C

46) QUESTION 74
You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project.
The project consists of a cube that contains the following objects:
A time dimension named DimTime that contains a hierarchy named Calendar. The first level of
th

  1. LastNonEmply
  2. FirstNonEmpty
  3. Count
  4. Sum
Answer : A

47) You are developing a Microsoft SQL Server 2005 Analysis Services (SSAS) project. You create a
cube that uses a dimension named Product. The Product dimension contains an attribute named
ProductName. You also create a URL action and set the action expressi

  1. Set the target type to Attribute Members and the target object to Product.ProductName.
  2. Set the target type to Attribute Members and the target object to Product.
  3. Set the target type to Dimension Members and the target object to Product.
  4. Set the target type to cube and the target object to Current Cube
Answer : A

48) 14. Which of the following are supported by SSAS:

  1. Dimension-level security
  2. Cell-level security
  3. All of the above
  4. None of the above
Answer : C

49) Which of following is not a type of relationship between a dimension and a measure group:

  1. Regular
  2. Many to Many
  3. Fact
  4. One to One
Answer : D

50) What is the difference between Star schema and Normalized schema?

  1. Both are same
  2. Star schema has less data than Normalized schema
  3. Star schema is not normalized
  4. All of the above
Answer : C

51) why surrogate key is used?

  1. To track type 2 slowly changing dimensions
  2. To be primary key for fact table
  3. For faster query performance in data mart
  4. All of the above
Answer : D

52) Drill across refers to

  1. Querying multiple fact tables and combining results into a single data set
  2. Querying multiple hierarchies and combining results into single data set
  3. Data analysis across multiple dimensions
  4. None of the above
Answer : A

53) Bridge table is user for

  1. Confirmed dimension
  2. Multi valued dimension
  3. Degenerate dimension
  4. None of the above
Answer : B

54) Which of the dimension belongs to Fastly Changing dimension

  1. Product Dimension
  2. Customer Dimension
  3. Time Dimension
  4. None of the above
Answer : B

55) “A student tracking system that detects each student attendance event each day.”, What type of table is it

  1. Fact less facts
  2. Additive facts
  3. Casual Dimension
  4. Coverage Tables
Answer : A

56) Acronym for KPI ?

  1. Key Performance Indicators
  2. Key Performance Index
  3. Both
  4. None of the above
Answer : A

57) Can we normalize a fact tables ?

  1. Yes
  2. No
Answer : A

58) What is a junk dimension

  1. A junk dimension is a collection of random transactional codes, flags and/or text attributes that are unrelated to any particular dimension. Simply to say a randomly used dimension is junk dimension
  2. Junk dimension is a Cartesian product of dimensions which have very low cardinality.
  3. A and B
  4. None of the above
Answer : C

59) Type 2 SCD Stands for

  1. Overwriting the existing record
  2. Creating a new dimension record
  3. Creating a current value field
  4. None of the above

Answer : B



60) Define the table name used to implement many to many relationship

  1. Bridge table
  2. Hierarchies
  3. Both
  4. None of the above
Answer : A

61) Whats most important char of OLAP

  1. time variant
  2. non volatile
  3. integrated
  4. All of the above
Answer : C

62) In DWH , generally data setup will be in which form?

  1. 3NF
  2. BCNF
  3. Normalized
  4. Denormalized
Answer : D

63) __ Is the Numerical property of a fact which describes quantitative aspects for analysis.

  1. FACT
  2. Measure
  3. Dimension
  4. All of the above
Answer : B

64) This operation limits analysis to subset of an attribute

  1. Drill down
  2. Dice
  3. Slice
  4. Drill Through
Answer : B

65) MDDB technology is proprietary to which OLAP Architecture ?

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

66) ROLAP Architecture is Non Intuitive ?

  1. True
  2. FALSE
Answer : A

67) When it is best to pre-calculate data in any OLAP architecture ?

  1. Data that is Slow to calculate at run-time, because it depends on many other cells or complex formulae or
  2. Data that is frequently viewed
  3. Data that is the basis of many other calculations
  4. All of the Above
Answer : D

68) What are the prerequisites for OLAP Administration ?

  1. Data management
  2. Application management
  3. Metadata management
  4. All of the Above
Answer : D

69) Which is a 3- tiered Architecture from the following ?

  1. MOLAP
  2. ROLAP
  3. None of the above
  4. Both (a) and (b)
Answer : D

70) Property of fact across which its analysis co-ordinates are defined is called ?

  1. Dimension
  2. Fact
  3. Measure
  4. None
Answer : A

71) DWH generally contains data in which of the following form ?

  1. Star Schema
  2. Snowflake
  3. None of the above
  4. Both (a) and (b)
Answer : B

72) Removing Partial dependency is step to which normal form ?

  1. 1st NF
  2. 2nd NF
  3. 3rd NF
  4. BCNF
Answer : C

73) which of the following is not the schema for data warehouse

  1. a) 3 NF
  2. Star Schema
  3. Snowflake
  4. None of the above
Answer : D

74) Summary tables is data warehouse is called as ______________________-

  1. Aggregate tables
  2. Factless fact Table
  3. Both
  4. None of the above
Answer : A

75) How do you join 2 fact tables______________-

  1. Confirmed Dimension
  2. Degenerated Dimension
  3. Junk Dimension
  4. None of the above

Answer : A



76) Which of the following is not a data modeling practice

  1. Conceptual data model
  2. Logical data model
  3. Dimensional Data model
  4. Physical data model
Answer : C

77) How to select distinct values without using SQL distinct statements

  1. ORDER BY
  2. UNIQUE FUNCTION
  3. MINUS OPERATOR
  4. GROUP BY
Answer : D

78) You will use only 10 columns out of 100 columns from a table sourced from a database where you do not have access to create any new objects. Which method do you use to display only 10 columns in your Data Source View.

  1. New Named Query
  2. New Named Calculation
  3. Database View
  4. Materialised view
Answer : A

79) You have a financial cube that has Customer and Account dimensions. The cube has a measure group that has only non-additive measures such as rates and percentages. The lowest grain of the measure group is the Account dimension. The non-additive measures cannot be aggregated at a higher level than the individual customer account. The Account dimension joins the measure group with a Regular relationship.
What relationship type do you need to use for the Customer dimension if you want to prevent the end users from browsing the non-additive measures by the Customer dimension?

  1. Referenced Relationship
  2. Regular
  3. No Relationship
  4. Fact
Answer : C

80) You have a Customer dimension. One of the attributes is Phone Number, which displays the customer phone number. The users won’t browse data by this attribute. To optimize the dimension design, you want to prevent the server from creating an attribute hierarchy for the Phone Number attribute.

  1. AttributeHierarchyEnabled
  2. AttributeHierarchyOrdered
  3. AttributeHierarchyOptimizedState
  4. AttributeHeirarchyVisible
Answer : A

81) 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. You need to change the Product attribute to show the product name when end users browse the cube.
What change do you need to make to the Product attribute?

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

82) You have a cube that uses Reseller and Geography dimensions and has a Reseller Sales measure group. The Reseller dimension has a Regular relationship with the Reseller Sales measure group. The Geography dimension cannot be joined directly to the Reseller Sales measure group. Both the Geography dimension and the Reseller dimension have a Geography Key attribute. You need to let end users browse the reseller sales data by Geography.
What type of a dimension usage relationship do you need to join the Geography dimension to the Reseller Sales measure group?

  1. Regular
  2. Referenced
  3. Many-to-Many
  4. Fact
Answer : B

83) You have written the following multidimensional expressions query:
Select [Measures].[Internet Sales Amount] on Columns
From[Adventure Works DW]
What change do you need to make to the query to filter the results for year 2003 only?

  1. Select [Measures].[Internet Sales Amount] on Columns Where [Date].[Calendar Year].&[2003] From [Adventure Works DW]
  2. Select [Measures].[Internet Sales Amount] on Columns Where [Date].[Calendar Year] = 2003 From [Adventure Works DW]
  3. Select [Measures].[Internet Sales Amount] on Columns From [Adventure Works DW] Where [Date].[Calendar Year].&[2003]
  4. Select [Measures].[Internet Sales Amount] on Columns From [Adventure Works DW] Where [Date].[Calendar Year] = 2003
Answer : C

84) Can a local Windows administrator be denied data access to a SQL Server Analysis Services (SSAS) cube on the same server?

  1. No, local windows administrators cannot be denied access to SSAS.
  2. Yes, a local windows administrator can be denied access to an SSAS cube by not giving the group account a database access role.
  3. Yes, local windows administrators can be denied access to an SSAS cube, but the only way to accomplish this is to exclude the administrator group from the server administrative role.
  4. Yes, local Windows administrators can be denied cube access only if they are assigned a database role that revokes their access to the cube data.
Answer : A

85) You need to define an MDX expression that returns the top 10 most profitable customers. You want the results of this expression to be readily available to any client application that connects to the cube.
Which implementation approach would you use?

  1. Cell calculation
  2. Named set
  3. Query set
  4. Calculated member
Answer : B

86) You have a partition enabled for proactive caching. The data in the data source changes at random. You need to configure proactive caching to force the rebuilding of the Multidimensional OLAP cache in 30 seconds if the data changes continuously and there is no quiet period.
Which advanced proactive caching option do you need to set up to force the rebuilding of the Multidimensional OLAP (MOLAP) cache?

  1. Bring Online Immediately
  2. Silence Interval
  3. Silence Override Interval
  4. Rebuild Interval
Answer : C

87) You are working with a moderately large SQL Server Analysis Services (SSAS) solution and need to implement the SSAS data on a separate drive. You want all new Multidimensional OLAP (MOLAP) data for the cubes to be stored on the new drive rather than in the Program Files directory.
How can you accomplish this goal?

  1. In the Business Intelligence Development Studio SSAS project, set the Output Path to the new drive.
  2. In the Business Intelligence Development Studio SSAS project, set the FullPath property to the new drive.
  3. Set the DataLocation property at the server layer to point to the new drive.
  4. Set the DataDir property of the SSAS instance properties to the new drive.
Answer : D

88) You open a SQL Server Analysis Services project in BIDS and make changes to the cube source file in project mode. You need to deploy the changes to a test server for QA testing. The cube on the test server already has measure group partitions. You want the deployment process to preserve the partition design of the test cube.
Which deployment option would you use?

  1. Deployment Wizard
  2. Synchronization Wizard
  3. Backup and restore
  4. BIDS deployment
Answer : A

89) You have implemented a data source view that uses tables from a SQL Server database. A new column has been added to one of the tables.
What is the easiest way to update the data source view definition to include the new column?

  1. Click the Refresh Data Source View toolbar button.
  2. Open the data source view in the Business Intelligence Development Studio (BIDS) data source view Designer. The data source view Designer will automatically detect the change and update the data source view.
  3. Drop the affected table in the data source view and add it to the data source view.
  4. Manually add the new column.

Answer : A



90) You have a data source view that has a Product table based on a SQL Server view. You need to create a primary key on the ProductID column to identify the records in the Product table uniquely.
How could you accomplish this task with minimum effort?

  1. Create a logical primary key in the DSV.
  2. Create a logical primary key in the DSV.
  3. In the DSV, change the PrimaryKey property of the ProductID dimension to True.
  4. Create an index view and define a primary key. Refresh the DSV.
Answer : A

91) You have a data source view that has Product and ProductCategory tables. Both tables have a ProductCategoryID column, and there is a relationship on ProductCategoryID. The ProductCategory table has a CategoryName column. You need to add a named calculation column to the Product table that shows the product category by referencing the CategoryName column from the ProductCategory table.
Choose the correct SQL SELECT statement for the named calculation column.

  1. (SELECT CategoryName FROM ProductCategory WHERE ProductCategoryID = Product.ProductCategoryID)
  2. (SELECT CategoryName FROM ProductCategory WHERE ProductCategoryID IN ProductCategory.ProductCategoryID)
  3. (SELECT CategoryName FROM ProductCategory WHERE ProductCategoryID = ProductCategory.ProductCategoryID)
  4. (SELECT CategoryName FROM ProductCategory WHERE ProductCategoryID IN Product.ProductCategoryID)
Answer : A

92) You have a cube that has a Reseller Sales measure group. You add the Product dimension to the cube. When you browse the cube by the Product dimension, you see the same total for all products.
What is the most likely cause of this problem?

  1. The AttributeHierarchyEnabled property of the dimension key attribute in the Product dimension is set to False.
  2. There are no dimension usage relationships between the Product dimension and the Reseller Sales measure group.
  3. The AllMemberAggregationUsage of the Product cube dimension is set to None.
  4. The Product dimension doesn’t have a dimension key.
Answer : B

93) You are implementing a measure that needs to show the closing inventory balance for a given time period. If the inventory balance for the selected period is empty, the measure must return the inventory balance for the last time period that contains data. You want the measure to aggregate as fast as possible, and you use the Enterprise Edition of SQL Server 2005 Analysis Services.
Which aggregation function would you use?

  1. Custom aggregation function
  2. Sum
  3. AverageOfChildren
  4. LastNonEmpty
Answer : D

94) ou have a complex cube that contains many dimensions and measure groups. Users have requested a simplified view of the cube that enables them to focus on their subject area. For example, the human resources department would like to see, by default, the human resources-related objects only.
How would you implement logical subviews of the cube?

  1. Let the client application filter the cube metadata.
  2. Create a cube perspective for each logical view.
  3. Create a separate data source view for each logical view.
  4. Create a separate cube for each logical view.
Answer : B

95) You have a measure group that has several partitions. One of the partitions contains data for the last three months. The end users query this partition frequently. You need to select a storage mode that gives the best query performance.
Which storage mode should you select?

  1. Hybrid OLAP (HOLAP)
  2. Multidimensional OLAP (MOLAP)
  3. Let the server decide
  4. Relational OLAP (ROLAP)
Answer : B

96) You have a cube that loads data from an OLTP database. The data in the OLTP data source changes at random. The cube needs to update its data as soon as the data changes in the data source. You need to select a standard storage mode that provides low data latency and the best query performance.
Which storage mode should you select?

  1. Scheduled ROLAP
  2. Scheduled MOLAP
  3. Automatic MOLAP
  4. Realtime ROLAP
Answer : C

97) You are implementing a large cube, and you need to optimize the cube performance before it is deployed to production. You need to create an aggregation design with minimum effort.
Which tool would you use to create the aggregation design?

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

98) You open an Analysis Services project in BIDS. You attempt to deploy the project by right-clicking the project node in Solution Explorer and selecting Deploy. However, BIDS interrupts the deployment process with the following error message:
The project could not be deployed to the ‘SSASTEST1’ server because of the following connectivity problems: A connection cannot be made. Ensure that the server is running.
To resolve the deployment error, you need to change the deployment server from SSASTEST1 to SSASTEST2.
Which project property do you need to change?

  1. Database
  2. Server
  3. Processing Option
  4. Deployment Mode
Answer : B

99) You have a SQL Server Analysis Services database that contains several dimensions and two cubes. You need to process the database periodically to bring it up to date with the data changes in the data source. You want to process all database objects with minimum effort.
Which database processing option would you use?

  1. Process Full
  2. Process Default
  3. Unprocess
  4. Process Update

Answer : A



100) You have a Product dimension and a Sales cube that uses the Product dimension. You want to process the Product dimension to reflect the data changes committed to the dimension table. Dimension data changes include adding, deleting, and updating dimension members. You want to process only the Product dimension and not the Sales cube.
Which dimension processing option would you use to update the Product dimension?

  1. Process Update
  2. Process Full
  3. Process Add
  4. Process Default
Answer : A

101) You have a Sales cube that uses a Product dimension. You need to process the Product dimension fully. You are concerned that the processing operation might affect other objects.
How can you determine which objects will be affected by processing a given object before starting the processing operation?

  1. Script the Product dimension and inspect the <AffectedObjects> element.
  2. Use the Process Progress window.
  3. Use the Impact Analysis feature of the Process Object dialog box.
  4. Right-click the Product dimension and choose Show Affected Objects.
Answer : C

102) You have a large partition that is configured for proactive caching. You don’t want end users to experience performance degradation while the Multidimensional OLAP (MOLAP) cache is being rebuilt.
Which option do you need to set to ensure that the server never switches to Relational OLAP while the Multidimensional OLAP cache is being rebuilt?

  1. Bring Online Immediately
  2. Apply Settings To Dimensions
  3. Update The Cache Periodically
  4. Enable ROLAP Aggregations
Answer : A

103) You install SQL Server Analysis Services (SSAS) on a production server. You need to disallow the Windows system administrators (members of the built-in local administrators group) administrative access to the SSAS server and its objects.
Which server property do you need to change to disallow Windows system administrators administrative access to the Analysis Services server?

  1. Security\BuiltinAdminsAreServerAdmins.
  2. To prevent server lockdown, SSAS doesn’t let you revoke administrative rights for Windows administrators.
  3. Security\ServiceAccountIsServerAdmin.
  4. Security\RequireClientAuthentication.
Answer : A

104) A cube has Internet Sales Amount and Reseller Sales Amount measures. You have defined a calculated member, Sales Amount, as follows:
[Sales Amount] = [Internet Sales Amount] + [Reseller Sales Amount]
You need to prevent a role from seeing both the Internet Sales Amount measure and the Sales Amount calculated member.
What changes do you need to make to the role?

  1. You need to enable read contingent permissions on Internet Sales Amount.
  2. You need to enable read permissions on Internet Sales Amount.
  3. You need to enable read permissions on Sales Amount.
  4. You need to enable read contingent permissions on Sales Amount.
Answer : A

105) You have implemented a drillthrough action in a cube. You deploy the cube to production. Users belonging to the Admin role report that they can execute the drillthrough action. However, members of the Sales role get “The permission required for this operation has not been granted” error when they attempt to execute the drill-down action.
What is the most likely reason the Sales role cannot execute the drill-down action?

  1. The Sales role has Local Cube/Drilldown access set to Drillthrough.
  2. The Sales role has Local Cube/Drilldown access set to None.
  3. The Sales role has cube access set to Read.
  4. The Sales role has cube access set to None.
Answer : B

106) Mary needs to be able to process the cubes without being able to view the data in the database.
How can you implement the permissions for Mary?

  1. Give Mary a database role with only the Process database permission.
  2. Give Mary a server administrator role with only the Process database permission.
  3. Give Mary a server administrator role but not a database role.
  4. Give Mary a server administrator role and a database role that denies read access.
Answer : A

107) What is Full load?

  1. Completely erasing the contents of one or more tables and reloading with fresh data.
  2. Update entire table with the current data.
  3. Update only relevant record.
  4. . None of the above.
Answer : A

108) Incremental or Refresh load?

  1. Reload the table with the fresh data.
  2. Apply ongoing changes to one or more tables based on a predefined schedule
  3. Truncate the table and load only the changed records to the table at frequent intervals.
  4. None of the above.
Answer : B

109) How to get the attribute key with MDX query

  1. WITH MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Member_Key SELECT {Measures.ProductKey} ON axis(0), [Product].[Product Categories].Members on axis(1) FROM [Adventure Works]
  2. WITH MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Member SELECT {Measures.ProductKey} ON axis(0), [Product].[Product Categories].Members on axis(1) FROM [Adventure Works]
  3. WITH MEMBER Measures.ProductKey as [Product].[Product Categories].Currentmember.Key SELECT {Measures.ProductKey} ON axis(0), [Product].[Product Categories].Members on axis(1) FROM [Adventure Works]
  4. All of the above
Answer : A

110) How to get the Last month in the time dimension?

  1. SELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON FROM [Sales Summary] WHERE ([Measures].[Sales Amount])
  2. SELECT ParallelPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON FROM [Sales Summary] WHERE ([Measures].[Sales Amount])
  3. SELECT [Date].[Calendar].[Month], [Date].[Calendar].LastChild ON FROM [Sales Summary] WHERE ([Measures].[Sales Amount])
  4. Any of the above
Answer : A

111) Start model in SSAS Datasource view is normalized to 3NF

  1. True
  2. FALSE
Answer : B

112) It is possible to use DTS to build a CUBE

  1. True
  2. FALSE
Answer : A

113) SSAS Cubes lives in

  1. Client
  2. Server
  3. DatawareHouse
  4. None of the above
Answer : B

114) Security model of SSAS allows setting permissions for groups on

  1. Dimensions table level
  2. Dimension Keys level
  3. Dimensions down to the cell level
  4. None of the above
Answer : C

115) Which option optimize the trade of between Disk space for storing aggregations and System Performance

  1. Key performance Indication
  2. Actions
  3. Perspective
  4. Storage Design Wizard
Answer : D

116) Datamart is

  1. Data Schema
  2. Star Schema
  3. Lookuptable Schema
  4. None Of the above
Answer : B

117) cube explosion is the result of which OLAP

  1. Holap
  2. WebOLAP
  3. Molap
  4. Rolap
Answer : C

118) To retrieve the detailed data from which the data in a cube cell was summarized is

  1. Drill Down
  2. Drill Through
  3. Both
  4. None
Answer : B

119) A multidimensional data set presented as a two-dimensional rowset in which unique combinations of elements of multiple dimensions are combined on an axis is called as

  1. Fact Less Dimension
  2. Factless FACT
  3. Flattened Row Set
  4. Multi Dimensioonal Database
Answer : C

120) A sparse cube requires less storage than a dense cube of identical structure design

  1. True
  2. false
Answer : A

121) Analysis server that stores a linked cube

  1. Client
  2. Source Cube
  3. Subscribing Server
  4. None of the above
Answer : C

122) We can use a client browsing tools such as Microsoft Excel or a 3rd party application to attach to the data cube

  1. True
  2. False
Answer : A

123) A measure group is associated with a Multiple fact or event that is tracked by the OLAP database.

  1. True
  2. False
Answer : B

124) Which OLAP Stores the entire Cube in RAM

  1. Molap
  2. Dolap
  3. Web Olap
  4. RTOLAP
Answer : D

125) A cube is a

  1. Multi dimensional
  2. 2 dimensional
  3. 3 dimensional
  4. All the above
Answer : C

126) Do we need to have a data mart in order to build cubes?

  1. True
  2. False
  3. Can’t Say
  4. May be
Answer : B

127) Proactive Caching is implemented at which level

  1. Only Measures
  2. Only one Measures or Dimensions
  3. Each and every Measure & Dimensions
  4. All the above
Answer : C

128) KPIs created in SQL Server  Analysis Services cannot be used in PerformancePoint Services –

  1. FALSE
  2. TRUE
  3. Can’t determine
  4. May be True
Answer : A

129) Each type of summary that can be retrieved from a single dimension is called

  1. Summary views
  2. Aggregates
  3. Level
  4. Hierarchy
Answer : C

130) Which of the following is used to slice the CUBE?

  1. Partition
  2. Aggregation
  3. Actions
  4. KPI
Answer : A

131) ____________command controls the aggregation of the cells in the cube

  1. Aggregate
  2. Sum
  3. AutoSum
  4. Calculate
Answer : D

132) Sparse cubes have __________ in their data

  1. Zero values
  2. Gaps
  3. Character Values
  4. Both (a) and (C)
Answer : B

133) ____________ provides a way  to display more than one dimension on each axis.

  1. Slicing & Dicing
  2. Drill down & Drill up
  3. Nested dimensions
  4. Nested measures
Answer : C

134) Which of the following is the default instance of Analysis Services service?

  1. MSOLAP$default
  2. MSSQLServerOLAPService
  3. MSOLAP$instancename
  4. MSOLAP$MSSQLServer
Answer : B

135) Which of the following is correct with respect to KPI

  1. We can create dimensions using KPI’s
  2. We can create cube using KPI’s
  3. We can create roles using KPI’s
  4. We can create company’s performance chart using KPI’s
Answer : D

136) Which is not an Action type in SSAS

  1. Cube Actions
  2. Drillthrough Actions
  3. Reporting Actions
  4. Standard Actions
Answer : A

137) The default partitions created when you create a cube is:

  1. MOLAP
  2. HOLAP
  3. ROLAP
  4. Real – time HOLAP
Answer : A

138) ___________  option is not found while designing aggregation

  1. Estimated storage reaches
  2. Performance gain reaches
  3. I click Stop
  4. I click Start
Answer : D

139) Which of the below is not correct with respect to Data source View(DSV) in SSAS.

  1. We can create Named calculations in DSV
  2. We can deploy cube in DSV
  3. We can create relationships between fact and dimensions in DSV
  4. We can Explore data in DSV
Answer : B

140) Which of the option is not a valid Cube processing type in SSAS 2008?

  1. Process Full
  2. Process Structure
  3. Process Index
  4. Process New
Answer : D

141) Which of the following options is not available while processing a dimension

  1. Process update
  2. Unprocess
  3. Reprocess
  4. Process Data

Answer : C



142) Perspectives are not meant to be used as a security mechanism in SSAS.

  1. FALSE
  2. TRUE
  3. Can’t determine
  4. May Be False
Answer : B

143) SSAS Perspectives require  storage space.

  1. No, as perspectives are like view definitions in relational databases
  2. Yes as perspectives are like Cubes
  3. Storage requirements of the perspectives can be determined during design time
  4. None of the above
Answer : A

144) Similarities between SSAS perspectives and Views in RDBMS

  1. They narrow down what can be seen by user
  2. They can combine multiple cubes to look like one
  3. They are securable objects
  4. All of the above
Answer : A

145) Linked Cubes which were available in SSAS 2000 are discontinued in SSAS 2005

  1. False
  2. TRUE
  3. May be True
  4. Can’t determine
Answer : B

146) The basic elements of a multidimensional cube are

  1. Tables, Indexes, and schema
  2. Measures, dimensions, and schema
  3. Masters, Transactions and schema
  4. None
Answer : B

147) Slice and dice can be compared to

  1. To filter the data to analyze
  2. To sort the data to analyze
  3. To drilldown the data
  4. None
Answer : C

148) Cube processing does the following

  1. Copies the data from the underlying data sources into the cube objects
  2. Populates the data from the underlying data sources into the cube objects
  3. Both A & B
  4. None
Answer : C

149) Which of the following options is not present while processing a dimension?

  1. Process Update
  2. Process Index
  3. Process Data
  4. Process Structure
Answer : D

150) User is browsing the cube, however he/she could not find any data in it? Select the appropriate reason(s) from the following

  1. Cube is not processed
  2. SQL browser services is stopped
  3. Dimensions are not processed
  4. ALL of the above

Answer : D



151) Which of the following is not the type of fact table?

  1. Transactional
  2. Periodic snapshots
  3. Accumulating snapshots
  4. Factless Fact Table
Answer : D

152) Which type of processing group options is not exist for dimensions

  1. ByAttribute
  2. ByTable
  3. ByMembers
  4. ALL of the above
Answer : C

153) Which storage Mode is not used in the cube?

  1. MOLAP
  2. ROLAP
  3. HOLAP
  4. DOLAP
Answer : D

154) The model in which centralized Fact table connects to denormalized dimensions

  1. SnowFlake Schema
  2. Star Schema
  3. Star Flake Schema
  4. None of The Above
Answer : B

155) Which type of data storage architecture in  a Cube gives fastest performance?

  1. DOLAP
  2. ROLAP
  3. MOLAP
  4. None of The Above
Answer : C

156) Which is the most common type of Dimension?

  1. Customer
  2. Product
  3. Date
  4. All of The Above
Answer : C

157) Which of the following data storage architecture is not server based?

  1. DOLAP
  2. ROLAP
  3. MOLAP
  4. All of The Above
Answer : A

158) “MAX” is Semiadditive AggregateFunction of a measure in a cube

  1. False
  2. TRUE
  3. May be True
  4. May Be False
Answer : A

159) Security in Microsoft SQL Server Analysis Services is managed by using____________

  1. Roles
  2. Permissions
  3. Both Roles & permissions
  4. None of the above

Answer : C



160) You are developing a new measure group. During deployment you should make sure that you get the cube definition on the production server and not overwrite any existing partitions. Which of the following provides a way to achieve this?

  1. You should utilize the Deployment Wizard with the proper selection.
  2. You should utilize a SQL Server 2008 Integration Services (SSIS) package and configure the processing selection to Do Process.
  3. You should utilize the Analysis Services Destination component in Microsoft SQL Server Integration Services (SSIS).
  4. You should utilize the DBCC SHRINKFILE and configure the processing selection to DoProcess.
Answer : A

161) You have a requirement to update data in a partition on hourly basis and you should use the incremental processing method. which of the following commands would help you to achieve this?

  1. You should utilize ProcessAdd for Analysis (XMLA) command
  2. You should utilize default command for Analysis (XMLA)
  3. You should utilize ProcessNone for Analysis (XMLA) command
  4. You should utilize ProcessView for Analysis (XMLA) command
Answer : A

162) There is a dimension called DimProduct which includes a property called Price. You should make sure that aggregations would not link to the Price property. which of the following options would help you to achieve this?

  1. Configure the IsAggregatable attribute of the PriceTester attribute to True.
  2. Configure the AttributeHierarchyEnabled attribute of the PriceTester attribute to False.
  3. Configure the MembersWithData attribute of the PriceTester attribute to LeafDataHidden.
  4. Configure the MembersWithOutData attribute of the PriceTester attribute to LeafDataHidden.
Answer : B

163) Queries on a Cube don’t operate as fast as usual, you should confirm which queries run slowly and also change the storage setting of the cube to fix the issue. Which of the following will help you to do this?

  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.
Answer : B

164) Which one of the following is a correct calculated measure

  1. Create CurrentCube.[Measures].[Internet Gross Profit] AS [Measures].[Internet Sales Amount] – [Measures].[Internet Total Product Cost], Format_String =  “Currency”, Associated_Group = ‘Internet Sales’
  2. Create Member CurrentCube.[Measures].[Internet Gross Profit] AS [Measures].[Internet Sales Amount] – [Measures].[Internet Total Product Cost], Format_String =  “Currency”, Associated_Measure_Group = ‘Internet Sales’
  3. Create Member CurrentCube.[Measures].[Internet Gross Profit] AS [Measures].[Internet Sales Amount] – [Measures].[Internet Total Product Cost], Format =  “Currency”, Associated_Measure_Group = ‘Internet Sales’
  4. Create Member CurrentCube.[Measures].[Internet Gross Profit] AS [Measures].[Internet Sales Amount] – [Measures].[Internet Total Product Cost], Format_String =  “Currency”, Associated_Measure = ‘Internet Sales’
Answer : B

165) Which one of the following is not a semi-additive measures:

  1. SUM
  2. Min
  3. DistinctCount
  4. LastNonEmpty
Answer : A

166) You have a fact table that includes a column called ProductBalance which contains the current account balance. How do you make sure that the browser shows closing account balance.

  1. Configure the AggregationFunction property of ProductBalance to Total.
  2. Configure the AggregationFunction property of ProductBalance to ByProduct.
  3. Configure the AggregationFunction property of ProductBalance to LastNonEmpty.
  4. Configure the AggregationFunction property of ProductBalance to FirstEmpty.
Answer : C

167) The network administrator reports that there might be unauthorized users who are accessing Analysis Services. You have decided to monitor user connections to Analysis Services. Which tool you can use to monitor analysis services?

  1. SQL Server Management Studio
  2. SQL Server Profiler
  3. SQL Server Browser
  4. Business Intelligence Development Studio
Answer : B

168) Hierarchies and dimensions are stored in different dimension tables  in Star Schema

  1. False
  2. TRUE
  3. May be True
  4. May be false
Answer : A

169) Star Schema is preferred to use

  1. When dimension table is relatively big in size
  2. When dimension table contains less number of rows
  3. When the dimension has more calculations
  4. None of the above
Answer : B

170) Which of the following is correct about Snowflake Schema

  1. Normalized tables
  2. Not good to use for small data warehouses/datamarts
  3. Fewer joins
  4. Less complex queries and easy to understand
Answer : A

Related Articles

Advanced Manufacturing Process Quiz

Web analytics, Adobe Analytics, Google Analytics Quiz

SailPoint Quiz