September 12, 2002 at 1:20 pm
I am planning a data mart that will have three dimensions I want to combine into a single dimension (ideally without using virtual dimensions). For instance, I have the dimensions NPA, NXX, and DID. In the fact table I will have foreign key fields for each of these dimension tables. Ideally what I would like is a single dimension that would facilitate drilling down from NPA to NXX to DID. Is this possible?
Thanks,
Michael Weiss
Michael Weiss
September 12, 2002 at 3:53 pm
quote:
I am planning a data mart that will have three dimensions I want to combine into a single dimension (ideally without using virtual dimensions). For instance, I have the dimensions NPA, NXX, and DID. In the fact table I will have foreign key fields for each of these dimension tables. Ideally what I would like is a single dimension that would facilitate drilling down from NPA to NXX to DID. Is this possible?Thanks,
Michael Weiss
bygs 🙂
bygs 🙂
September 12, 2002 at 4:12 pm
sorry about the dupe..
I have done something similar, but instead of using separate table information, I create a view of the data. For instance:
create view dim_numbers
select
left(variable,3),
substring(variable,4,3),
right(variable,4),
variable,
from datasource
NPA->NXX->DID
You can use views as a dimension source and the drill down can be created from there.
bygs 🙂
bygs 🙂
September 12, 2002 at 4:50 pm
Thanks! I forgot about using views as sources for dimensions. I must admit I am not entirely following you though. When you say you are not using separate table information but rather a view created from the data, what data are you referring to? The data being loaded into the data mart? If so, won't this necessitate an incremental update of the dimension with every data load? That is what I am trying to avoid as I need to be able to update the cubes in near real time...and I can't use a single table with every combination of npa, nxx, and did as that would be something on the order of 6 plus billion rows and would take forever to do the foreign key lookups against in the ETL processes. I am intrigued by your idea but could use some help in understanding it better...
Thanks!
Michael Weiss
Michael Weiss
September 13, 2002 at 11:43 am
...Hey, I knew a Michael Weiss in college...
Well, I created my view from my existing data where the unique ID is included in the view and since each final DID is less than 64k there is no process problems.
Are you trying to append to the existing data, and only reprocessing that new data?...
Could you be a little more descriptive of what your trying to accomplish, so I can think of a schema.
bygs 🙂
bygs 🙂
September 14, 2002 at 9:07 pm
Okay...now I am really lost! lol What does the DID being less than 64k have to do with whether or not it is a problem processing? What I am trying to accomplish is the appending of new data and only processing that new data...however, if append the data to a "phone number" table for example, and do an incremental update, AS still does distinct count queries against the entire table...this takes about three hours and I need to get cube processing time down to around a few minutes...here is my proposed schema...
Fact Table: fact record id,date key, time key, npa key, nxx key, did key, duration
Dimension Tables: 1. npa; 2. nxx; 3. did
ETL processes will do lookups against npa, nxx, and did tables and insert corresponding key values into the fact table.
Using this schema, I need to be able to drill down through npa, nxx, and did in a single dimension (npa, nxx, and did will be levels in the phone number dimension).
I really am curious what you meant by deriving your view from existing data...where is your existing data held and in what format? Thanks for your suggestions and input...
Michael Weiss
Michael Weiss
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply