ssas.IndexColumn_from_Relationship - V

type: V ( view ), modify_date: 2021-09-01 13:40:43

RepoObject_guid: 969CF1C3-0B08-EC11-8515-A81E8446D5B0

Description

Examples

Entity Diagram

entity-ssas.indexcolumn_from_relationship

Columns

Table 1. Columns of ssas.IndexColumn_from_Relationship - V
PK Column Name Data Type NULL? ID

nvarchar(128)

NOT NULL

int

NOT NULL

nvarchar(264)

NOT NULL

int

NOT NULL

bit

NOT NULL

bit

NOT NULL

uniqueidentifier

NOT NULL

uniqueidentifier

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(500)

NULL

Foreign Key Diagram

entity_1_1_fk-ssas.indexcolumn_from_relationship

References

Referencing Objects

Object Reference Diagram - 1 1

entity_1_1_objectref-ssas.indexcolumn_from_relationship

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-ssas.indexcolumn_from_relationship

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-ssas.indexcolumn_from_relationship

Column Reference Diagram

entity_1_1_colref-ssas.indexcolumn_from_relationship

Indexes

idx_IndexColumn_from_Relationship__1

idx_IndexColumn_from_Relationship__2

idx_IndexColumn_from_Relationship__3

Column Details

_

databasename

databasename

nvarchar(128)

NOT NULL

index_column_id

index_column_id

int

NOT NULL

index_name

index_name

nvarchar(264)

NOT NULL

is_descending_key

is_descending_key

int

NOT NULL

is_index_primary_key

is_index_primary_key

bit

NOT NULL

is_index_unique

is_index_unique

bit

NOT NULL

RepoObject_guid

RepoObject_guid

uniqueidentifier

NOT NULL

RepoObjectColumn_guid

RepoObjectColumn_guid

uniqueidentifier

NOT NULL

TableName

TableName

nvarchar(128)

NOT NULL

tables_columns_name

tables_columns_name

nvarchar(128)

NOT NULL

tables_columns_type

tables_columns_type

nvarchar(500)

NULL

sql_modules_definition

ssas.IndexColumn_from_Relationship - V script
/*
create index from each ssas column, which is used in any relationship

one index per column
*/
CREATE View ssas.IndexColumn_from_Relationship
As
Select
    index_name           = Concat (   'ix_'
                                    --, Row_Number () Over ( Partition By T2.databasename, T2.TableID Order By T2.ExplicitName )
                                    --, '_'
                                    , T2.tables_name
                                    , '_col_'
                                    , T2.tables_columns_name
                                  )
  , index_column_id      = 1 --one column per index => explicite value 1
  , T2.RepoObjectColumn_guid
  , is_descending_key    = 0
  , is_index_primary_key = IsNull ( T2.tables_columns_isKey, 0 )
  , is_index_unique      = IsNull ( T2.tables_columns_isUnique, 0 )
  , T2.databasename
  , T2.tables_columns_name
  , T2.tables_columns_type
  , T2.RepoObject_guid
  , TableName            = T2.tables_name
From
    ssas.model_json_311_tables_columns_T As T2
Where
    Exists
(
    Select
        1
    From
        ssas.model_json_32_relationships_T As T3
    Where
        T3.databasename                 = T2.databasename
        And T3.relationships_fromTable  = T2.tables_name
        And T3.relationships_fromColumn = T2.tables_columns_name
)
    Or Exists
(
    Select
        1
    From
        ssas.model_json_32_relationships_T As T3
    Where
        T3.databasename               = T2.databasename
        And T3.relationships_toTable  = T2.tables_name
        And T3.relationships_toColumn = T2.tables_columns_name
)