Multiple Date Dimensions

  • I am a DW noob, so please be gentle with me 🙂

    My fact table has sales information with a number of different types of dates, for simplicity's sake let's say I have an Order Date and a Due Date.

    The simple question is: do I need to define two dimensions or can I use one date dimension for both purposes?

    Then as a follow up, if I need two, how do I create them using the SQL Server script? It creates one no problem, but then when I try to create another it gets confused telling me there's already a date dimension. If I can use the one, then how do I define that as two dimensions in my cube? How do I put both dates in the same cube, related to one dimension?

    Thanks for your time

    Daniel

  • As I recall, you want just one hierarchical date dimension which is to be shared by all of the date-based fact columns.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • hey Daniel,

    Are you using 2005 or 2000? In 2005 you can use a single time dimension (or any dim really) as a Role Playing dimension, so you create it once, then use it many times (against each of the FK's in the fact) in the cube.

    In 2000, you'll need to create separate dimensions (in AS) but these can all be fed from the single 'Time' table in the DB.

    Steve.

  • Hi Steve, Barry, thanks both for the replies.

    I am using SQL Server 2005. I've tried to make it work with one physical table and two dimensions and I can't get it to work. The time dimension shows up once in my cube and when I set a filter on it, it filters the fact table on both fields. I can see how you'd only need one physical table with the date records in them, but can't figure out how to create two dimensions off of that table to tie to the two different date columns in my fact table.

  • Hey Daniel,

    If you have the one dimension set up in your project, then go to the Cube Structure tab (when editing the cube) and in the Dimensions pane, right click and select Add Cube Dimension. Select the dimension (even though you've added it previously). It should add with a name like " 1". Using the properties (or select it and F2) rename it to something nicer. Then go to the Dimension Usage tab (still cube editing) and where it may be currently related to the fact using the (in your case) Date FK1, change it to relate to the fact using DateFK2 (where Date FK1 and Date Fk2 are the two foreign keys in your fact that you want a Date dim for).

    Bottom lin eis you use 1 dim table to create 1 (project/AS DB) dimension, but then add that Dim twice (or more) to the cube and then remember to change each of these Cube dimensions relate to the fact.

    For the official approach see here.

    HTH,

    Steve.

  • Hey Steve,

    That looks like it is exactly what I've been looking for. You saved me a lot of work, thanks a lot mate :).

    Daniel.

  • Hi guys. Well, I've tried the approach above, adding a copy of the dimension to the cube and setting it's relation to another key in my fact table. But when I try to filter on one of the role dimensions, the dataset shrinks to the filtered set. One dimension, dimProgram lists all TV programs. I added a new role dimension called dimFilterProgram and linked to factProgram on the dimFilterProgramID key. The dimProgram dimension is linked to the dimProgramID. I want to see what all people who watch say American Idol (selected in the dimFilterProgram dimension) also watch. I filter on programName = American Idol using the dimFilterProgram dimension. Then I try to browse all the other programs using the dimProgram dimension. But, guess what- only American Idol viewers show up in the cube. Somehow the cube thinks these are the same.

    Do you have any idea what is going on or how to fix?

    Thanks,

    Ben

  • Hey Ben,

    I think the problem is not so much with your implementation as your data. You mention that you have two fields (dimFilterProgramID and dimProgramID). Now, i'm not there so I can't say for sure, but i'm guessing that at best, you have values such as 'American Idol' and 'Wheel of Fortune' (obviously using their keys) for a given fact record. At worst, you'll actually have 'American idol' and 'American idol' for the two fields respectively. Assuming it is the latter, then what you're seeing in the cube is correct (ie filtering to American Idol on Dimension 1 will effectively filter the fact records 'in play' to being those that have American Idol in field 1 , and i'm assuming, field 2).

    I guess that's the downside. The upside is that it sound slike what you're really wanting to do is (a form of) 'Market Basket Analysis' - where you want to see what other 'products' are purchased by a customer in the same transaction. If this is what you're looking to do, then AS2K5 comes out of the box with data mining algorithyms, one of which is used for Market basket analysis (it gets even easier if you have Excel 2007 and AS2K5, download the Datamining addins and off you go). You could pick Person as the transaction identifier and then the TV Show as what you're analyzing.

    Assuming i've gotten it all wrong and you're not looking for MBA but literally, if Joe watches American Idol, what else does/did he watch 'ever', then theoretically you could get this using your two fields, but you'll be limited to only ever seeing (at most) 2 shows that he watched based on your first filter selection. So where the fields have FK values of 98 and 146, and then another transaction has 37 and 42, if you filter to 98 (American Idol), you will only see in the other dimension 146 (Wheel of Fortune), not 37 and 42 (Survivor and Smackdown) because these have no relationship to other fact record (other than being watched by Joe). You could of course expand this and have say 6 fields for shows watched, but you'll always limit yourself by the number of fields you have.

    HTH,

    Steve.

  • Steve, thank you very much for your response. What I'd like to understand from your response is more about what fact records will show when you use one role play dim as a filter. Your recap of my data is pretty much on target. Basically, think of 10 tv viewers watching programs for a week. The fact table has one record for each person for each program they watch (together with date, time, channel, etc). If viewer one watched 15 programs during the week, there would be 15 records in the fact table for that viewer.

    So without data mining, I do want to get a list of all the programs a viewer who saw American Idol watched. I want to exclude all people who did not watch American Idol, and get the list of all other programs watched by people who saw American Idol.

    With a time role play, it would be like asking to see all orders shipped (ship date role) for orders entered (entry date role) on Wednesday of last week. What's happening in my case is that it seems like under the covers, AS is still not seeing the role play dimension as separate. It doesn't make sense to me and I've spent 2 days trying to solve this. By asking for American Idol viewers through the program filter role, I still should see all programs for the relevant people from the program role.

    Let me know what you think. And thanks again!!

    Ben

  • Hey Ben,

    The problem i see here is that this case doesn't require a role playing dimension - it's really not going to do what you want it to (or think it should). Using your Order and Ship date example, the difference there is that (usually, unless it's Amazon 😉 ) the ship date is several days later than the order date. There are two dates on the fact record, and so when filtering on the order date dimension, this will filter (or slice) the cube data to only fact records with that ordered date. The other dimension will then show the respective dates the orders were shipped. In your TV example, this is the equivalent of recording only 2 programs watched, A & B, so when you filter to A, the other dim will show B.

    With your dataset, and knowing you don't want to use DM, then i think what you've really got is a many to many issue (like bank accounts and customers). Probably with only a slight modification to your model you'll get the M2M working and delivering the results you're after. I think if you take a look at Mark Russo's paper (here) on M2M dimensions your problem sounds like the classic M2M and his examples inthe first chapter should help in getting it set up.

    HTH,

    Steve.

  • Steve, thank you for your clarification. It helped me understand what is happening here. Maybe I will use data mining after all. The interesting thing here is that it's so easy to do in the relational world i.e.

    Select p.program_name, count(distinct f.tv_watcher)

    from fact_program f join dim_program p on f.program_id = p.program_id

    where f.tv_watcher in

    (select f1.tv_watcher

    from fact_program f1 join dim_program p2 on f1.program_id = p1.program_id

    where p1.name = 'American Idol')

    Maybe I need a role playing fact table :). I'll check out the paper you referred me to as well.

    Thanks again,

    Ben

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply