Forum Discussion
Creating Dimensions from a flat file
I have an aggregated flat file taken from a view on our sql database and i want to turn this into a star schema.
I work for an anti fraud software company.
Each row represents an aggregated view of our data that shows particular product, inception channel, reasons etc and shows the number of applications.
There are no 'keys' in this flat file - are there any particular best practices to turn this into a star schema - i.e do i have to generate keys myself via an index in power query or something? Would it better to 'unaggregate' my source data?
Thanks.
6 Replies
Can you provide some sample data so that can provide more suggestion for you,what the relationship among the fields such as one to many or many to many?
Best Regards!
Yolo Zhu
- Hi, do you need any additional info above the above data provided?
What the output you want to achieve? can you provide some sample output?
Best Regards!
Yolo Zhu
INDUSTRY_SECTOR CLIENT_NAME DOMAIN_NAME ENQUIRY_TYPE SOURCE_TYPE INCEPTION_CHANNEL PRODUCT_DESCRIPTION PRODUCT_INTERCOMPANY_TYPE_DESCRIPTION DISPLAYNAME National Reason Local Reason REASON_AGGREGATE REASON_AGGREGATE_TYPE PRODUCT_AGGREGATE REFERRED_BY_RULE REFERRED_TO_TASK LATEST_LOADED_MONTH LATEST_LOADED_QUARTER FIRST_LOADED_MONTH FIRST_LOADED_QUARTER LATEST_UPDATED_MONTH LATEST_UPDATED_QUARTER TOTAL_APPS Referred? PEER_GROUP Finance Client A Domain A FINANCE_APPLICATION REAL-TIME INTERNET Tradesperson Application Membership Application Suspect NULL Re-signed after being blacklisted - found by links on certain attributes NULL NULL NULL Y Y 01/12/2021 2021-Q4 01/11/2021 2021-Q4 01/12/2021 2021-Q4 1 Referred 24A Finance Client A Domain A FINANCE_APPLICATION REAL-TIME INTERNET Tradesperson Application Membership Application Suspect NULL Re-signed after being blacklisted - found by links on certain attributes NULL NULL NULL Y Y 01/08/2022 2022-Q3 01/08/2022 2022-Q3 01/09/2022 2022-Q3 1 Referred 24A Finance Client A Domain A FINANCE_APPLICATION REAL-TIME INTERNET Tradesperson Application Membership Application Suspect NULL Re-signed after being blacklisted - found by links on certain attributes NULL NULL NULL Y Y 01/12/2019 2019-Q4 01/09/2019 2019-Q3 01/09/2019 2019-Q3 1 Referred 24A Hi Anonymous thanks for the above. I have provided a sample of the data (5Mil rows in real world)
The file is how i recieve from our MI team but i don't have visibility of any of the keys to the different items so i'm unable to create a star schema from this fromat - do i have to makeshift create keys. all joins should be one to many and conventional - nothing unusual
