Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
74 Cards in this Set
- Front
- Back
|
What does a partition define?
|
The slice of the fact table data that is loaded into a measure group
|
|
|
What are the two reasons for partitioning?
|
Improved performance (allows processing only relevant year for example or allows parallel processing), improve manageability (delete one partition while leaving cube intact
process current partition more frequently than historical) |
|
|
What are guidelines for partition sizes?
|
At least 4000 rows
lest than 20 million rows less than 250 MB partitions |
|
|
How should you format the primary key for the time dimension?
|
Use the date format YYYYMMDD
|
|
|
If you want to bind partitions to seperate fact tables, what is required?
|
The tables need to have the same schema
|
|
|
What is table binding?
|
The default that says that a new measure group has a single partition that is bound to the entire table
the alternative is query binding |
|
|
What is query binding?
|
The option to narrow the partitoin slice of the default partition to leave space for the new partition
|
|
|
What are the three posible logical layers of a cube?
|
Metadata (essential)
data (essential) aggregations |
|
|
Where is cube metadata stored?
|
Always on the SSAS server
|
|
|
What does cube metadata do?
|
Makes the cube appear as a logical entity to end users
|
|
|
What are the three storage modes supported by SSAS?
|
OLAP (MOLAP, Default)
Relational OLAP (ROLAP) Hybrid OLAP (HOLAP) |
|
|
Sort MOLAP, ROLAP, and HOLAP by query time:
|
MOLAP (fast)
HOLAP (Medium) ROLAP (slow) |
|
|
Sort Molap, ROLAP, and HOLAP by latency:
|
ROLAP (low)
HOLAP (Medium) MOLAP (high) |
|
|
Sort MOLAP, ROLAP, and HOLAP by processing time:
|
MOLAP/HOLAP (fast)
ROLAP (Slow) |
|
|
sort MOLAP, ROLAP, and HOLAP by storage size:
|
HOLAP (small)
MOLAP(medium) ROLAP(large) |
|
|
How is data and aggregations stored with MOLAP?
|
data and aggregations are stored in file-based multi-dimensional structures created and managed by the SSAS server
|
|
|
With MOLAP, what must happen before data is available?
|
The partition must be processed, bu tthe admin can enable proactive caching to implement real-time data refreshing
|
|
|
How does MOLAP compare in storage size to the nonindexed relational data?
|
20 -25 percent the size
|
|
|
How is data and aggregations stored with ROLAP?
|
data and cube aggregations remain in the relational databse, so the SSAS server creates additional relational tables to hold cube aggregations
|
|
|
When is ROLAP the most practical?
|
If you need a low-latency partition and you cannot use proactive caching
|
|
|
What kind of notificaitons can you use with proactive caching?
|
Proactive caching supports SQL Server, client-initiated, and scheduled polling notification options
|
|
|
How is data and aggregations stored with HOLAP?
|
Data is stored in the relational store, but the aggregations are stored on the SSAS server
|
|
|
When does HOLAP perform as well as MOLAP?
|
If the query can be satisfied entirely by existing aggregations
|
|
|
When is HOLAP most efficient?
|
Large historical partitions that are queried infrequently
|
|
|
What are two places you cand configure the storage mode?
|
Properties of Partitions or properties for measure groups. Partition settings overrides measure group settings
|
|
|
What can SSAS cache in MOLAP and HOLAP modes?
|
aggregations (both modes)
data (MOLAP only) |
|
|
When is proactive caching most useful?
|
When the relational databse is transaction oriend, data changes randomly, and you need minimum latency
|
|
|
When is explicit cube processings most useful?
|
When you use ETL process to load data or other times when data changes are predictable
|
|
|
What changes are happening to the rlational data during stead state or steady mode?
|
No changes are happening to the relational data. The server listens for data change notificaito events
|
|
|
What are three kinds of data change notification events?
|
SQL Server trace events that the relational engine raises
Client Initiated by the client sending a NotifyTableChange XML for Analysis command (XMLA) to SSAS Scheduled Polling |
|
|
What two stopwatches are started when a notification event reaches the SSAS server?
|
Silence interval stopwatch (which waits until the databse is quiet before rebuilding the cache in case a batch is coming in)
Latency stopwatch which triggers a rebuild of the MOLAP cache after a set amount of time |
|
|
What does SSAS do if it gets a query while the new version of the MOLAP cache is being built?
|
It satisfies the query from the ROLAP database
|
|
|
What are the seven predfined partition storage settings?
|
Real-time ROLAP
Real0time HOLAP Low-latency MOLAP Medium0latency MOLAP automatic MOLAP Sheduled MOLAP MOLAP |
|
|
How long does it take for the cache to expire in Low-, and Medium- latency MOLAP?
|
Expires in 30 minutes (medium latency MOLAP)
4 hours (medium-latency MOLAP) |
|
|
What is the default silence interval with Automatic MOLAP, and how long before it starts processing the cache without silence?
|
10 second silence interval, 10 minutes without a period of silence
|
|
|
What are the three ways the server can derive aggregated values?
|
Retrieve internal cache
precalculated aggregations stored in the cube get data from partition and perform aggregate on the fly |
|
|
What should you do if you are sure an attribute does not need to be aggregated if you are going to use the aggregation design wizard?
|
Set the AggregationUsage attribute propertie to "None"
|
|
|
What are the differences between Full, Unrestricted, and Default AggregationUsage settings?
|
Full makes aggregation at this level or lower required
unrestricted lets the Aggregation design wizard decide default puts some into Unrestricted and others into None depending on rules |
|
|
What should you update before running the Aggregation Design Wizard?
|
EstimatedCount and EstimatedRows
|
|
|
What is a generally adequate range of performance gains from Aggregations for most cubes?
|
20-30%
|
|
|
What are the two way to copy an Aggregation design?
|
In SSMS right-click a partition and click "Copy Aggregation Design"
Before the Partition Wizard completes select the Copy Aggregation option |
|
|
What are the six options for deployment?
|
BIDS
Deployment Wizard XMLA script Synchronize Database Wizard Backup and Restore Analysis Managament Objects (AMO) |
|
|
When would you use the BIDS deployment option?
|
When you deploy the latest changes to your local server for testing and you don't need to retain the existing management settings such as roles and partitions
|
|
|
When would you use the Deployment Wizard deployment option?
|
Deploying to a test or production environment when you need more granular control
|
|
|
When would you use the XMLA Script deployment option?
|
Scheduling a deloyment task
|
|
|
When would you use the Synchronize Database Wizard deployment option?
|
Synchronizing two cubes, such as a staging cube and a production cube
|
|
|
When would you use the Backup and Restore deployment option?
|
Moving a cube from one server to another
|
|
|
When would you use the Analysis Management Objects (AMO) deployment option?
|
Handling deployment programmatically
|
|
|
What are the two design options that are supported by BIDS for working with SSAS databases?
|
Connected mode (directly conntected to SSAS so changes are immediately applied)
Project Mode (default and recommended option, allows mutliple team members to check out files without overwriting each other) |
|
|
What does the default setting in the Deployment Options Processing options do?
|
Aplies the minimum processing tasks to bring the cube to a ready state
|
|
|
Once you tell BIDS to deploy, what four steps does it go through?
|
1. Builds the projects to verify all objects are defined correctly
Aborts deployment and shows errors if it finds inconsistencies 3. Builds deployment script named <project name>.asdatabase and saves it to the project bin folder 4. Sends deployment script to the server to synchronzize the server database with the local project |
|
|
Why would you use the deployment wizard over BIDS?
|
If you need more control or incremental deployment (e.g. you want to retain partitions and roles)
|
|
|
What are the four BIDS Build Files?
|
1. Projectname.asdatabase
2. Projectname.configsetting 3. Projectname.deploymentoptions 4. Projectname.deloymenttargets |
|
|
What two deployment modes does the deployment wizard support?
|
Interactive and command-line
|
|
|
What do the /a /s and /o switches on the command-line mode of the Deployment Wizard do?
|
/a makes changes to configuration files that the wizard usses as a starting point next time
/s uns in silent mode /o automates script generation |
|
|
What two sections does the deployment script consist of?
|
Alter
Process |
|
|
How would you automate the XMLA deployment script gneration?
|
Use the /o switch on the Deployment Wizard
|
|
|
What are your three options for executing a deployment script?
|
1. Mannually by opening the script in SSMS, connecting to the target server, and hitting "execute"
Scheduled with the SQL Server Agent In an SSIS using the Analysis Services Execute DDL task |
|
|
What is the output of the Synchronize Database Wizard?
|
A single XMLA command named Synchronize
|
|
|
What permissions does the SSAS Server need to run "synchronize"?
|
Administrative rights to the source database
|
|
|
Suppose you wanted to deploy the latest changes to a farm of production servers without reprocessing the SSAS database on each produciton server. How would you do it?
|
Process the cube on the staging server and then synchronize each productiong server user the Synchronize Database Wizard
|
|
|
What are the 7 objects that you can process in SSAS?
|
Database
cube measure group partition dimension mining structure mining model |
|
|
What are the three objects that you can process in SSAS that store data?
|
dimensions
partitions mining structures |
|
|
What are the eight processing options for OLAP objects?
|
Default
Full Update Add Data Index Unprocess Structure |
|
|
What is the minimum processing option you need if you add a new attribute to a dimension?
|
Process full
|
|
|
What button in the Process dialog box reveals affected dependencies?
|
Impact analysis
|
|
|
What will SSAS do if it encouters a null value in a key column when processing dimension data?
|
It converts the value to zero for numeric columns or to an empy string for string columns if "unknown member" is enabled.
|
|
|
What two settings in the Storage Options dialog box should you change if query performance is your highest priority and you do not want to allow proactive caching to revert to OLAP at all?
|
Latency: Not Enabled
Bring Online Immediately:Unchecked (this will enable the queries to hit the old MOLAP cache while the new one is being built) |
|
|
What three notification options does Proactive Caching support?
|
SQL Server Notfications
Client-initiated notifications Scheduled polling notifications |
|
|
What are the advanatages and disadvantages of SQL Server Notifications?
|
A:Easy to set up
D:Requires SSAS to connect with Admin rights, on guaranteed event delivery because there is no event queuing Always results in Full Process for partitions and process update for dimensions |
|
|
What does scheduled polling assume?
|
The relational table has a column that indicates an update event
|
|
|
What are the requirements for a polling query?
|
The polling query needs to be a Singleton query, meaning the query must retun only one row (e.g. SELECT MAX(ModifiedDate) AS ModifiedDate FROM FactInternetSales)
|
|
|
When might you build the UDM directly on top of the operational data source?
|
If the database schema is not that complex and the business requirements call for low latency
|
|
|
What would you use to automate UDM processing during ETL?
|
An SSIS Analysis Services Processing Task
|
