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?