CreateStarSchema

/*
Function: CreateStarSchema

This function creates a star schema data model for analyzing Dataverse standard tables.

Parameters:
- factTableName (text): Name of the fact table.
- factTableColumns (list): List of columns in the fact table.
- dimensionTables (list): List of dimension table names.
- dimensionTableColumns (list): List of lists, each containing column names for the corresponding dimension table.

Return Type: table

Exceptions: 
- Validation check for fact table and dimension table names.

Example:
let
    starSchema = CreateStarSchema("Sales", {"DateKey", "ProductKey", "CustomerKey", "Amount"}, 
                    {"Date", "Product", "Customer"}, 
                    {{"DateKey", "DateName", "DateCategory"}, 
                     {"ProductKey", "ProductName", "ProductCategory"}, 
                     {"CustomerKey", "CustomerName", "CustomerRegion"}}
                )
in
    starSchema
*/
CreateStarSchema = (factTableName as text, 
                    factTableColumns as list, 
                    dimensionTables as list, 
                    dimensionTableColumns as list) =>
                    
    let
        // Validation checks for table names
        validateFactTable = if Text.Length(factTableName) > 0 then factTableName else error "Fact table name is invalid",
        validateDimensionTables = List.Transform(dimensionTables, each if Text.Length(_) > 0 then _ else error "Dimension table name is invalid"),
        
        // Create fact table
        factTable = Table.FromList({factTableColumns}, Splitter.SplitByNothing(), {"FactColumn"}),

        // Create dimension tables
        dimensionTableList = List.Transform({factTableColumns} & dimensionTableColumns, each Table.FromList({_}, Splitter.SplitByNothing())),
        
        // Combine fact table and dimension tables
        starSchema = Table.Combine({factTable} & dimensionTableList)
    in
        starSchema
in
    CreateStarSchema

Example of usage:

let
    starSchema = CreateStarSchema("Sales", {"DateKey", "ProductKey", "CustomerKey", "Amount"}, 
                    {"Date", "Product", "Customer"}, 
                    {{"DateKey", "DateName", "DateCategory"}, 
                     {"ProductKey", "ProductName", "ProductCategory"}, 
                     {"CustomerKey", "CustomerName", "CustomerRegion"}}
                )
in
    starSchema

In this example, the CreateStarSchema function is used to create a star schema data model for analyzing sales data. The fact table is named "Sales" with columns DateKey, ProductKey, CustomerKey, and Amount. There are three dimension tables: Date, Product, and Customer, with their respective columns. The function returns the combined star schema table for further analysis.