SQL Server script

You can use this script:

• To create the metadata tables by using the metadata part of the script directly.
• As an example for creating the data tables, bearing in mind related metadata settings.

/*
**          SQL Server
**
**  Create the basic OLAP metadata tables:
**    _AttributeFields
**    _CubeAccessControl
**    _Cubes
**    _CubesDimensions
**    _Dimensions
**    _DimensionLevelNames
**    _HierarchyLevelNames
**    _Jobs
**    _JobsParameters
**    _FactLoad
**    _FactLoadParameters
**    _Hierarchies
**    _HierarchyAttributes
**    _Parameters
**    _ScheduledJobs
**    _Sources
**    _Subsets
**    _ErrorText
**    _ErrorLogFactLoad
**    _ErrorLogElementLoad
**    _ErrorLogAttributeFill
**    _ErrorLogOther
**
**  Create the following views:
**    _ErrorLogOverview
**
*/

CREATE TABLE [dbo].[_AttributeFields](
    [DimensionName] [nvarchar](50) NOT NULL,
    [TableId] [int] NOT NULL,
    [RelationalField] [nvarchar](50) NULL,
    [FieldName] [nvarchar](50) NOT NULL,
    [FieldDescription] [nvarchar](150) NULL,
    [FieldType] [nchar](1) NULL,
    [FieldWidth] [int] NOT NULL,
    [FieldDecimalPlaces] [int] NULL,
    [FieldOrderPosition] [int] NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_CubeAccessControl](
    [CubeName] [nvarchar](50) NOT NULL,
    [AccessCubeName] [nvarchar](50) NOT NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_Cubes](
    [CubeName] [nvarchar](50) NOT NULL,
    [CubeDescription] [nvarchar](150) NOT NULL,
    [Param1] [nvarchar](150) NULL,
    [Param2] [nvarchar](250) NULL,
    [AccessControl] [bit] NOT NULL,
    [TransactionLog] [bit] NOT NULL,
    [CubeType] [nchar](1) NULL,
    [ExtendedProps] [ntext] NULL,
    [CubeRules] [ntext] NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_CubesDimensions](
    [CubeName] [nvarchar](50) NOT NULL,
    [DimensionName] [nvarchar](50) NOT NULL,
    [DimensionOrder] [int] NOT NULL,
    [MeasureDimension] [bit] NOT NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_Dimensions](
    [DimensionName] [nvarchar](50) NOT NULL,
    [DimensionDescription] [nvarchar](150) NOT NULL,
    [SourceId] [int] NULL,
    [Param1] [ntext] NULL,
    [Param2] [ntext]NULL,
    [DimensionType] [int] NULL,
    [DefaultElement] [nvarchar](71) NULL,
    [FlatView] [bit] NOT NULL,
    [InvertedHierarchy] [bit] NOT NULL,
    [AccessCube] [nvarchar](50) NULL,
    [LoadFlags] [bigint] NULL,
    [ExtendedProps] [ntext] NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_DimensionLevelNames](
    [DimensionName] [nvarchar](50) NOT NULL,
    [LevelNumber] [int] NOT NULL,
    [LevelName] [nvarchar](50) NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_HierarchyLevelNames](
       [DimensionName] [nvarchar](50) NOT NULL,
       [HierarchyName] [nvarchar](50) NOT NULL,
       [LevelNumber] [int] NOT NULL,
       [LevelName] [nvarchar](50) NULL,
       [ColumnName] [nvarchar](50) NULL,
       [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_Jobs](
    [JobId] [bigint] NOT NULL UNIQUE,
    [JobType] [int] NOT NULL,
    [JobObject] [nvarchar](71) NOT NULL,
    [JobParameter] [int] NULL,
    [JobGroup] [nvarchar](100) NULL,
    [Started] [datetime] NULL,
    [Completed] [datetime] NULL,
    [Status] [int] NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_JobsParameters](
    [JobParameter] [bigint] NOT NULL,
    [ParameterSet] [int] NOT NULL,
    [DimIdx] [int] NOT NULL,
    [ParameterValue] [ntext] NOT NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_FactLoad](
    [JobObject] [nvarchar](50) NOT NULL UNIQUE,
    [CubeId] [nvarchar](50) NOT NULL,
    [FactTable] [ntext] NULL,
    [FactChangesTable] [ntext] NULL,
    [FactLoadType] [int] NOT NULL,
    [ParameterId] [int] NOT NULL,
    [LastLoadedVersion] [int] NULL,
    [NewestVersionToLoad] [int] NULL,
    [SourceId] [int] NULL,
    [FactType] [int] NULL,
    [RelationalColumnName] [nvarchar](50) NULL,
    [LoadFlags] [bigint] NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_FactLoadParameters](
    [Id] [int] NOT NULL,
    [DimIdx] [int] NOT NULL,
    [ColumnName] [nvarchar](50) NULL,
    [TargetElement] [nvarchar](71) NULL,
    [TargetElementUnknown] [nvarchar](71) NULL,
    [TargetElementMissing] [nvarchar](71) NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_Hierarchies](
    [HierarchyName] [nvarchar](50) NOT NULL,
    [HierarchyDescription] [nvarchar](150) NOT NULL,
    [DimensionName] [nvarchar](50) NOT NULL,
    [SourceId] [int] NULL,
    [HierarchyType] [int] NOT NULL,
    [Param1] [ntext] NULL,
    [Param2] [ntext] NULL,
    [DefaultElement] [nvarchar](71) NULL,
    [ExtendedProps] [ntext] NULL,
    [Comment] [ntext] NULL	
)

CREATE TABLE [dbo].[_HierarchyAttributes](
       [DimensionName] [nvarchar](50) NOT NULL,
       [HierarchyName] [nvarchar](50) NOT NULL,
       [FieldName] [nvarchar](50) NOT NULL,
       [ElementColumnName] [nvarchar](50) NOT NULL,
       [AttributeColumnName] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[_Parameters](
    [Id] [bigint] NOT NULL,
    [Key] [nvarchar](50) NOT NULL,
    [DataType] [int] NOT NULL,
    [StringValue] [ntext] NULL,
    [IntegerValue] [int] NULL,
    [LinkId] [bigint] NULL,
    [Comment] [ntext] NULL
    CONSTRAINT ID_KEY_PK PRIMARY KEY ([Id], [Key])
)

CREATE TABLE [dbo].[_ScheduledJobs](
    [Id] [bigint] NOT NULL UNIQUE,
    [JobType] [int] NOT NULL,
    [JobObject] [nvarchar](71) NOT NULL,
    [JobParameter] [int] NULL,
    [Schedule] [ntext] NOT NULL,
    [Scheduled] [bit] NOT NULL,
    [Disabled] [bit] NOT NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_Sources](
    [SourceId] [int] NOT NULL,
    [SourceType] [int] NOT NULL,
    [ConnectionParameters] [ntext] NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_Subsets](
    [DimName] [nvarchar](50) NOT NULL,
    [SubsetName] [nvarchar](50) NOT NULL,
    [SubsetKind] [int] NOT NULL,
    [SubsetCategory] [int] NOT NULL,
    [SubsetTable] [ntext] NOT NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_ErrorText](
    [ErrorCode] [int] NOT NULL,
    [LanguageRegion] [nvarchar](15) NOT NULL,
    [Text] [ntext] NULL
)

CREATE TABLE [dbo].[_ErrorLogFactLoad](
    [JobId] [bigint] NOT NULL,
    [ErrorCode] [int] NOT NULL,
    [ErrorDimIdx] [int] NULL,
    [ErrorDimName] [nvarchar](50) NULL,
    [Element00] [nvarchar](256) NULL,
    [Element01] [nvarchar](256) NULL,
    [Element02] [nvarchar](256) NULL,
    [Element03] [nvarchar](256) NULL,
    [Element04] [nvarchar](256) NULL,
    [Element05] [nvarchar](256) NULL,
    [Element06] [nvarchar](256) NULL,
    [Element07] [nvarchar](256) NULL,
    [Element08] [nvarchar](256) NULL,
    [Element09] [nvarchar](256) NULL,
    [Element10] [nvarchar](256) NULL,
    [Element11] [nvarchar](256) NULL,
    [Element12] [nvarchar](256) NULL,
    [Element13] [nvarchar](256) NULL,
    [Element14] [nvarchar](256) NULL,
    [Element15] [nvarchar](256) NULL,
    [Element16] [nvarchar](256) NULL,
    [Element17] [nvarchar](256) NULL,
    [Element18] [nvarchar](256) NULL,
    [Element19] [nvarchar](256) NULL,
    [Element20] [nvarchar](256) NULL,
    [Element21] [nvarchar](256) NULL,
    [Element22] [nvarchar](256) NULL,
    [Element23] [nvarchar](256) NULL,
    [Element24] [nvarchar](256) NULL,
    [Element25] [nvarchar](256) NULL,
    [Element26] [nvarchar](256) NULL,
    [Element27] [nvarchar](256) NULL,
    [Element28] [nvarchar](256) NULL,
    [Element29] [nvarchar](256) NULL,
    [ElementCaption00] [nvarchar](256) NULL,
    [ElementCaption01] [nvarchar](256) NULL,
    [ElementCaption02] [nvarchar](256) NULL,
    [ElementCaption03] [nvarchar](256) NULL,
    [ElementCaption04] [nvarchar](256) NULL,
    [ElementCaption05] [nvarchar](256) NULL,
    [ElementCaption06] [nvarchar](256) NULL,
    [ElementCaption07] [nvarchar](256) NULL,
    [ElementCaption08] [nvarchar](256) NULL,
    [ElementCaption09] [nvarchar](256) NULL,
    [ElementCaption10] [nvarchar](256) NULL,
    [ElementCaption11] [nvarchar](256) NULL,
    [ElementCaption12] [nvarchar](256) NULL,
    [ElementCaption13] [nvarchar](256) NULL,
    [ElementCaption14] [nvarchar](256) NULL,
    [ElementCaption15] [nvarchar](256) NULL,
    [ElementCaption16] [nvarchar](256) NULL,
    [ElementCaption17] [nvarchar](256) NULL,
    [ElementCaption18] [nvarchar](256) NULL,
    [ElementCaption19] [nvarchar](256) NULL,
    [ElementCaption20] [nvarchar](256) NULL,
    [ElementCaption21] [nvarchar](256) NULL,
    [ElementCaption22] [nvarchar](256) NULL,
    [ElementCaption23] [nvarchar](256) NULL,
    [ElementCaption24] [nvarchar](256) NULL,
    [ElementCaption25] [nvarchar](256) NULL,
    [ElementCaption26] [nvarchar](256) NULL,
    [ElementCaption27] [nvarchar](256) NULL,
    [ElementCaption28] [nvarchar](256) NULL,
    [ElementCaption29] [nvarchar](256) NULL,
    [FactType] [int] NULL,
    [ValueNumeric] [real] NULL,
    [ValueString] [ntext] NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_ErrorLogElementLoad](
    [JobId] [bigint] NOT NULL,
    [ErrorCode] [int] NOT NULL,
    [Dimension] [nvarchar](256) NULL,
    [DimensionCaption] [nvarchar](256) NULL,
    [Hierarchy] [nvarchar](256) NULL,
    [HierarchyCaption] [nvarchar](256) NULL,
    [Element] [nvarchar](256) NULL,
    [ElementCaption] [nvarchar](256) NULL,
    [ElementType] [nvarchar](256) NULL,
    [ParentElement] [nvarchar](256) NULL,
    [ParentElementCaption] [nvarchar](256) NULL,
    [Weight] [real] NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_ErrorLogAttributeFill] (
    [JobId] [bigint] NOT NULL,
    [ErrorCode] [int] NOT NULL,
    [Dimension] [nvarchar](256) NULL,
    [DimensionCaption] [nvarchar](256) NULL,
    [Hierarchy] [nvarchar](256) NULL,
    [HierarchyCaption] [nvarchar](256) NULL,
    [Element] [nvarchar](256) NULL,
    [ElementCaption] [nvarchar](256) NULL,
    [TableId] [int] NULL,
    [AttributeFieldName] [nvarchar](256) NULL,
    [AttributeDataValue] [nvarchar](512) NULL,
    [AttributeFieldType] [nvarchar](16) NULL,
    [AttributeFieldLength] [int] NULL,
    [AttributeFieldDecimals] [int] NULL,
    [Comment] [ntext] NULL
)

CREATE TABLE [dbo].[_ErrorLogOther](    
    [JobId] [bigint] NOT NULL,
    [ErrorCode] [int] NOT NULL,
    [ObjectKind] [nvarchar](256) NULL,
    [ObjectName] [nvarchar](256) NULL,
    [ObjectCaption] [nvarchar](256) NULL,
    [ErrorText] [text] NULL
)

GO

CREATE VIEW [dbo].[_ErrorLogOverview] AS
SELECT [JobId], '_ErrorLogFactLoad' AS "Table", COUNT(*) AS "Row Count" FROM [_ErrorLogFactLoad]
    GROUP BY [JobId]
UNION
SELECT [JobId], '_ErrorLogElementLoad' AS "Table", COUNT(*) AS "Row Count" FROM [_ErrorLogElementLoad]
    GROUP BY [JobId]
UNION
SELECT [JobId], '_ErrorLogAttributeFill' AS "Table", COUNT(*) AS "Row Count" FROM [_ErrorLogAttributeFill]
    GROUP BY [JobId]
UNION
SELECT [JobId], '_ErrorLogOther' AS "Table", COUNT(*) AS "Row Count" FROM [_ErrorLogOther]
    GROUP BY [JobId]