databases Online Quiz - 2

The sys.sysobjects system view contains one row for each object in the database, making it ideal for counting objects. While sys.objects is preferred in modern SQL Server versions, sys.sysobjects is also available. The other options are either invalid procedures or incorrect dynamic management views.

Database recovery follows three steps: Analysis phase determines what needs recovery, Redo phase replays committed transactions, and Undo phase reverses uncommitted transactions. This is ARIES-style recovery. The order is Analysis→Redo→Undo. Option D incorrectly says 'Recovery' as a step rather than 'Undo'.

Executing SET STATISTICS IO ON in SQL Server displays information about disk activity, including logical reads, physical reads, and read-ahead reads generated by statements. The command SET STATISTICS TIME ON displays CPU and elapsed time, while SET STATISTICS I/O ON containing a slash is syntactically invalid.

A table can have only one clustered index because clustered indexes physically reorder the data storage order. The entire table's rows are stored in the order of the clustered index key. Multiple clustered indexes would require storing the same data in different physical orders simultaneously, which is impossible. Non-clustered indexes can be many.

The Join stage in DataStage requires the input datasets to be partitioned using the hash partitioning technique on the join key columns, and sorted, so that matching keys from different datasets reside in the same partition.

$PROJDEF is a special variable in DataStage that references project-specific parameter values. When using $PROJDEF for environment variables, the values are pulled from the project configuration, allowing jobs to move between environments (dev to test to production) without modifying job code.

In IBM InfoSphere DataStage, the Sequential File stage and Row Generator stage run in sequential mode by default. Conversely, File Set and Lookup File Set stages default to parallel mode for processing multi-file sets and lookups.

In a DataStage Transformer stage, the execution order is: 1) Stage Variables (computed first), 2) Derivations (use stage variables), 3) Constraints (use both stage variables and derivations for filtering). This order ensures that derived values and constraints can reference previously computed stage variables.

The DataStage Lookup stage supports two join types: Inner Join (only matching records) and Left Outer Join (all records from left stream, matching from right). Right Outer Join is not supported because the lookup is always from the input stream to the reference dataset.

To enable restartability in a DataStage sequence, you add checkpoints. Checkpoints save the state of the sequence after each job, allowing the sequence to resume from the last checkpoint after failure, rather than restarting from the beginning.

The correct execution order in a DataStage Transformer stage is: 1) Stage Variables are computed first, 2) Constraints are evaluated to determine if the row passes through, 3) Derivations are computed for output columns. This differs from question 165491 which asked about a different order.

To answer this question, the user needs to have knowledge about Cognos 8 BI and the techniques that can be used to improve the performance of PowerCubes.

Option A: Run the cube through the pcoptimizer utility that is shipped with Cognos 8 BI.
This option is correct. The pcoptimizer utility is used to optimize the performance of PowerCubes. It can be run on the cube to improve query response times and reduce the cube size.

Option B: Enable the Allow dynamic usage of dimension information governor in Framework Manager.
This option is incorrect. The Allow dynamic usage of dimension information governor controls how much of the dimension information is loaded into memory. It does not directly improve the performance of PowerCubes.

Option C: Edit the EnablePCOptimizer entry in the Cognos.ini file so that its value=1, and then rebuild the cube.
This option is incorrect. The EnablePCOptimizer entry in the Cognos.ini file is used to enable or disable the PowerCube optimizer. Enabling it alone will not improve the performance of PowerCubes. The cube needs to be rebuilt after making the change to the Cognos.ini file.

Option D: Ensure that the Pass-through SQL setting is enabled on all regular dimensions in the Framework.
This option is incorrect. The Pass-through SQL setting is used to pass SQL statements directly to the database. It is not directly related to improving the performance of PowerCubes.

Therefore, the correct answer is:

The Answer is: A. Run the cube through the pcoptimizer utility that is shipped with Cognos 8 BI.

To implement a time dimension, Framework Manager modelers import an existing time dimension table into the model. They then create relationships between this time dimension and fact query subjects to enable temporal analysis.

In Framework Manager's business view with star schema groupings, conformed dimensions are identified by using the same dimension name across multiple star schemas. This naming convention indicates that the dimension shares the same meaning and structure across different fact tables.

OLAP-style queries against relational databases (ROLAP) are enabled in Framework Manager by creating regular dimensions for descriptive attributes and measure dimensions for numeric facts, then setting their scope appropriately. This allows dimensional analysis directly on relational sources without pre-built cubes. Shortcuts and star schema groupings are different modeling techniques, and relationships between dimensions are fundamental modeling steps, not specific to enabling OLAP queries.

Cognos 8 supports multiple authentication providers simultaneously, not just one. Authentication requires connection to authentication providers, not specifically the Cognos namespace (which is for internal users/groups). Object security in Framework Manager is implemented by defining access for users, groups, and roles from authentication providers. The statement about groups and roles (not individual users) is incorrect because Framework Manager does support security at the individual user level.

CubeSwap.bat is the IBM Cognos utility that allows replacing a PowerCube with a new version without affecting users who are currently using reports. The swap is transparent to users. BMTScriptPlayer.exe is for batch testing, pcaccel.exe for cube accelerator (processing), and RSUpgrade.bat for Report Server upgrades, none of which handle live cube swapping.

A time dimension is critical for multi-fact queries that need to align facts across time periods. Without it, time-based rollups (aggregating by day, month, quarter, etc.) become difficult because Framework Manager cannot automatically generate the necessary time joins and period alignments. Virtual star schemas do not strictly require a time dimension for all multi-fact queries, and authors can still create multi-fact time-based queries using other techniques like unions, though it becomes more complex.

In Framework Manager, query items (individual columns/fields) are the objects at the finest granularity level where security can be applied using groups and roles from the Cognos namespace. Data sources, relationships, and parameter maps are project-level or structural objects that use different security mechanisms (connection permissions, model design, or project-level security).