| |
|
|
|
Da Notes -
70-019 - M
Personal notes on the MCDBA tests. These notes are from the various resources I used to pass the MCDBA exams including the MCDBA sample exams from Transcender. |
Passing Score: 620, Questions: 50, Time: 150 Min.
|
Da Notes Index
|
|
|
Maintaining a Database and VLDB
|
· Monitor and optimize the amount of space in the database.
· Perform backup procedures, restore procedures, and roll-off procedures on the data warehouse.
· Develop archiving procedures.
· Develop methods for refreshing data.
· Perform disaster recovery procedures on the database.
· Maintain database indexing.
· Verify database consistency.
· Monitor and optimize query performance.
· Automate maintenance tasks by using alerts and agents.
· Schedule DTS events.
· Schedule backup events.
· Schedule replication events.
|
MDStore
|
The MDStore interface and the MDStores collections establish and maintain the hierarchy that defines the structure of OLAP data
The MDStore interface is implemented with DSO that contains multidimensional data. The MDStores collection of a server object contains database objects. Database objects contain cube objects. Cubes contain partitions, and partitions contain aggregations.
|
MDX
|
· MDX - multidimensional expressions for manipulating data, supporting MDX functions in the definitions of calculated members, and in a full language implementation for building local cubes and querying cube data using PivotTableR Service with OLE DB and Microsoft ActiveXR Data Objects (ADO).
· The principal difference between SQL and MDX is the ability of MDX to reference multiple dimensions.
· OLAP Services recognize SQL and MDX dialect.
· Cube editor's Calculated Member Builder can help developing MDX.
· MDX extensions are required for creating a local cube: CREATE CUBE and INSERT INTO.
· Your cube contains a Geography dimension that has the levels Region and City. The regions are Eastern, Central, and Western. Each region contains several cities. How can you specify the names of the regions on an axis in your MDX statement?
· You can explicitly list each region on an axis, or you can use the Children function as follows: {[Geography].[Eastern], [Geography].[Central], [Geography].[Western]} or [Geography].Children
· List all of the regions and cities on the same axis - Use the members function to build a list of all regions and cities: [Geography].Members
· Create tuples that combine all cities in the Eastern region and the years 1997 and 1998 on the COLUMNS axis. How can you do this without explicitly listing each combination? Use the Crossjoin function as follows: Crossjoin({[1997],[1998]}, {Descendants([Region].[Eastern], [Cities])})
· Cube contains two measures, ListPrice and Cost. You want to include a calculated value, which is not a measure in the cube, in a report. How can you do this? Create a calculated member called Profit by subtracting Cost from ListPrice. Use the following calculated member on one of the axes in the MDX query: WITH MEMBER [Measures].[ Profit] AS ([Measures].[ListPrice] - [Measures].[Cost])
· Describe the steps you should follow to create a local cube containing information for cost by region for each quarter in 1997. The default measure for the source cube is [Total Sales]. First create an MDX query that displays [Geography].children on the COLUMNS axis and{Q1:Q4} on the ROWS axis. Additionally, the query should specify Measures.[Cost] in the WHERE clause. Write a CREATE CUBE statement based on the results of the MDX query. Write an INSERT INTO statement to populate the local cube. Include the CREATE CUBE and INSERT INTO statements in a connection string when opening a connection to the database in a custom application.
· Users want to build new OLAP cubes to address a particular business scenario. A single user will use these new cubes. How can you provide this functionality? Use MDX CREATE CUBE and INSERT INTO statements to create and populate a local cube for offline analysis.
· Write a query that reads the quarterly unit sales for each store in the state of Washington.
· SELECT Descendants([Store].[WA], [Store Name]) ON COLUMNS, [1997].Children ON ROWS FROM Sales WHERE (Measures.[Unit Sales])
· Return the first cell in a cellset - Cellset.Item(0) or Cellset.Item(0, 0)
· Name the Cellset object property that contains the slicer dimensions - FilterAxis
|
Microsoft Repository
|
A repository for each OLAP server is created to store metadata for multidimensional objects such as cubes, dimensions, and so on . This is a Microsoft Access (.mdb) database at \Program Files\OLAP Services\Bin\msmdrep.mdb.
Use the Migrate Repository wizard to migrate the Access database to a SQL Server (.mdf) database. The process cannot be undone. Maximum database integrity. Will minimize administrative overhead - use backup and restore methods of SQL Server.
DTS packages can be stored in Microsoft Repository. If stored, the metadata for the package is available to repository tools and data lineage can be tracked for the package.
|
MOLAP (multidimensional)
|
fastest query response times
multidimensional structure to contain aggregations and a copy of the base data
more appropriate for cubes frequently used, and for rapid query response
Custom app steps needed to create a MOLAP cube
Create a new cube connection object
Create connection string variables
MSOLEDB must be specified as the provider
Specify full path to the cube data source
Specify the sql server data warehouse as the data source name
Create cube statement to define dimensions and measures
INSERT INTO statement with a SELECT clause
List all dimension levels and measures in the INSERT INTO
List column names in the SELECT clause
Use OPEN method to populate the cube
ROLAP: specify OPTIONS_DEFER_DATA
|
MSMDREP.MDB
|
A repository for each OLAP server is created to store metadata for multidimensional objects such as cubes, dimensions, and so on . This is a Microsoft Access (.mdb) database at \Program
Files\OLAP Services\Bin\msmdrep.mdb.
|
MSMDSRV.EXE
|
SQL Server OLAP service program
|
MSOLAP.DLL
|
Pivot Table Service DLL
|
|
|
|
