70-463

Indexed views

Columnstore indexes are not allowed on ______.

Create sequence dbo.SeqcustomerDwKey as INT. Start WITH 1 INCREMENT BY 1;

Create a sequence object. Name it SeqCustomerDwKey. Starting with 1 and use increment of 1

Partition aligned

If your table is partitioned, the columnstore index must be ______.

Line of business applications

LOB

Additive measures

Measures can be summarized across all dimensions are called _____?

Not a good

NonClustered indexes on dimension attributes are___ practice.

Staging tables

Temporarily stores data before cleansing it or merging it with data from other sources.

Segments

The columnstore index is divided into units called _____?

Hash joins

To get this type of join, you do not index the foreign keys of a fact table.

Read-mostly workloads

Use columnstore indexes for _______.

Current row flag and surrogate key

What columns do you add to a table to support type 2 SCD changes?

Simple data movements

Where data is moved from the source to the destination "as-is" (unmodified)

Hash join, scan, filter

Which operators can benefit from batch processing?

Unicode, row, page

Which types of data compression are supported by SQL server?

One

You can have only ____ columnstore index(s) per table.

Computed column

A ____ column is a virtual column in a table. They can also help with performance.

Sequence

A ____ is a user-defined, table-independent (and therefore schema bound) object

Filtered index

A columnstore index can't be a _______.

Filegroups

A database can have multiple data files, grouped in multiple ______.

Inferred member

A row in a dimension added during fact table is called an _____. Also know as a late-arriving dimensions problem

Disk to memory

A segments is unit of transfer from ______.

Snowflake schema

A star schema with normalized dimensions is called?

Full recovery model

All transactions are fully logged, with all associated data.

Bitmap

Batch mode processing is particularly useful for data warehousing queries when combined with _____ filtered hash join in a star join pattern.

Conformed dimensions

Dimensions with connections to multiple fact tables are called shared or ______

Small dimensions

Do not use columnstore indexes for ______.

Update data frequently, partition switching doesn't fit your workflow

Don't use columnstore indexes when ______.

Vector

Each column within a batch is stored as a ____ in a separate memory area.

Minimum and maximum

Each segment has metadata that stores the _______ and ________ value of each column for that segment. This enables segment elimination in the storage engine. SQL Server loads only those segments requested by a query into memory

Semi-additive measures

For some measures, you can use some aggregate functions over all dimensions but time. Some examples include levels and balances.

Look up tables for each dimension

How can you spot natural hierarchies in a snowflake schema?

through share dimensions

How do you connect multiple star schemas in a DW?

Merge and nested

If sequential queries deal with smaller amounts of data then _____ joins benefit from indexes on fact table foreign keys.

Nonaligned indexes

Indexes that are partitioned differently than their base tables.

Normalization

Is a process in which you define entities in such a way that a single table represents exactly one entity

Bulk logged recovery model

Is an adjunct of the full recovery model that permits high-performance bulk copy operations

Data flow engine

It encapsulate a ________ that extracts, transforms, and loads data from data sources to data destination s.

Partition scheme

Maps partitions to filegroups. Performs physical mapping.

Third normal form

No functional dependency between non-key columns is considered what type of form.

Date

Over which dimension can you not use the SUM aggregate function for semi-additive measures?

Buffers

Rows are grouped into _____, and _____ are used to move rows through a data pipeline.

Simple recovery model

SQL Server automatically reclaims log space for committed transactions.

Batch mode processing

SQL Server processes data in batches rather than processing one row at a time.

Uniquifier

SQL server make it unique by adding a 4-byte sequential number called a _____ to duplicate keys.

In-memory, buffer-oriented

The data flow engine uses an ______, _____ architecture to efficiently manage different kinds of datasets

Sys.column_store_segments and Sys.column_store_dictionaries

There are two new catalog views you can use to gather information about columnstore indexes. Name those 2 catalog views.

Partition switching

This is a process that switches a block of data from one table or partition to another table or partition.

Partition elimination

This is a query optimizer process in which SQL server acesses is only those partitions needed to satisfy query filters.

Aligned index

This is an index belts on the same partition scheme that its based table.

Partition function

This is an object that maps rows to partitions by using values from specific columns. Performs logical mapping.

True

True or False; You cannot use row or page compression on a column store index.

False

True or false, Partitioning a large fact table will not make maintenance easier or perform better

False

True or false, as a general practice, you should not store every table with a clustered index, because storing a table as a B-tree does not have advantages

True

True or false, opt for an integer autonumbering surrogate key as the clustered primary key for all DW tables, unless there is a really strong reason to decide otherwise.

True

True or false, you can create an intermediate dimension between two fact tables that supports SSAS many-to-many relationship from an existing fact table, and not directly from a table from the source transactional system.

True

True or false; at SSIS run time, the data flow builds an execution plan from the data flow, and the data flow engine executes the plan.

Data is extracted, transformed, loaded

What three distinct elements constitutes a complex data movement?

Complex data movement

What type of SSIS movement is used when the data needs to be transformed before it can be stored, and were additional programmatic logic is required to accommodate the merging of the new and/or modified data, arriving from the source, with existing data, already present at the destination

Identity property and sequence object

Which database objects and object properties can you use for auto numbering?

Measure

Which kind of a column is not a part of a dimension?

Member property

Which kind of a column is not part of a fact table?

Price and DiscountPct

Which measures would you expect to be non-additive?

To speed up queries that aggregate data and that involve multiple joins

Why would you use indexed views?

Select APP_NAME, DATABASE_PRINCIPLE_ID(), USER_NAME(), SUSER_ID(), SUSER_SID(), SUSER_SNAME(), CONNECTIONPROPERTY('net_transport'), CONNECTIONPROPERTY('client_net_address'), CURRENT_TIMESTAMP, @@ROWCOUNT; GO

Write an select statement for data lineage for these columns. ApplicationName, databasePrincipleId, databasePrincipleName, ServerPrincipleId, ServerPrincipleSID, ServerPrincipleName, TransportProtocol, ClientNetAddress, CurrentDateTime, RowsProcessedByLastCmand

Snowflake schema

You are creating a quick POC project. Which schema is most suitable for this kind of project?

Foreign Key

You can remove ____ constraints to achieve better load performance.

Bitmap

____ filtered hash joins outperform other types of joins for parallel queries with large scans.

Row compression

____ reduces metadata overhead by storing fixed data type columns in a variable-length format

Page compression

_____ Includes row compression, but also adds prefix and dictionary compressions.

LastNonEmpty

_____ aggregate function, which properly use is the SUM aggregate function across all dimensions but time and defines the last known value as the aggregate over time.

ADO Connection manager

_____ enables connections to activeX data objects and is provided mainly for backwards compatibility. Consider using an OLE DB or an ODBC connection manager instead

Unicode compression

_____ is applied automatically when you apply either row or page compression.

Discretizing

_____ means grouping or binding values to a few discrete groups.

Dimensional model

______ actually consist of both star and snowflake schemas

ADO.NET Connection manager

______ enables connections to data stores using a Microsoft.NET provider. Is compatible with SQL Server

Prefix compression

______ stores repeated prefixes of value from a single column in a special compression information (CI) structure that immediately follows the page header,replacing the repeated prefix values with reference to the corresponding prefix.

Private dimensions

_______ are dimensions that pertain to only a single star schema

Covered

_______ are queries that read all data from one or more non-clustered indexes, without going to the base table. This means that there are fewer reads and less disk IO.