Attribute Key Cannot Be Found: Single Underlying Table!

I've spent quite a bit of time searching this topic and have not come up with an answer that would help my situation.

I'm building a cube from a single table. That table is used to build both the attributes and measures. I sometimes get the "attribute key cannot be found" error even though queries prove that the data indeed there. Often all that needs to happen to fix the problem is to simply reprocess the cube without making any changes.

Any ideas as to how I could fix this problem? It causes a problem as the cube is scheduled and that is failing. I can't be rebuilding the cube manually each day.

Hello! Even if you have fact table dimensions this problem can be related to that you must always process the dimensions first and the rest of the cube later. If you use MOLAP for storage the dimensions will be built as separate objects even if you use a single table as source.

If you process the cube and have activated "process related objects" in the advanced settings dialogue you can process the cube and the dimensions will be processed first.

HTH

Thomas Ivarsson

|||Perfect! That you so much for your help, Thomas. That makes good sense. I've set the Integration Service project to Process Related Objects.

Thanks Again,

Robin Sarac

edit: Spelling mistakes...

Attribute Hierachies

Hi there. I have a problem that I have been struggling to understand. Here is the situation. I will use a time dimension just as an example.

If I build a time dimension containing 2 Years of dates from 2005 to 2006 with 3 attributes: Year, Month, Date and set my attribute relationships as Date ->Month and Month -> Year. In management studio I can drag over Month and then Year (in that particular order ) and it will give me the correct results. For example if I expand the Month of January 2006 the year column will show 2006. Now If I filter the year column for the year of 2006, you would expect that the month column would only contain the months of 2006, but it doesn't. It still has months for 2005.

Now if I rebuild that dimension setting the attribute relationships to the key. I.e. Date ->Year, Month. And no attribute relation to month. I can rebuild the cube and drag over the same 2 colums, Month and Year (in that order) and then filter on the the year column for 2006 and then the Month column will show only the months for 2006.

Whats going on here?

I would check to make sure you have the total heirarchy structure as part of your key.

IE: Date should have a key of (Year --> Month --> Date).

|||

I'm not quite sure what you mean since date is the key.

My user hierarchy for the first scenario is:

Year

Month

Year (year is the attribute relationship)|||

I was able to recreate this scenario using Adventure Works. Seems to me this should work, but it behaves as you described. Here's what I did:

