SSAS Cube missing attribute keys
-
I'm getting that wonderful "missing attribute key" error when trying to process my cube. I know why I'm getting it. I have a dimension that does not have values for each of the records in the Fact table. In fact, in only has a limited number of records. This is the deliberate result of a named query.
It's like a LEFT OUTER JOIN in T-SQL. For example, OrderID is in the Fact table, and OrderID also exist in the Dimension table. But not all orders have X attribute associated with them, so it's a 1 to 0-to-many relationship between the two.
I don't want to make up or force values in the dimension table. If I try and add this attribute into the Named Query for the fact table, it'll slow down processing on a mega level (I've already tested the queries in SSMS).
Any advice on how I can proceed?
-
So using your example, (assuming it's keyed on OrderID) your dimension *doesn't* contain all OrderID's that exist in the Fact? I guess you could let AS handle the unknown for you (change your processing option) but I'm guessing thats probably not what you want either.
Steve.
-
I appreciate at the advice, but this is initial cube processing. I can process the structure just fine, but when I try to process the data, everything fails.
Unless I'm misunderstanding the terms, structure doesn't help me if I can't get the data out there.
-
I guess it's structural (in a way) but it's definitely a processing option - to ignore the missing key values (and the structural part is setting the dim to have/use an unknown member).
I guess i don't understand why you've not got the PK's in the dim to match the entries in the Fact but that's by the by
Steve.
-
For the same reason that any SQL Server might have a "LEFT OUTER JOIN" situation between tables. In this particular case, there is a "rule" that applies only to two of three sources feeding into our data warehouse. Unfortunately, the BU wants to see that information displayed on their final report with a Zero in the cases where it is NULL.
Because of the nature of the information, I have MyFactKey listed in the DIM table as what needs to be joined on rather than MyDimKey in the Fact table. Which makes it difficult (if not impossible) to make up data in the Dim table to fill in the blank records.
-
Curiouser and Curiouser, the issue may not be me after all. In this particular case, the two ids in question actually *should* have associated information in the source db, but don't. :exclamationmark:
I still would like the answer to my question, though, if anyone knows it.
-
On looking back over what you've written, i think I get where you're headed and it sounds almost like this should be treated as a Fact, maybe with a dim built over it. Taking that a step further, trying to relate it to the existing Fact data, it almost sounds like a M2M style modeling problem. Have you looked over Marco Russo's paper on this? I'm not sure if M2M supports the 1-0 though, so I'm not sure if that may end up breaking that approach.
Steve.
-
I'll take a look at the M2M stuff. Not sure what you mean by building a Dim over a Fact table, though. Could you elaborate or point me to a link?
-
Brandie, skip what I said (on both the fact as dim [ie degenrate dim] and M2M).
I go back to what I had suggested initially. To make this work, you're going to have to provide PK's in the dim - you can either do this through your processes (which you indicated was really a no go) or let SSAS create them for you (ie the unknown i mentioned). The unknown is going to leave you with an unknown member in the dim, which you can choose to make i) visible; ii) hidden; iii) automatically NULL (or iv None, but then, you'd be back where you started).
Once you've enabled the Unknown in your dim, when processing the cube, go to the Change Settings and then Dimension Key Errors tab. In there, chose to use a Custom Error Config and then Ignore the errors (erk).
Atfer you finish processing,you'll end up with a single Unknown (either named NULL, or hidden, or named 'helpmeyoda' or whatever you chose to name it in the Dim) - this unknown is effectively the PK to the FK's in the Fact that are missing.
Steve.
-
I agree that you have to have some kind of unknown bucket, but you may want to consider two unknown buckets. The first one is for a value that isn't there and that you don't expect to be there. I call this "N/A" and is always -1 for the dimension key. The other is for a value that isn't there but should be there. I call this "Unknown" and is always 0 for the dimension key.
-
Okay, I don't think these scenarios will work, but maybe I'm just confused. Let me try to re-phrase what I was trying to say earlier and see if I'm missing something.
FactTable has CKey as it's PK.
DimTable has CKey and SourceKey as its combined PK.
Both tables, Dim & Fact, use Named Queries as their sources. Not all CKeys exist in DimTable. To create an unknown record in the Dim, I'd have to know in advance what CKeys don't exist in the DimTable.
I can't add a PK to DimTable and stick that PK in the FactTable because it would require Monster Query from Heck that would destroy any semblance of performance.
I can't fake CKey in DimTable because it would also require Monster Query from Heck.
You're saying there's no way of saying "make this value a zero when there is no record in DimTable"?
We've fixed the original data problem in our ODS, but the problem is there are still 400+ records that don't connect to this Dim table.
EDIT: Okay, wait. I think I get what Steve's last post is saying. Working on that now.
-
DimTable has CKey and SourceKey as its combined PK.
Dimension tables should have their own computer generated key. This key would get joined to the fact table. This dim table would have an Unknown bucket, which many designate with the 0 key. Anytime you had a value in the fact table that did not have a corresponding entry in the dim table, set the field to 0. If N/A is also a valid option, consider adding another field with -1 as the key for that bucket.
I hope this helps.
-
Hey Brandie - sounds like you've got it now, but just in case - SSAS can create that unknown (catchall member) for you auto-magically, so you *won't* need to know what you don't know (ie missing keys).
Personally, i prefer the same approach as Ron mentioned (it's what we use for basically all cases), but you would end up having to modify the fact FK's to -1, 0 etc and I thought you weren't interested in doing this.
Last note - when you use the Unknown member in the dim (the one SSAS creates for you), keep in mind, it will collect *ALL* members that are unknown (ie the FK's in the fact without matching PK's in the dim), which in general, is what you want - BUT, if your ETL breaks down in some way, without running some additional validation queries, you'll never know that a value that *should* be in the dim table *isn't*, as it will be grouped up with all the other member values into a single 'Unknown'.
Steve.
-
Steve,
I just tried what you instructed with the Unknown. I actually had the Unknown Member already set to Visible, but I missed changing the Error process part of it originally. So far the data has processed fine, so now I get to browse the Cube and see if it did what I wanted it to do.
One hurdle down. Thanks.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply