f



Warehouse design question - Fact Table Primary Key

I think I know the answer to this question but, just in case: 

I have a dimensional model in a star schema with 5 dimension tables
and 1 fact table.  I understand (from reading Kimball) that the
primary key on my fact table should consist of a composite of the
primary keys on my dimension tables.  But, in my case, these 5
dimension table primary keys don't describe a unique row in my table.

What I originally did was add the extra 3 (!) columns that made up the
alternate primary key in my production table to the 5 dimension table
keys to get a primary key for the fact table.  I'm not so sure that a
table with a primary key of 8 columns is such a good thing though.

Am I breaking any rules that really matter (or any rules at all) if
keep the 5 Foreign Keys from fact to dimensions but make the primary
key on the fact table from the 4 columns of my production alternate
key?

(This is a warehouse used by Cognos.)
0
svigil
7/20/2004 10:37:10 PM
comp.databases.olap 2325 articles. 0 followers. Post Follow

1 Replies
257 Views

Similar Articles

[PageSpeed] 10

Sandra,

Be clear about the granularity required by the users of the data.
If there is a natural granularity that the current 5 dimension tables
don't expose & this is not a problem for end-users then fine.
There is no logical demand that the leaf level of the cube should be
the row level of the fact table (even if it is in most cases).
This is the purpose of drillthrough functionality.

Regards,

John Keeley

www.johnkeeley.com





svigil@coinstar.com (Sandra Vigil) wrote in message news:<5a364092.0407201437.64cccf62@posting.google.com>...
> I think I know the answer to this question but, just in case: 
> 
> I have a dimensional model in a star schema with 5 dimension tables
> and 1 fact table.  I understand (from reading Kimball) that the
> primary key on my fact table should consist of a composite of the
> primary keys on my dimension tables.  But, in my case, these 5
> dimension table primary keys don't describe a unique row in my table.
> 
> What I originally did was add the extra 3 (!) columns that made up the
> alternate primary key in my production table to the 5 dimension table
> keys to get a primary key for the fact table.  I'm not so sure that a
> table with a primary key of 8 columns is such a good thing though.
> 
> Am I breaking any rules that really matter (or any rules at all) if
> keep the 5 Foreign Keys from fact to dimensions but make the primary
> key on the fact table from the 4 columns of my production alternate
> key?
> 
> (This is a warehouse used by Cognos.)
0
duvinrouge
7/21/2004 9:00:41 AM
Reply:

Similar Artilces:

