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.