Database Reference
In-Depth Information
■
The metadata database may seem like overkill at first, but as time goes on, you will appreciate it more
and more. Randal and his team once spent three months reconstructing which solution objects were still in use so
that they could be moved to a more powerful sQl server. The new server was going to increase performance, but to
optimize the database, the client wanted only the objects that were necessary for the current Bi solution. This made
sense, because the tables were huge! But because no one had ever recorded which table, view, or stored procedure
was needed by which solution objects, it was an expensive nightmare for the customer! The sample we are using is
a much simplified version of that database.
Note
We have provided the code that creates this database in Listing 18-3. Of course, we also included the
script le as part of the downloadable content for this topic, which you will nd in the
C:\_BookFiles\
Chapter18Files\ListingCode
folder.
Listing 18-3.
Creating a Metadata Database
USE Master
GO
-- Create or replace the database as needed
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'PubsBISolutionsMetaData')
BEGIN
-- Close connections to the DB
ALTER DATABASE [PubsBISolutionsMetaData] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- and drop the DB
DROP DATABASE [PubsBISolutionsMetaData]
END
Go
-- Now re-make the DB
CREATE DATABASE [PubsBISolutionsMetaData]
GO
USE [PubsBISolutionsMetaData]
GO
-- And add the core tables --
CREATE TABLE [dbo].[ObjectTypes](
[ObjectTypeId] [int] NOT NULL,
[ObjectTypeName] [nvarchar](1000) NULL,
[ObjectDescription] [nvarchar](4000) NULL,
CONSTRAINT [PK_ObjectTypes] PRIMARY KEY CLUSTERED
( [ObjectTypeId] ASC )
)
GO
CREATE TABLE [dbo].[DWObjects](
[DWObjectId] [int] NOT NULL,
[DWObjectName] [nvarchar](100) NULL,
[ObjectTypeId] [int] NULL,
[CurrentlyUsed] [bit] NULL,
