You get a bonus - 1 coin for daily activity.
Now you have 1 coin
Summary - The physical model of a data warehouse
Lecture
Это окончание невероятной информации про физическая модель хранилища данных.
...
determines the physical order of the corresponding rows in the table. The bottom (leaf) level of such an index stores the actual data rows of the table. A table or view can have only one clustered index at any given time.
If the CLUSTERED argument is not specified, a nonclustered index is created.
NONCLUSTERED. Creates an index that specifies a logical ordering for the table. The logical order of the rows in a nonclustered index does not affect their physical order.
index_name. The name of the index. Index names must be unique within a table or view, but do not have to be unique within the database.
Column. The column or columns the index is based on. The names of one or more columns for creating a composite index. Columns to be included in a composite index are specified in parentheses after the table_or_view_name argument, in sort order.
A single composite index key can include up to 16 columns. All columns of a composite index key must belong to the same table or the same view. The maximum total size of the values in a composite index is 900 bytes.
[ ASC | DESC ]. Determines the sort order of the values of the given index column: ascending or descending. The default value is ASC.
INCLUDE ( column [ ,... n ] ). Specifies non-key columns to be added to the leaf level of the nonclustered index. A nonclustered index can be either unique or non-unique.
Column names in the INCLUDE list must not repeat and cannot be used simultaneously as both key and non-key columns.
WHERE . Creates a filtered index by specifying which rows to include in the index. A filtered index must be a nonclustered index on the table. Filter statistics are also created for the data rows of the filtered index.
ON partition_scheme_name ( column_name ). Specifies the partition scheme that defines the filegroups corresponding to the partitions of the partitioned index. The partition scheme must already have been created in the database using the CREATE PARTITION SCHEME or ALTER PARTITION SCHEME statement. The column_name argument specifies the column by which the index will be partitioned. This column must match the data type, length, and precision of the argument of the partitioning function used by the partition_scheme_name scheme. The column_name argument can refer to columns that are not part of the index definition. Any column of the underlying table can be specified, except in the case of partitioning a UNIQUE index, where column_name must be chosen from the columns used in the unique key. This restriction allows the Database Engine component to verify the uniqueness of the key values within a single partition only.
ON filegroup_name. Creates the specified index in the given filegroup. If no location is specified and the table or view is not partitioned, the index uses the same filegroup as the underlying table or view. The filegroup must already exist.
ON "default". Creates the specified index in the default filegroup.
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" }]. Specifies the placement of FILESTREAM data for the table when creating a clustered index. The FILESTREAM_ON clause allows moving FILESTREAM data to another FILESTREAM filegroup or partition scheme.
The full or partial name of the object being indexed.
database_name. The name of the database.
schema_name. The name of the schema to which the table or view belongs.
table_or_view_name. The name of the table or view being indexed.
::= Specifies the options to be used when creating the index.
PAD_INDEX = { ON | OFF }. Determines index padding. The default value is OFF.
FILLFACTOR = fillfactor. Specifies a percentage that indicates how full the Database Engine component should make the leaf level of each index page during index creation or rebuild. fillfactor must be an integer between 1 and 100. The default value is 0. If fillfactor is 100 or 0, the Database Engine component creates indexes with fully filled leaf-level pages.
SORT_IN_TEMPDB = { ON | OFF }. Specifies whether to store intermediate sort results in the tempdb database. The default value is OFF.
IGNORE_DUP_KEY = { ON | OFF }. Determines the response to an error that occurs when an insert operation attempts to insert duplicate key values into a unique index.
STATISTICS_NORECOMPUTE = { ON | OFF }. Specifies whether distribution statistics are recalculated. The default value is OFF.
DROP_EXISTING = { ON | OFF }. Specifies that the named existing clustered or nonclustered index is dropped and rebuilt. The default value is OFF.
ONLINE = { ON | OFF }. Determines whether the underlying tables and associated indexes remain available for queries and data modification during index operations. The default value is OFF.
ALLOW_ROW_LOCKS = { ON | OFF }. Specifies whether row locking is allowed. The default value is ON.
ALLOW_PAGE_LOCKS = { ON | OFF }. Specifies whether page locking is allowed. The default value is ON.
MAXDOP = max_degree_of_parallelism. Overrides the configured maximum degree of parallelism setting for the duration of the index operation. MAXDOP can be used to limit the number of processors used to execute a plan in parallel. The maximum number of processors is 64.
DATA_COMPRESSION. Specifies the data compression mode for the given index, partition number, or range of partitions. Available options: NONE — the index or specified partitions are not compressed; ROW — row compression is applied to the index or specified partitions; PAGE — page compression is applied to the index or specified partitions.
ON PARTITIONS ( { | } [ , ...n ] ). Specifies the partitions to which the DATA_COMPRESSION setting applies. If the index is not partitioned, the ON PARTITIONS argument will produce an error. If the ON PARTITIONS clause is not specified, then DATA_COMPRESSION applies to all partitions of the partitioned index.
The CREATE INDEX clause specifies the name of the index; the ON clause specifies the name of the table and the columns for and on which the index is built; the UNIQUE keyword indicates that the indexed column values must be unique for the table, i.e., duplicate values in the indexed column are excluded. The table must already have been created and must contain definitions of the indexed columns. The UNIQUE specification is optional, and you can also create non-unique indexes.
In the SQL dialect of the MS SQL Server family of DBMSs, indexes on columns with a primary key constraint and UNIQUE constraint are created automatically. Therefore, the data warehouse designer needs to create indexes only for those columns for which they consider an index appropriate.
In our case, candidate columns for creating additional indexes — although this could be debated — are "Product name" (Name) in the "Product" dimension table and "Salesperson last name" (Empl_LName) in the "Employees" (Employee) dimension table. Let's create these indexes as shown below.
CREATE UNIQUE CLUSTERED INDEX Idx1 ON Product(Name);
go
CREATE UNIQUE CLUSTERED INDEX Idx2 ON Employee (Empl_LName);
go
After completing the actions listed above, the task of creating the physical model can be considered complete, as a first approximation. Now the developed script can be run against the created database, and the data warehouse can be considered created.
Summary
In this lecture, we examined the principles of developing a physical model of a data warehouse. Creating a physical model of a data warehouse consists of modeling and creating objects for storing data in the database of a specific DBMS. This task comes down to modeling and creating the tables and objects in the database that will store information about the entities of the data warehouse's subject area. In solving this task, the designer maps the relations of the logical data model of the data warehouse onto database tables and indexes. A subset of SQL commands – the Data Definition Language (DDL) – is used to accomplish this task.
A data warehouse is created in a relational database. The physical model of a relational database is a representation of the database's relations and the connections between them, embodied in a sequence of SQL commands. Executing this sequence of commands creates the specific database and its objects.
First, the database tables are created. Recall that tables in relational DBMSs consist of one or more columns, or fields. Columns are named cells in a record that hold values. Columns are defined through a specification that establishes the column's format and its characteristics, set by means of constraints.
The general algorithm for building the physical model of a data warehouse includes the following steps.
Developing the physical model of the data warehouse:
defining the base database tables;
defining the columns in the tables;
defining the data types for the columns;
assigning primary keys to the tables;
setting NOT NULL constraints on column values;
creating relationships between tables.
Developing the data warehouse creation script:
forming CREATE TABLE commands for the data warehouse tables;
defining constraints on the columns of the data warehouse tables;
forming additional indexes with the CREATE INDEX command.
Comments