Database Design Problem with Hibernate Primary Key and Foreign Key
hi, I am a newbie to Hibernate. It seems to me that Hibernate does not allow you to declare both id and composite-id in .hbm.xml mapping. That was why i have a triple primary key in my ENROLLED table as following: CREATE TABLE ENROLLED ( SID INTEGER NOT NULL, CID INTEGER NOT NULL, ENROLL_SINCE DATETIME NOT NULL, EID INTEGER NOT NULL auto_increment, COURSE_ROLE_ID INTEGER DEFAULT 0, LAST_LESSON_ID INTEGER, COMPLETE_BY_DATE DATETIME, GRADE VARCHAR(10), STATUS_ID MEDIUMINT(9), IS_AVAILABLE CHAR(1) DEFAULT 'Y', CREATION_DATE DATETIME ...

Design Question: Primary Key
Just a quick question. I just inherited a database a week or so ago, and I noticed something in one of the main tables that caught my attention. The USERS table has the primary key as User_ID Varchar(50) I was always taught that the primary key shouldn't mean anything, and in this case..the User_ID is what the user types in when they register. What would you suggest? >> I was always taught that the primary key shouldn't mean anything.. << I am not sure why and how you were taught, but have you ever questioned why? -- -- Anith ( Please reply to newsgroups only ) No chance to ask why. Like I said....it was inherited....and no idea who the developer was. But...there must be the RIGHT way to do it. Should a Primary Key be a varchar and be something the user can enter? "Anith Sen" <anith@bizdatasolutions.com> wrote in message news:OkGnb.10803$FI2.4056@newsread1.news.atl.earthlink.net... > >> I was always taught that the primary key shouldn't mean anything.. << > > I am not sure why and how you were taught, but have you ever questioned why? > > -- > -- Anith > ( Please reply to newsgroups only ) > > On Wed, 29 Oct 2003 02:14:21 +0000, Member wrote: > Just a quick question. > > I just inherited a database a week or so ago, and I noticed something in > one of the main tables that caught my attention. > > The USERS t...

newbie question--1 table's columns to link to other tables' primary keys
I'm new to databases, so if my question doesn't make any sense, don't hold it against me. I have three tables--one stores user information (username, password, email, etc), another stores a list of songs--this table uses a 2-column primary key. One of the primary key columns is of DATETIME type and the other is a SMALLINT AUTO_INCREMENT type. My third table stores information about each listen--by that I mean a new record is made everytime a user listens to a song, and in that record, is the user and the song, so naturally there would only be two columns in this table, namely User ...

Designing a database within a database... design question storing data...
I have a system that basically stores a database within a database (I'm sure lots have you have done this before in some form or another). At the end of the day, I'm storing the actual data generically in a column of type nvarchar(4000), but I want to add support for unlimited text. I want to do this in a smart fashion. Right now I am leaning towards putting 2 nullable Value fields: ValueLong ntext nullable ValueShort nvarchar(4000) nullable and dynamically storing the info in one or the other depending on the size. ASP.NET does this exact very thing in it's Session State model;...

I am trying to set up a database using the database connectivity toolkit and 8.5. I have tables setup and each one has an autoincrementing primary ID key.
I have very little experience with databases, but I have to set one up. I am setting up a relational BD, I have several tables set up, each has a primary key set up as a ID, autoincrement. &nbsp; How can I not send this column data, and let it autoincrement like it should so that it is a unique entry. I keep on getting an error that says that the number of colums don't match. Is there any way to do this. &nbsp; I want the database to keep track of the line count for this, and increment when each new entry arrives.&nbsp; Does this make any sense? The insert data VI has a Colu...

BUG #1055: no keys in inherited table with primary key when inserting into inheriting table
The following bug has been logged online: Bug reference: 1055 Logged by: Agri Email address: agri@desnol.ru PostgreSQL version: 7.4 Operating system: PC-linux-gnu Description: no keys in inherited table with primary key when inserting into inheriting table Details: let me desribe a bug in the term of sql commands: create table first (id int primary key ); create table second (f2 int) inherits (first); create table third (ref_id int); alter table third add constraint third_ref_first foreign key (ref_id) references first; insert int...

Database design question
Hi, I have a question regarding best practices in database design. In a relational database, is it wise/necessary to sometimes create tables that are not related to other tables through a foreign Key relationship or does this always indicate some sort of underlying design flaw. Something that requires a re evaluation of the problem domain? The reason I ask is because in our application, the user can perform x number of high level operations (creating/updating projects, creating/ answering surveys etc. etc.). Different users can perform different operations and each operation can manipulate o...

Database/Table Design Question
Hi, Facts: I created a database to support an application that tracks events on different objects. The two main tables are tbl_Object and tbl_EventLog. Each table has unique ID and on the tbl_EventLog there is FK for a record in the tbl_Object. The events are inserted all the time for the same or different objects from the tbl_Object. There are about 600,000 objects in the tbl_Object and 1,500,000 (and growing) events in tbl_EventLog. Question: The user often wants to know what the last event was for a specific object. What is the best way of retrieving the last event? Should I simply do a...

Database Design Vendors and Items Question. Do I need a third table?
Hi, probably a simple question that I have for what I think is a simple database. I have a Vendors table and an Items table. There is only one items, but several Vendors may have that same item. I have to have a third table, correct, to connect them? I was thinking OrderDetails (or something like that) OrderDetailID ItemID VendorID ItemSerial (I thought about this because each vendor might have the same vendor, but different serial numbers, which I cannot put in the items table) Price Purchased (y/n field) Comments I can then select all items and the vendors or a vendor and all the item...

Design Question -> Copying a table in another database, the relinking it.
Hi all, I have a situation where I have a split database. At the back end, I need to - create a new table (I will call it newTable) with the exact fields, and relationships as another table (let's call it oldTable, and I need to copy everything EXCEPT the data). - Then I need to delete oldTable, and rename newTable 'oldTable' The problem is, that all of this happens at the back end, and I'm having some problems making this work. I've tried using CreateTableDef, and this creates the definitions...but it never manages to actually create a table (that I can see). I've also tried the following SQL, SELECT * INTO newTable FROM oldTable Where 0 = 1; The 0=1 will never occur, thus I will have a new table with the proper definitions, but no data...which is what I want. But this creates a table at the front end :( I need a table at the back end which is linked... Anyone have any ideas? Mark Copy oldTable. Paste. When you paste you will be given three options. One will allow you to copy the structure only. Easier though to open the oldTable, press Ctrl+a and then press delete. Voila! All the data is gone and you have a blank oldTable. mark_aok wrote: >Hi all, > >I have a situation where I have a split database. At the back end, I >need to >- create a new table (I will call it newTable) with the exact fields, >and relationships as another table (let's call it oldTable, and I need >to copy everything EXCEPT the data). &g...

Unknown primary keys and foreign keys ina database
I would like to connect to a database and determine, on the fly, what columns are the foreign and primary keys for each table.&nbsp; Is there any way to do this using the LabView Database connectivity toolkit or a SQL query? Thank you! Brad Found it, thanks!&nbsp; For future reference (for anyone who might search for this topic) Go to: Tools -&gt; Options -&gt; View and check "system" and "hidden" objects to explore the structure of these tables.&nbsp; It's not recommended to edit them directly for obvious reasons, but will at least give an idea...

Access Project
I have a created a SQL Database with a table called Tbl_Customer which includes lots of Rows of customer information. The primary Key is CustID which is an Identity (Auto Number) I want to be able to create a new table called Tbl_Address which has CustID as a foreign Key and I want to be able to add between 1 and 4 addresses (different types such as home, term time etc). Eventually I want to be able to create a form so you can view the customer details, and skip through the addresses in a sub form. My problem is that as soon as I set the relationship as 1 to many, I can no longer add any a...

Primary Key on a table is not coming over when I import table in SSIS
SQL 2005 SP2. When I try to import a table from one server to another using SSIS, the table imported on the new server does not have the primary key which exists on the source table. Help please. Thanks. Raziq. *** Sent via Developersdex http://www.developersdex.com *** There is a setting under the options to move it. Be sure all objects are checked within the options that you need. By default I dont not believe this is the case. i would bet any indexes and any foreign keys besides primary keys did not move over as well as this is also an option. Mike On Jul 21, 3:37=A0pm, Raziq Shekha...

How do I find the primary key of a table by querying the system tables?
Hi all, I've googled for this for a while but haven't found anything. Please could someone tell me how I can find iF a table has a primary key and what is the name of the constraint. I have this so far (I'm on ASE 11.0 for Linux btw): select i.name, o.name from sysindexes i, sysobjects o where i.id = o.id and o.type = 'U'; I think all my primary key indexes start PK_, but I'm not sure. I figure the primary key must be flagged somewhere, but I can't see anything obvious on the system tables. TIA Rich Hi Richard, Try the "status" co...

Two foreign keys pointing to the same primary key in other table
i came accross requirement of multiple foreign keys in a table referencing the same primary key another table. i created the table relations using the relations editor in access. even though i could have multiple foreign keys to a table access did allow the referential integrity. my database structure is table1 - students table table 2 - subjects table table 3 - subjects opted by students in the table 3 totally there are 6 subject columns and a student can opt for variable number of subjects. table structure students ID -> primary key, FirstName, LastName, ........ subjects ID->pri...

General Design Question SQL Tables or Local Tables
A general design question: Assuming I can figure out a way to link some local tables in an .MDB file to my Access2000 .ADP database (any help on this is appreciated as well), I'm wondering which of the following methods will yield faster performance over a slow WAN network: Method A: Creating tables on my SQL Server to store temporary records that are linked to records in permanent SQL Server tables. Method B: Linking tables in a seperate .MDB file on the client workstation that are linked to records in permanent SQL Server tables. My current .MDB database uses a dozen or so local table...

Primary Key built from other Primary Keys?
Hello comp.databases, Can a primary key of a table be built from primary keys of other tables? For example, I have a table called 'Match' and the way I'd like to construct it is that the primary key of this table is built from 'Home Team', 'Opposition', 'Location' (these are all entities with tables) and 'Date' (this is an attribute of the 'Match' table). For your information, the 'Home Team' table has 'Name' for it's primary key and 'Opposition' and 'Location' both have 'Name' and 'Z...

Relationships error, C# Visual Studio 2005 database bug?, "the columns in table XYZ do not match an existing primary key or UNIQUE constraint", copying columns
Problem: I replicated, using Frasier "Pro Visual C++/CLI" (2006), Chap 12, a database that has a relationship between two tables, called Content and Author, using a common column, called "AuthorID". I used the Server Explorer insider Visual Studio 2005 to do this. It worked. Now I tried the exact same thing from Visual Studio 2005 but this time using C# not C++ as my language of choice. The interface is slightly different, but I made sure everything was done as before. I used the Server Explorer 'GUI' to do this, clicking and following the 'wizards' as...

How can i copy a table to a new table with primary key remain
How can i copy a table to a new table(structure and data) with primary key remain i have try to use Select * into table from old_table However, i dont have any primary key which old_table have could anyone help me thx On 9 Mar 2006 18:29:53 -0800, realjacky@gmail.com wrote: From the database window choose the Tables list. Select your table. Ctrl+C Ctrl+V -Tom. >How can i copy a table to a new table(structure and data) with primary >key remain > >i have try to use Select * into table from old_table > >However, i dont have any primary key which old_table have > &g...

SQL
I want to create a table with member id(primary key for Students,faculty and staff [Tables]) and now i want to create issues[Tables] with foreign key as member id but in references i could not able to pass on reference as or condition for students, faculty and staff. Thank You, Chirag Chirag wrote: > I want to create a table with > > member id(primary key for Students,faculty and staff [Tables]) > > and now i want to create issues[Tables] with foreign key as member id > but in references i could not able to pass on reference as or > condition for students, faculty ...

Does a foreign key require 'primary key' in the pointed-to table?
I would like to use ignore_dup_key on one of my indexes, so that inserts of rows which duplicate an existing primary key will be ignored. But you can't declare a primary key constraint with ignore_dup_key, so I tried making an index instead. create table my_table (a int not null) go create unique clustered index idx on my_table (a) with ignore_dup_key go execute sp_primarykey 'my_table', a go New primary key added. (return status = 0) The sp_primarykey above is purely for documentation, so I've heard, but I assume it c...

A Question on Database design
Hi All, We have requirement to develop an application in java which should have data export/import option. This application will be deployed in one global centre and in different regional centres. There is no link/network connection between the global centre and region centre. For this application, i have to design a database. The design should support export/import functionality also. I have two approaches towards the database design... 1. Create all tables with PKs(Primar key), as sequence generated ID and FK constraints. If i take up this approach, While importing the regional c...

Database design question
This appears simple enough but I'm going round in circles with it. I have a growing contacts database with the usual contact data , being used to send mail merges via email and mail. I need to categorise each contact record several ways So contact 1 may belong to category 1 , contact 2 could belong to category 1 and category 2 , and so on. The number of categories is intially fixed at 5 but will grow as the database grows and the query needs become more complex. I need to develope a structure that will allow easy creation of queries , easy updating of data entry forms as the categories gr...

Table design question
Hi, constantly facing this design issue I decided to ask for some help. Consider the tables below; there is a user with historical data and a related table log: CREATE TABLE user ( id INT NOT NULL ) <--------------------- | CREATE TABLE user_history ( | user_id INT NOT NULL, oo----------------| valid_from DATETIME NOT NULL, | name CHAR(100) NOT NULL, | ... ) | | CREATE TABLE log ( ...

Database Design Questions
Hello Everyone, I'm really stuck on how to design this application, so I thought I'd see if anyone had any general ideas on how to proceed. I'd say I'm an intermediate level Access developer. I volunteered to help my kid's school (a small non-profit) with a tremendous need they have for a complete student administration database. I've developed fairly complex databases before but this one is very unique in how it must be designed. The challenge is this. A record must be created that contains information about the student and both parents (tracking begins with an i...

Web resources about - Warehouse design question - Fact Table Primary Key - comp.databases.olap

Warehouse - Wikipedia, the free encyclopedia
... , Finland . Goods are shown loaded on pallets to the left of the aisle , and stacked pallets with no loads to the right of the aisle. A warehouse ...

Warehouse - Wikipedia, the free encyclopedia
... . The image shows goods loaded on pallets to the left of the aisle , and stacked pallets with no loads to the right of the aisle. A warehouse ...

How Facebook Manages A 300-Petabyte Data Warehouse, 600 Terabytes Per Day
How did Facebook manipulate the Hive storage format to enable it to deal with a data warehouse that stores some 300 petabytes and takes in about ...

Pias warehouse fire: Soma statement
Dear friends The complete destruction of the Sony DADC /Pias warehouse in Enfield, during the riots on Monday night has caused a worrying time ...

Scaling the Facebook Data Warehouse to 300 PB
... Wilfong, writing for the Facebook Engineering Blog: At Facebook, we have unique storage scalability challenges when it comes to our data warehouse. ...

Bunnings Warehouse (@Bunnings) on Twitter
Log in Sign up You are on Twitter Mobile because you are using an old version of Internet Explorer. Learn more here Bunnings Warehouse @ Bunnings ...

Auto Parts Warehouse on the App Store on iTunes
Get Auto Parts Warehouse on the App Store. See screenshots and ratings, and read customer reviews.

Door Blown Off a Warehouse in Kivalina - Flickr - Photo Sharing!
November 2011 - My staffer, Bob Walsh, visited communities in Western Alaska affected by the large winter storm last month - including Nome, ...

Entertaining Dancer Brightens Up Mexico Warehouse - YouTube
What started as a marketing strategy for a cell phone company has become an Internet phenomenon after Edgar Ramos Nieves’ musical performances ...

Costco offered warehouse to move here
A DARWIN property developer group has offered to build a warehouse and provide a long-term lease for retail giant Costco on prime land in a bid ...

Resources last updated: 2/25/2016 2:28:19 AM