I'm working on creating a star schema for my project. I want to use it for reporting. I have used Google Play Store dataset from Kaggle.

The thing that I'm having trouble loading data into my fact tables. Below is my schema design. I was able to load data into my dimension table:

enter image description here


While you don't specify the specific problem related to loading your fact table, I'm going to assume the question approximates "how do I translate AppName to an AppKey so I can store that ID into my fact table?"

What you're looking to do is search your dimension table for a given value and return the key if it's found. Otherwise, we typically have a sentinel value to indicate this is an unknown value. Since you say you've loaded your dimension tables, then this shouldn't be an issue but it's a best practice.

If you are doing this through SSMS, then you would be writing a query that approximates the following

INSERT INTO dbo.Sentimental_Fact
,  AD.AppKey
,  D.Positive
,  D.Negative
,  D.Neutral
    dbo.kaggle_data AS D
        dbo.User_Rating_Dim AS URD
        -- I assume the appname in user rating is a typo and
        -- should be UserName
        ON URD.AppName = D.UserName
        dbo.App_Dim AS AD
        ON AD.AppName = D.AppName

If you were looking to use SSIS, I suggest you read the excellent Stairway to Integration Services by Andy Leonard. In short, you'll pull in in your base kaggle_data table into a Data Flow Task via an OLE DB Source Component and then use a series of Lookup Components to augment or enrich your data flow with the keys from the dimension tables. Finally, you'd land that into an OLE DB Destination (not an OLE DB Command)

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.