First, I created a new dimension called Date Test 1 that had three attributes -- Date, Month, Year based on the DimTime dimension table. For this dimension, I left Month and Year related to Date (the default). The Month attribute had members like "January 2002", "January 2003", etc. while the Year attribute had members like "CY 2002", "CY 2003", etc. I included this dimension in the Adventure Works cube, relating it to the Internet Sales measure group (joining from the Date attribute to the OrderDateKey column in the underlying fact table). After deploying and processing the updated project, I browsed the cube. From the Date Test 1 dimension, I put Month on rows and got each month listed. I then added Year to the filter area (not the subcube filter area, but the normal page filter area. I selected only "CY 2003" from the list of years and the list of months was indeed filtered to just those in 2003. Note that this was done without putting any measures in the data area -- just the dimension data. This works as I would expect it to.

Second, I created another new dimension called Date Test 2 that had the same three attributes with the same definitions. However, in this test, I moved the attribute relationship for Year from the Date attribute to the Month attribute. Thus, Date had a single attribute relationship to Month and Month had a single attribute relationship to Year. I again added this new dimension to the Adventure Works cube, relating it in the same manner as Date Test 2. Then I did the same browsing test. In this case, adding Month on rows to begin with gives the same list of all months across years. But, adding Year to the filter area and selecting "CY 2003" did NOT filter the list of months displayed on rows. Again, this was done without any measures in the data area (as was the first test above).

A few interesting observations:

1. I turned on SQL Server Profiler and captured the MDX queries being generated. As far as I can tell, they look exactly the same. They query in the browser window results in a few different queries that look something like this:

CREATE SESSION
SET [Adventure Works].[{39001120-29B1-4733-BBBA-C2BA3B63A05B}Pivot21Axis1Set0] AS
'
{
{ [Date Test X].[Month].[All] },
AddCalculatedMembers([Date Test X].[Month].[Month].MEMBERS)
}
'

SELECT
NON EMPTY [{39001120-29B1-4733-BBBA-C2BA3B63A05B}Pivot21Axis1Set0]
FROM [Adventure Works]
WHERE
(
[Date Test X].[Year].&[2003]
)

2. In the browser window, if I instead add the Year attribute to the subcube filter area (instead of the page filter area in the pivot area itself), things work as expected. For both dimension tests, the list of months displayed on the rows after adding the Month attribute is filtered when a specific year is selected for the Year attribute filter.

I suspect that this has something to do with how the query engine determines what attribute combinations actually exist (ie, the autoexists feature of the engine). It seems to be able to determine that the months of a given year are the only months that exist when both attributes are related to the key attribute for the dimension. But it doesn't seem to be able to determine this in a scenario where the relationship between the attributes is above the key attribute for the dimension.

I would consider this to be a bug myself. Anyone from Microsoft care to comment (Mosha?)

HTH,

Dave Fackler

|||

Thanks for taking the time to go through this. Another thing to note is that the same situation occurs when both attributes are related to the key attribute and then you try to filter the dates by the month.

I suspect that this is a bug as well. Any other comments?

Thanks.

Attaching Database does not name database correctly

Here are the steps I have taken:
I have a situation where I need to rename a database and its physical files
via a scripting process. I am having a problem in the last step where I am
reattaching the renamed files with the new database name...the problem is
that no matter what I attempt to rename the database to it keeps the old
database name in the properties. Incidently this even happens using
Management Studio...if you detach the database, rename the data and log
file, and then reattach it then check the properties of the database under
the files tab and you will see that it kept the original database name.
Example:
Original filename 00002014
1. Detach the database with sp_detach_db - Works fine
2. Set the permissions on the data and log file with cacls.exe - Works fine
3. Create a renamed backup copy of the original data and log files using
xp_cmdshell Copy - Works fine
4. Create a renamed data and log file that is the same as the new database
name will be. - Works fine
5. Create a database for Attaching - Weirdness occurs:
CREATE DATABASE [90002014] ON
( FILENAME = N'D:\Microsoft SQL Server\90002014.mdf' ),
( FILENAME = N'D:\Microsoft SQL Server\90002014_log.ldf' )
FOR ATTACH
The problem that happens is that even though the database is created and
attached correctly.the name when I query "master.dbo.sysaltfiles" is the OLD
database name. So instead of 90002014 it was still the old 00002014 but the
database name on the database in management studio is the new name 90002014.
It is crazy! How do I change the actual name of the database when I do the
CREATE DATABASE FOR ATTACH Procedure. (or for that matter using the
SP_ATTACH_DB stored proc, both have the same problem)
RonYou must be talking about the LOGICAL names of the files not the database
itself. This is normal. To change the LOGICAL names you use the ALTER
DATABASE command with the MODIFY FILE option to change the Logical name to a
new one.
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:uDbx7jKPGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Here are the steps I have taken:
> I have a situation where I need to rename a database and its physical
> files via a scripting process. I am having a problem in the last step
> where I am reattaching the renamed files with the new database name...the
> problem is that no matter what I attempt to rename the database to it
> keeps the old database name in the properties. Incidently this even
> happens using Management Studio...if you detach the database, rename the
> data and log file, and then reattach it then check the properties of the
> database under the files tab and you will see that it kept the original
> database name.
>
> Example:
> Original filename 00002014
>
> 1. Detach the database with sp_detach_db - Works fine
> 2. Set the permissions on the data and log file with cacls.exe - Works
> fine
> 3. Create a renamed backup copy of the original data and log files using
> xp_cmdshell Copy - Works fine
> 4. Create a renamed data and log file that is the same as the new
> database name will be. - Works fine
> 5. Create a database for Attaching - Weirdness occurs:
>
> CREATE DATABASE [90002014] ON
> ( FILENAME = N'D:\Microsoft SQL Server\90002014.mdf' ),
> ( FILENAME = N'D:\Microsoft SQL Server\90002014_log.ldf' )
> FOR ATTACH
>
> The problem that happens is that even though the database is created and
> attached correctly.the name when I query "master.dbo.sysaltfiles" is the
> OLD database name. So instead of 90002014 it was still the old 00002014
> but the database name on the database in management studio is the new name
> 90002014.
>
> It is crazy! How do I change the actual name of the database when I do
> the CREATE DATABASE FOR ATTACH Procedure. (or for that matter using the
> SP_ATTACH_DB stored proc, both have the same problem)
>
> Ron
>|||That was it...THANKS!
Ron
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u2Ogc5KPGHA.3924@.TK2MSFTNGP14.phx.gbl...
> You must be talking about the LOGICAL names of the files not the database
> itself. This is normal. To change the LOGICAL names you use the ALTER
> DATABASE command with the MODIFY FILE option to change the Logical name to
> a new one.
> --
> Andrew J. Kelly SQL MVP
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:uDbx7jKPGHA.2300@.TK2MSFTNGP15.phx.gbl...
>

Attaching a database, but missing a file

The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignoring
the missing file?
--Zorpie
In some cases, you can attach without the log file and SQL Server will create a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if they have any emergency
rescue options. It will probably lead to a possibly both logically and physically inconsistent
database. So this is not something you want to do. Probably to dig up your most recent backup
instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of the
> log files was not getting backed up. (there were three different log files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because of
> one log file missing? Or is there some way to force the attachment, ignoring
> the missing file?
> --Zorpie
|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie
|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.

> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie

Attaching a database, but missing a file

The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignorin
g
the missing file?
--ZorpieIn some cases, you can attach without the log file and SQL Server will creat
e a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if
they have any emergency
rescue options. It will probably lead to a possibly both logically and physi
cally inconsistent
database. So this is not something you want to do. Probably to dig up your m
ost recent backup
instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of th
e
> log files was not getting backed up. (there were three different log file
s)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment, ignor
ing
> the missing file?
> --Zorpie|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.

> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie

Attaching a database, but missing a file

The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignoring
the missing file?
--ZorpieIn some cases, you can attach without the log file and SQL Server will create a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if they have any emergency
rescue options. It will probably lead to a possibly both logically and physically inconsistent
database. So this is not something you want to do. Probably to dig up your most recent backup
instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of the
> log files was not getting backed up. (there were three different log files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because of
> one log file missing? Or is there some way to force the attachment, ignoring
> the missing file?
> --Zorpie|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie

Attaching 6.5 db files

Hello everyone,
I'm in a situation with a 3rd party where they have 3 .dat files (no log fil
es) from a SQL 6.5 database. These files were located on a backup tape from
2001. The client would like to make this database operational again. The
master.dat file was not rec
overable, nor was the log file. Is there any way to "attach" these files so
we can ultimately upgrade to SQL 2000? Any input would be appreciatedYou would need a 6.5 installation to begin with. Then you would either use
LOAD ("restore") if the DAT file is a backup file (produced with DUMP) or
use DISK REINIT and DISK REFIT if it is a database file (which I'm sure
requires all the database devices that constituted the database).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mike" <mjucht@.neb.rr.com> wrote in message
news:E27B27A7-F299-473A-A816-42C757AE8330@.microsoft.com...
> Hello everyone,
> I'm in a situation with a 3rd party where they have 3 .dat files (no log
files) from a SQL 6.5 database. These files were located on a backup tape
from 2001. The client would like to make this database operational again.
The master.dat file was not recoverable, nor was the log file. Is there any
way to "attach" these files so we can ultimately upgrade to SQL 2000? Any
input would be appreciated|||Mike
You can not attach SQL 6.5 databases, it was not a feature available at that
time.
If you have the dat files you may be able to restore the database to a SQL S
erver 6.5 installation and then upgrade. Although you only have .dat files y
ou may have the logs, in 6.5 you do not have to seperate the data and logs s
o the .dat files may contai
n both.
However even if you do have the entire database in the files you have, you s
till will not be able to restore the database unless you build it exactly li
ke it was. What this means is that say you had a 300mb 6.5 database that was
created as 100mb and expan
ded by 100mb twice. You would need to create an empty database in exactly th
e same way before you could restore it.
The best way to restore a 6.5 database is to have the output from sp_help_re
vdatabase available. I don't suppose you have that do you?
Hope this helps
John|||I'm reading up on the DISK REINIT command and it states the SIZE parameter m
ust be identical to how the device was originally created. Is there any way
to tell how the device was originally created without the master.dat? I'm
afraid the answer is "no" b
ut if you have any ideas I'd appreciate them.
Thanks,
Mike
-- Tibor Karaszi wrote: --
You would need a 6.5 installation to begin with. Then you would either use
LOAD ("restore") if the DAT file is a backup file (produced with DUMP) or
use DISK REINIT and DISK REFIT if it is a database file (which I'm sure
requires all the database devices that constituted the database).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mike" <mjucht@.neb.rr.com> wrote in message
news:E27B27A7-F299-473A-A816-42C757AE8330@.microsoft.com...
> Hello everyone,
files) from a SQL 6.5 database. These files were located on a backup tape
from 2001. The client would like to make this database operational again.
The master.dat file was not recoverable, nor was the log file. Is there any
way to "attach" these files so we can ultimately upgrade to SQL 2000? Any
input would be appreciated|||You have the physical file size, and can go on that!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mike" <mjucht@.hotmail.com> wrote in message
news:ECC5EA3A-2283-45A5-87A6-F29156BD01B4@.microsoft.com...
> I'm reading up on the DISK REINIT command and it states the SIZE parameter
must be identical to how the device was originally created. Is there any
way to tell how the device was originally created without the master.dat?
I'm afraid the answer is "no" but if you have any ideas I'd appreciate them.
> Thanks,
> Mike
> -- Tibor Karaszi wrote: --
> You would need a 6.5 installation to begin with. Then you would
either use
> LOAD ("restore") if the DAT file is a backup file (produced with
DUMP) or
> use DISK REINIT and DISK REFIT if it is a database file (which I'm
sure
> requires all the database devices that constituted the database).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Mike" <mjucht@.neb.rr.com> wrote in message
> news:E27B27A7-F299-473A-A816-42C757AE8330@.microsoft.com...
(no log
> files) from a SQL 6.5 database. These files were located on a backup
tape
> from 2001. The client would like to make this database operational
again.
> The master.dat file was not recoverable, nor was the log file. Is
there any
> way to "attach" these files so we can ultimately upgrade to SQL 2000?
Any
> input would be appreciated
>
>