Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Data cube based on one table

Answered Data cube based on one table

  • Friday, November 30, 2012 9:03 AM
     
     
    Sign In to Vote
    0

    Hello all,

    I have a large amount of data all in one table and when I'm trying to create a data cube I only get one dimension.

    Is it possible to create more than one dimension with one table?

    For example I have these fields (ItemID, SellerType, ShopLocation, ItemCondition, Sold, Price, ShippingCost)

    I want to compare items based on Location, Type, Condition and see how shipping cost and price affect the sales.

    Can I use data mining to do that?

    Thank you in advance.

     

All Replies

  • Friday, November 30, 2012 3:31 PM
     
     Answered
    Sign In to Vote
    0

    Yes, you can. It's called fact or Degenerate dimension. And, It's got nothing to do with Data mining.

    Please follow the link:

    http://sql-bi-dev.blogspot.com/2010/05/creating-degenerated-dimension.html

    Fact/Degenerate dimensions are mostly used to create drillthrough solutions( for example, when you need to drillthrough and report out invoice numnber (itemid in your case) or transaction number). And its advisable to create separate dimension tables for (ShopLocation, item condition,sellertype) in the relational data warehouse instead of using Fact dimension.

     hth,

    Rok

    please remember to mark as answered if the post helped resolve the issue. "Essentially, all models are wrong, but some are useful"" - George E.P.Box


    • Edited by rok1 Friday, November 30, 2012 3:52 PM
    • Marked As Answer by dealwi8me Saturday, December 01, 2012 10:15 AM
    •  
     
  • Saturday, December 01, 2012 9:40 AM
     
     Answered
    Sign In to Vote
    0

    Thank you for the link, it's very helpful.

    The problem is even though I try to create a new dimension (as the link shows), the only choice i get is the already added dimension.

    I'm not sure if I understand it correctly, but dimension must be based on a primary key and since I have one table with one key I can only create one dimension. In order to be able to have more than one dimension I should have something like this:

    Table Items ((ItemID, SellerTypeID, ShopLocationID, ItemConditionID, Sold, Price, ShippingCost)
    Table SellerType (SellerTypeID,SellerTypeDescription)
    Table ShopLocation (ShopLocationID,ShopLocationDescription)
    Table ItemCondition (ItemConditionID, ItemConditionDescription)

    Is that correct? Will it work if I create a relational data warehouse as this?

    Thanks again.

    • Marked As Answer by dealwi8me Saturday, December 01, 2012 10:15 AM
    •  
     
  • Saturday, December 01, 2012 10:15 AM
     
     
    Sign In to Vote
    0

    I tried what I mentioned in my previous post and it works. I can now create the dimensions I need.

    Thank you.