FK Issue with Fact Table

Question

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

UPDATE: Added separate Twitter Schema Is this design feasible?

Answer 1

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.