0

I have a Datawarehouse star schema. One Dim table is 'DimTweet'. This contains tweet details.

For a selected date I want to be able to display the number of tweets.

However, I have a problem in my current design. How do I use one TweetID as FK in the Fact tbl for multiple rows in the DimTweet tbl?

Code:

INSERT INTO [CarDW].[dbo].[FactCarDetail]
           ([CarID]
           ,[RegionID]
           ,[DateID]
           ,[TweetID]
           ,[SharePrice]
           ,[ShareQty]
           ,[SalesQty]
           ,[TweetCountPositive]
           ,[TweetCountNegative])
SELECT
    c.CarID,
    r.RegionID,
    d.DateKey,
    -- ????? AS TweetID
    scsp.Price,
    scsp.Quantity,
    scs.Quantity,
    -- COUNT NO. OF TWEETS Postive
    -- COUNT NO. OF TWEETS Negative
FROM 
    dbo.DimCar c        
INNER JOIN  
    dbo.StagingCarSale scs
    ON scs.CarModel = c.Model
INNER JOIN
    dbo.DimRegion r
    ON r.Region = scs.Region
INNER JOIN
    dbo.DimDate d
    ON d.Date = scs.SaleDate
--INNER JOIN
--  dbo.DimTweet t
--  ON t.
INNER JOIN  
    dbo.StagingCarSharePrice scsp
    ON scs.CarModel = scsp.CarModel
        AND scs.SaleDate = scsp.PriceDate

DW Design

UPDATE: Added separate Twitter Schema Is this design feasible?

enter image description here

  • 2
    That factTable is not at the granularity of a single tweet so tweetid doesn't belong there. What is tweet related to in it? – Martin Smith Apr 21 '16 at 21:56
  • I am trying to add Tweets to the DW to show any possible increase or decrease in car sales/share price and effect on number of positive/negative tweets. – K09 Apr 22 '16 at 7:57
5

I see a tweet as an event happening, so I would model it as a new fact table FactTweet. More specifically as a factless fact. The dimensions for FactTweet would be DimDate, DimCar (if you can relate a tweet to a car), DimAuthor and I would probably keep URL and Description as degenerated dimensions. You could potentially add the sentiment of the tweet as well (positive, negative or neutral).

DimCar and DimDate would be conformed dimensions for both FactTweet and FactCarDetail so you would be able to report on both fact in a single query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.