Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
umbrellabirdtom
Frequent Visitor

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 6
umbrellabirdtom
Frequent Visitor

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

umbrellabirdtom
Frequent Visitor

INDUSTRY_SECTORCLIENT_NAMEDOMAIN_NAMEENQUIRY_TYPESOURCE_TYPEINCEPTION_CHANNELPRODUCT_DESCRIPTIONPRODUCT_INTERCOMPANY_TYPE_DESCRIPTIONDISPLAYNAMENational ReasonLocal ReasonREASON_AGGREGATEREASON_AGGREGATE_TYPEPRODUCT_AGGREGATEREFERRED_BY_RULEREFERRED_TO_TASKLATEST_LOADED_MONTHLATEST_LOADED_QUARTERFIRST_LOADED_MONTHFIRST_LOADED_QUARTERLATEST_UPDATED_MONTHLATEST_UPDATED_QUARTERTOTAL_APPSReferred?PEER_GROUP
FinanceClient ADomain AFINANCE_APPLICATIONREAL-TIMEINTERNETTradesperson ApplicationMembership ApplicationSuspectNULLRe-signed after being blacklisted - found by links on certain attributesNULLNULLNULLYY01/12/20212021-Q401/11/20212021-Q401/12/20212021-Q41Referred24A
FinanceClient ADomain AFINANCE_APPLICATIONREAL-TIMEINTERNETTradesperson ApplicationMembership ApplicationSuspectNULLRe-signed after being blacklisted - found by links on certain attributesNULLNULLNULLYY01/08/20222022-Q301/08/20222022-Q301/09/20222022-Q31Referred24A
FinanceClient ADomain AFINANCE_APPLICATIONREAL-TIMEINTERNETTradesperson ApplicationMembership ApplicationSuspectNULLRe-signed after being blacklisted - found by links on certain attributesNULLNULLNULLYY01/12/20192019-Q401/09/20192019-Q301/09/20192019-Q31Referred24A
Anonymous
Not applicable

Hi @umbrellabirdtom 

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?
Anonymous
Not applicable

Hi @umbrellabirdtom 

What the output you want to achieve? can you provide some sample output?

 

Best Regards!

Yolo Zhu

 

I'd just like to know if I should bother creating a star schema from this flat file. So the expected output would be a star schema. I know the recommendation is to star schema but I currently have this very wide flat file - i want to know how I would go about creating 5the star schema from this data i have shared if poss

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.