Creating Dimensions from a flat file

[umbrellabirdtom]

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.

[umbrellabirdtom]

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]

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

[Anonymous]

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

[umbrellabirdtom]

Hi, do you need any additional info above the above data provided?

[Anonymous]

Hi @umbrellabirdtom 

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

Best Regards!

Yolo Zhu

[umbrellabirdtom]

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