cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thale
Helper I
Helper I

Build fact tables from RDBMS table

I want to let my Power BI sit on top of our SSAS in which we are designing cubes with facts and dimension. 

 

Base is an OLTP DB where we are going to extract all the data with the SSIS and then create facts and dimensions. In this case I have a registration table which looks like this:

 

iddate_reg_startprocess1_typedate_finished_process1process2_typedate_finished_process2date_reg_finished
125-05-2021type125-05-2021type128-05-202128-05-2021
226-05-2021type2328-05-2021type9  
327-05-2021type1    
427-05-2021     
5      

 

If someone start the registration the date_reg_start is set in the OLTP table. After that, if the user starts a type of process1 the type1 is set. If he finishes it, the date is set in the same row. the same procedure with process2. If process2 got completed, the registration_finish date will be set. 

 

So, how will be look like the fact tables based on this oltp-registration table?

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @thale ,

 

Based on my understanding, you can transform your data like below first:

 

process.PNG

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy1TUw1TUyMAJxSioLUnELWiAJInFidaKVjEBCZmiajIzRdYFELYG0AhiDNIKVmGOxTQEFg5SaoCtVwFQWCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date_reg_start = _t, process1_type = _t, date_finished_process1 = _t, process2_type = _t, date_finished_process2 = _t, date_reg_finished = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"id", "date_reg_start", "date_finished_process1", "date_finished_process2", "date_reg_finished"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"id", "date_reg_start", "date_reg_finished", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1","_type","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","date_finished_","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value1", "Custom", each if [Attribute] = [Attribute.1] then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Attribute.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "process"}, {"Value", "type"}, {"Value.1", "date_finished"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"date_reg_start", type date}, {"date_reg_finished", type date}, {"date_finished", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"id", Int64.Type}, {"date_reg_start", type date}, {"date_reg_finished", type date}, {"process", type text}, {"type", type text}, {"date_finished", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"id", "date_reg_start", "process", "type", "date_finished", "date_reg_finished"})
in
    #"Reordered Columns"

 

 Then, you can create a Date dimension table and process, process type dimension table according to your requirements.

 

If this is not what you want, please share me more details.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Icey
Community Support
Community Support

Hi @thale ,

 

Based on my understanding, you can transform your data like below first:

 

process.PNG

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy1TUw1TUyMAJxSioLUnELWiAJInFidaKVjEBCZmiajIzRdYFELYG0AhiDNIKVmGOxTQEFg5SaoCtVwFQWCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date_reg_start = _t, process1_type = _t, date_finished_process1 = _t, process2_type = _t, date_finished_process2 = _t, date_reg_finished = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"id", "date_reg_start", "date_finished_process1", "date_finished_process2", "date_reg_finished"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"id", "date_reg_start", "date_reg_finished", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1","_type","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","date_finished_","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value1", "Custom", each if [Attribute] = [Attribute.1] then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Attribute.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "process"}, {"Value", "type"}, {"Value.1", "date_finished"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns", {{"date_reg_start", type date}, {"date_reg_finished", type date}, {"date_finished", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"id", Int64.Type}, {"date_reg_start", type date}, {"date_reg_finished", type date}, {"process", type text}, {"type", type text}, {"date_finished", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"id", "date_reg_start", "process", "type", "date_finished", "date_reg_finished"})
in
    #"Reordered Columns"

 

 Then, you can create a Date dimension table and process, process type dimension table according to your requirements.

 

If this is not what you want, please share me more details.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps