0

I have following tables in my database (this is only a single part).

  1. C_location table - hold information about locations (about 500 row)
  2. C_ProjectStatus table - hold information about ProjectStatus (about 5 rows)
  3. Project table - hold information about projects (about 1 mln rows)
  4. ProjectLocation - the list of location that the project is implemented (about 10mln rows, each project has about 10 locations)
  5. ProjectLocationParticipant - hold the information about the Participants in each location (about 5 participant for each location about 50 mln rows).

In general this is not the whole DB, I have also other "one to many" relations for Project, ProjectLocaion, etc. For example each Project has many implementers (ProjectImplementers table with FK to project) and ProjectImplementer can have its own "one to many" relation table with FK to ProjectImplementer table. So I have 3 kind of tables

  1. Main entity or root - the Project table
  2. lookup table - C_ProjectStatus and C_Location
  3. Sub-entity table - All the child tables for project table, like ProjectLocation, ProjectLocationParticipant, etc.

This is my database structure.

CREATE TABLE C_Location (
LocationID INT PRIMARY KEY CLUSTERED IDENTITY, 
Name NVARCHAR(50)
)
GO
CREATE TABLE C_ProjectStatus (
ProjectStatusID INT IDENTITY PRIMARY KEY, 
Name NVARCHAR(50)
)
CREATE TABLE Project (
ProjectID INT IDENTITY PRIMARY KEY, 
Title NVARCHAR(50), 
StartDate DATETIME, 
ProjectStatusID INT FOREIGN KEY REFERENCES dbo.C_ProjectStatus(ProjectStatusID)
)
GO
CREATE TABLE ProjectLocation (
PojectLocationID INT IDENTITY PRIMARY KEY, 
ProjectID INT FOREIGN KEY REFERENCES dbo.Project(ProjectID),
LocationID INT FOREIGN KEY REFERENCES dbo.C_Location(LocationID)
)
GO 
CREATE TABLE PorjectLocationParticipant (
ProjectLocationParticipantID INT PRIMARY KEY IDENTITY, 
ProjectLocationID INT FOREIGN KEY REFERENCES dbo.ProjectLocation(PojectLocationID), 
ParticipantFirstName NVARCHAR(50), 
ParticipantLastName NVARCHAR(50) 
)

I am very new in data warehouse so I need help to convert this structure to star schema.

Artashes Khachatryan
  • 387
  • 2
  • 10
  • It's always hard to recommend a structure or approach to anyone without an in-depth understanding of what they are trying to achieve and what they will use the database for. Fortunately, there are lots of helpful reference books out there. I'd recommend this [one - The Datawarehouse Toolkit](https://www.amazon.co.uk/Data-Warehouse-ToolKit-Third-Dimensional/dp/1118530802). The first few chapters contain everything you need to get started on your first dw project. – David Rushton Nov 17 '17 at 09:30

1 Answers1

0

create a table with all measures-called as fact table. In your case it would be all the primary keys of the existing tables into one table and establish foreign key relationships with them. The existing tables would be your dimensions. If projectlocation and project table form a parent child relationship and its the norm through out, by leaving the tables as they are now with an addition of fact table creates a snowflake schema. If you are looking only for a star schema, combine project and project location tables into one table(which becomes one dimension), and likewise for all tables. This way each dimension holds only data related to one entity of your data warehouse.

Dheerendra
  • 284
  • 1
  • 7