Flat File to Star Schema in Power BI Desktop.

[Anonymous]

Hello Community,

Task: I have to create this Power BI Report (1st screenshot).

Background: Data Source is an exported Excel spreadsheet from Aconex and I got Business to send it in CSV just in case the file size increases in the future. I will automate Power BI Dataset refresh by using Microsoft Flow to pick on this source CSV as soon as a new file is posted on our SharePoint Online Site. This is an interim solution, however, the final solution is to get all the data both from Aconex and Primavera P6 (2nd screenshot) to Microsoft Azure for Power BI Reports to have refreshed data.

Question: I am planning to create a Star Schema (3rd screenshot) out of this Flat File per Patrick @ Power BI Tutorial | From Flat File To Data Model video.

Actions: I have transformed data in Flat File (4th, 5th & 6th screenshot) in Power Query. Currently, it is looking like screenshots of Power Query (7th - 12th screenshots).

Difficulty & Clarifications: I am finding difficulty in defining the Facts and various Dimension tables. Or if it is actually worth creating a Star Schema for this report!

Not sure if there will be a relationship that could be established between other tables once the other 2 reports come into picture data coming from a different source system (Primavera P6).

Can anyone please help/guide in solving this situation?

Thanks for your time in advance.

Kind regards, B

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Sorry for late reply, we can use the following measure in the table visual to meet the 1st screenshot.

Total =
DISTINCTCOUNT ( 'ConstructionProcedureCompletion'[Issue Number] )

No. Open =
CALCULATE (
    DISTINCTCOUNT ( 'ConstructionProcedureCompletion'[Issue Number] ),
    FILTER ( 'ConstructionProcedureCompletion', [Status] = "Open" )
)

No. Closed =
CALCULATE (
    DISTINCTCOUNT ( 'ConstructionProcedureCompletion'[Issue Number] ),
    FILTER ( 'ConstructionProcedureCompletion', [Status] = "Closed" )
)

% Closed =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'ConstructionProcedureCompletion'[Issue Number] ),
        FILTER ( 'ConstructionProcedureCompletion', [Status] = "Closed" )
    ),
    DISTINCTCOUNT ( 'ConstructionProcedureCompletion'[Issue Number] ),
    0
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared. BTW, please delete your screenshot if it contain any confidential information or it comes from the real data.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Anyone.. please!

Thanks, B

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Sorry for late reply, we can use the following measure in the table visual to meet the 1st screenshot.

Total =
DISTINCTCOUNT ( 'ConstructionProcedureCompletion'[Issue Number] )

No. Open =
CALCULATE (
    DISTINCTCOUNT ( 'ConstructionProcedureCompletion'[Issue Number] ),
    FILTER ( 'ConstructionProcedureCompletion', [Status] = "Open" )
)

No. Closed =
CALCULATE (
    DISTINCTCOUNT ( 'ConstructionProcedureCompletion'[Issue Number] ),
    FILTER ( 'ConstructionProcedureCompletion', [Status] = "Closed" )
)

% Closed =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'ConstructionProcedureCompletion'[Issue Number] ),
        FILTER ( 'ConstructionProcedureCompletion', [Status] = "Closed" )
    ),
    DISTINCTCOUNT ( 'ConstructionProcedureCompletion'[Issue Number] ),
    0
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared. BTW, please delete your screenshot if it contain any confidential information or it comes from the real data.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lid-msft

Thanks for your prompt response, greatly appreciated. I've got the report I was expecting and there is no confidential data since I blurred out the crucial parts of the screenshots.

Is there anything you would like to comment on the Star Schema for these reports?

Thanks for your time in advance.

Cheers, B

[Anonymous · Accepted solution]

Dear All,

Thought to share this with you in regards to my query as below:

Difficulty & Clarifications: I am finding difficulty in defining the Facts and various Dimension tables. Or if it is actually worth creating a Star Schema for this report!

Solution: After reading 'The Data Warehouse Toolkit - The Complete Guide to Dimensional Modeling by Ralph Kimball & Margy Ross (again after 15 years), I've realized that my Star Schema has a Factless Fact Tables wherein I had to keep 'Count of Issue Numbers' in the Fact Table along with Issue Numbers (FK) which are connected to Issue Dimension with Issue Numbers (PK). This solved my problem and is ticking all the boxes of best Star Schema principles.

If anyone wants any clarifications, please let me know and I am more than happy to share my thoughts.

Once again, thanks to you all for everything.

Kind regards, B

[Anonymous]

Anyone...? comment on how to do the best Star Schema for this simple Flat File..?

Regards, B