Solved

Can I configure multiple surrogate keys on a table?

Posted on 2011-09-23
10
416 Views
Last Modified: 2016-02-14
I am working with a Time table and time intelligence in ssas 2008. I would like to assign  surrogate keys to FIscal Year Fiscal Quarter Fiscal Month Fiscal Day.  

Is there a way to perform this in column properties in the current table?
0
Comment
Question by:rhservan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 36587862
I think you are misusing the term "surrogate keys".  "Surrogate keys" are assigned to tables, not to columns (FWIW, you can have as many as you want.  But what would be the point???) Also, surrogate keys are unique--if Fiscal Year were a surrogate key, then each year could only appear once in your table.  

Perhaps, do you mean "foreign key".  Not sure what you are trying to do?  

Or are you referring to the identity property?  Only one "identity" column is allowed per table.  

0
 
LVL 2

Assisted Solution

by:marappan
marappan earned 250 total points
ID: 36587946
Hi rhservan,

                      I can help you with this. But, I need to know why do you want to use surrogate key instead of using natural key. If you have already chosen a primary key can i know what is that? Also can you please explain what you are going to achieve by adding surrogate key?

Thanks,
Marappan
0
 
LVL 2

Expert Comment

by:marappan
ID: 36588015
If you are tying to enforce uniqueness to those four columns below is the syntax:

CREATE TABLE Calendar_DEMO
(
FIscal_Year INT NOT NULL UNIQUE,
Fiscal_Quarter INT NOT NULL UNIQUE,
Fiscal_Month INT NOT NULL UNIQUE,
Fiscal_Day INT NOT NULL UNIQUE
)
GO
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:rhservan
ID: 36588023
I am building Fiscal YTD, QTD, MTD & DTD.  It was suggested that I use surrogate keys in order to utilize a cumulative total for each period.
0
 
LVL 2

Expert Comment

by:marappan
ID: 36588065
Can you please paste your scheme for better understanding?
0
 

Author Comment

by:rhservan
ID: 36588112
Schema attached.
SCHEMA.JPG
0
 
LVL 2

Expert Comment

by:marappan
ID: 36588467
Can you please give me the name of the table and column name you want to implement the key?
0
 

Author Comment

by:rhservan
ID: 36588528
DimTimeIn
Fiscal YTD, QTD, MTD & DTD
0
 

Author Comment

by:rhservan
ID: 36588694
I am preparing to run the time intelligence tool on my cube.

The DimTimeIn Table was created with two calendars:
1. Calendar Year
2. Fiscal Year

I have built hierarchies in SSAS for Fiscal Year. I would like for this calendar to provide:
1.F Year To Date - When clicked on the year value It gives me a cumulative total from January 1
2. F Quarter To Date - When clicked on the quarter value it gives me cumulative total from the first day of that quarter.
etc......

Hope this helps  
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36589430
Let me comment on you DimTimeIn table.

1. It appears that it would be better named DimDate as the granularity is date, not time.  :>)

2. The table already has a surrogate key:  Date_Key.  To my way of thinking, PK_Date is also a surrogate key.  I'm still not quite "getting" the rationale for two surrogate keys on the same table.

3. The columns are present to do the rollups you desire.  I think what you are asking is there a way to combine the multi-column rollups into single-column rollups (for sake of simplicity, I guess).   Sure, you can do that.  

Let's say your FY column contains the 4-digit year and your FQ column contains the 1 digit quarter and your FM column contains the 1-2 digit month.  To do a rollup by month, you need to involve all 3 columns.  You can derive single columns: yyyyqq and yyyymm to enable single column rollups for FQ and FM.   You can even do it in a view or computed columns so that there is no effort to populate them and no chance they will get out of sync.  

4. Stiill not sure how "surrogate keys" apply to your situation.  The only "stretch" that comes to mind is if you are considering moving from a star design to a snowflake design. In that case FY, FQ, FM are dimensions in their own right and THOSE dim tables would best have surrogate keys that are referenced by foreign keys in the dimTimeIn table.

5.  I suppose I've confused you beyond hope, by now, so let me give the simple advice. Don't add anymore surrogate keys to dimTimeIn.  Do not add more foreign keys to the dimTimeIn table.  Either derive the new columns you need or if you need add FQ, FY dimensions and link them to your fact table.

 







 


 

0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL: Only Wanting One Record 8 60
mssql 7 32
How do i delete the last node in an xml in T-SQL 7 26
Rewriting a simple query 2 31
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question