<# Genera el proyecto Power BI (.pbip) "Radiografia_Marmotech" Modelo (TMDL) con las 3 vistas de presidencia + DimCalendario + medidas DAX, y un reporte (PBIR) con 3 paginas. Abrir el .pbip en Power BI Desktop y Guardar como .pbix. Autor: jsoto · 2026-08-26 #> param( [string]$Root = "C:\Users\jsoto\Documents\NACE\proyectos\Fuentes\MARMOTECH\Dashboards MARMOTECH\Radiografia_Marmotech" ) $ErrorActionPreference = "Stop" $Name = "Radiografia_Marmotech" $SM = Join-Path $Root "$Name.SemanticModel" $RP = Join-Path $Root "$Name.Report" if(Test-Path $Root){ Remove-Item $Root -Recurse -Force } New-Item -ItemType Directory -Force -Path $Root,$SM,(Join-Path $SM "definition"),(Join-Path $SM "definition\tables"),$RP,(Join-Path $RP "definition"),(Join-Path $RP "definition\pages") | Out-Null function W($path,$content){ $dir = Split-Path $path -Parent if(!(Test-Path $dir)){ New-Item -ItemType Directory -Force -Path $dir | Out-Null } # UTF-8 sin BOM [System.IO.File]::WriteAllText($path, $content, (New-Object System.Text.UTF8Encoding($false))) } function G(){ [guid]::NewGuid().ToString() } $TAB = [char]9 # ---------------- .pbip ---------------- W (Join-Path $Root "$Name.pbip") @" { "version": "1.0", "artifacts": [ { "report": { "path": "$Name.Report" } } ], "settings": { "enableAutoRecovery": true } } "@ # ---------------- SemanticModel/.platform ---------------- W (Join-Path $SM ".platform") @" { "`$schema": "https://developer.microsoft.com/json-schemas/fabric/gitIntegration/platformProperties/2.0.0/schema.json", "metadata": { "type": "SemanticModel", "displayName": "$Name" }, "config": { "version": "2.0", "logicalId": "$(G)" } } "@ # ---------------- SemanticModel/definition.pbism ---------------- W (Join-Path $SM "definition.pbism") @" { "version": "4.0", "settings": {} } "@ # ---------------- database.tmdl ---------------- W (Join-Path $SM "definition\database.tmdl") ("database`n${TAB}compatibilityLevel: 1550`n") # ---------------- model.tmdl ---------------- $model = @() $model += "model Model" $model += "${TAB}culture: es-ES" $model += "${TAB}defaultPowerBIDataSourceVersion: powerBI_V3" $model += "${TAB}sourceQueryCulture: es-ES" $model += "" $model += "annotation PBI_QueryOrder = [`"vwPLMensual`",`"vwGastosDepto`",`"vwBalanceMensual`",`"DimCalendario`"]" $model += "" W (Join-Path $SM "definition\model.tmdl") (($model -join "`n") + "`n") # ---------------- helper: tabla desde vista SQL ---------------- function Table-Sql($tname,$cols){ # cols: array de "nombre|tipo" $l = @() $l += "table $tname" foreach($c in $cols){ $p = $c.Split("|"); $cn=$p[0]; $ct=$p[1] $l += "${TAB}column '$cn'" $l += "${TAB}${TAB}dataType: $ct" $l += "${TAB}${TAB}sourceColumn: $cn" if($ct -eq "dateTime"){ $l += "${TAB}${TAB}formatString: General Date" } $l += "${TAB}${TAB}summarizeBy: none" $l += "" } $l += "${TAB}partition $tname = m" $l += "${TAB}${TAB}mode: import" $l += "${TAB}${TAB}source =" $l += "${TAB}${TAB}${TAB}${TAB}let" $l += "${TAB}${TAB}${TAB}${TAB} Origen = Sql.Database(`"192.168.40.8`", `"MarmotechBI`")," $l += "${TAB}${TAB}${TAB}${TAB} Datos = Origen{[Schema=`"presidencia`", Item=`"$tname`"]}[Data]" $l += "${TAB}${TAB}${TAB}${TAB}in" $l += "${TAB}${TAB}${TAB}${TAB} Datos" $l += "" return ($l -join "`n") + "`n" } $plCols = @("ano|int64","numero_mes|int64","mes|string","AnoMes|string","fecha_mes|dateTime","grupo|string","orden_linea|int64","linea|string","subgrupo_cod|string","subgrupo_desc|string","cuenta|string","cuenta_desc|string","nivel|int64","debito|decimal","credito|decimal","resultado|decimal","costo_gasto|decimal") $gsCols = @("ano|int64","numero_mes|int64","mes|string","AnoMes|string","fecha_mes|dateTime","gerencia_cod|string","gerencia|string","departamento|int64","depto_nombre|string","subgrupo_cod|string","subgrupo_desc|string","cuenta|string","cuenta_desc|string","debito|decimal","credito|decimal","gasto|decimal") $blCols = @("ano|int64","numero_mes|int64","mes|string","AnoMes|string","fecha_mes|dateTime","grupo|string","grupo_desc|string","subgrupo_cod|string","subgrupo_desc|string","cuenta|string","cuenta_desc|string","movimiento_mes|decimal","saldo_natural|decimal","saldo_presentacion|decimal") # --- vwPLMensual (aloja las medidas) --- $pl = Table-Sql "vwPLMensual" $plCols # medidas $M = @() function Meas($n,$expr,$fmt){ $s = @() $s += "${TAB}measure '$n' = $expr" if($fmt){ $s += "${TAB}${TAB}formatString: $fmt" } $s += "" return ($s -join "`n") } $m0 = "`n" $m0 += Meas "Ventas Netas" "CALCULATE ( SUM ( vwPLMensual[resultado] ), vwPLMensual[grupo] = `"4`" )" "\`$ #,0,,`" M`";\`$ -#,0,,`" M`";\`$ 0" $m0 += Meas "Costo de Ventas" "CALCULATE ( SUM ( vwPLMensual[costo_gasto] ), vwPLMensual[grupo] = `"5`" )" "\`$ #,0,,`" M`"" $m0 += Meas "Gastos Operativos" "CALCULATE ( SUM ( vwPLMensual[costo_gasto] ), vwPLMensual[grupo] = `"7`" )" "\`$ #,0,,`" M`"" $m0 += Meas "Otros Ing. y Gastos" "CALCULATE ( SUM ( vwPLMensual[costo_gasto] ), vwPLMensual[linea] = `"Otros Ingresos y Gastos`" )" "\`$ #,0,,`" M`"" $m0 += Meas "ISR" "CALCULATE ( SUM ( vwPLMensual[costo_gasto] ), vwPLMensual[linea] = `"Impuesto sobre la Renta`" )" "\`$ #,0,,`" M`"" $m0 += Meas "Utilidad Bruta" "[Ventas Netas] - [Costo de Ventas]" "\`$ #,0,,`" M`"" $m0 += Meas "Utilidad Operativa" "[Utilidad Bruta] - [Gastos Operativos]" "\`$ #,0,,`" M`"" $m0 += Meas "Utilidad antes ISR" "[Utilidad Operativa] - [Otros Ing. y Gastos]" "\`$ #,0,,`" M`"" $m0 += Meas "Utilidad Neta" "SUM ( vwPLMensual[resultado] )" "\`$ #,0,,`" M`"" $m0 += Meas "Margen Bruto %" "DIVIDE ( [Utilidad Bruta], [Ventas Netas] )" "0.0%" $m0 += Meas "Margen Operativo %" "DIVIDE ( [Utilidad Operativa], [Ventas Netas] )" "0.0%" $m0 += Meas "Margen Neto %" "DIVIDE ( [Utilidad Neta], [Ventas Netas] )" "0.0%" $m0 += Meas "Costo % Ventas" "DIVIDE ( [Costo de Ventas], [Ventas Netas] )" "0.0%" $m0 += Meas "Gastos % Ventas" "DIVIDE ( [Gastos Operativos], [Ventas Netas] )" "0.0%" $m0 += Meas "Ventas AA" "CALCULATE ( [Ventas Netas], SAMEPERIODLASTYEAR ( DimCalendario[Date] ) )" "\`$ #,0,,`" M`"" $m0 += Meas "Utilidad Neta AA" "CALCULATE ( [Utilidad Neta], SAMEPERIODLASTYEAR ( DimCalendario[Date] ) )" "\`$ #,0,,`" M`"" $m0 += Meas "Ventas YoY %" "DIVIDE ( [Ventas Netas] - [Ventas AA], [Ventas AA] )" "0.0%" $m0 += Meas "Utilidad YoY %" "DIVIDE ( [Utilidad Neta] - [Utilidad Neta AA], [Utilidad Neta AA] )" "0.0%" $m0 += Meas "Ventas YTD" "TOTALYTD ( [Ventas Netas], DimCalendario[Date] )" "\`$ #,0,,`" M`"" $m0 += Meas "Utilidad Neta YTD" "TOTALYTD ( [Utilidad Neta], DimCalendario[Date] )" "\`$ #,0,,`" M`"" # medidas de gastos $m0 += Meas "Gasto Total" "SUM ( vwGastosDepto[gasto] )" "\`$ #,0,,`" M`"" $m0 += Meas "Gasto AA" "CALCULATE ( [Gasto Total], SAMEPERIODLASTYEAR ( DimCalendario[Date] ) )" "\`$ #,0,,`" M`"" $m0 += Meas "Gasto YoY %" "DIVIDE ( [Gasto Total] - [Gasto AA], [Gasto AA] )" "0.0%" $m0 += Meas "Gasto % de Ventas" "DIVIDE ( [Gasto Total], [Ventas Netas] )" "0.0%" # medidas de balance $m0 += Meas "Saldo (fin periodo)" "VAR u = MAX ( vwBalanceMensual[fecha_mes] ) RETURN CALCULATE ( SUM ( vwBalanceMensual[saldo_presentacion] ), vwBalanceMensual[fecha_mes] = u )" "\`$ #,0,,`" M`"" $m0 += Meas "Activos" "CALCULATE ( [Saldo (fin periodo)], vwBalanceMensual[grupo] = `"1`" )" "\`$ #,0,,`" M`"" $m0 += Meas "Pasivos" "CALCULATE ( [Saldo (fin periodo)], vwBalanceMensual[grupo] = `"2`" )" "\`$ #,0,,`" M`"" $m0 += Meas "Patrimonio" "CALCULATE ( [Saldo (fin periodo)], vwBalanceMensual[grupo] = `"3`" )" "\`$ #,0,,`" M`"" $m0 += Meas "Activo Corriente" "CALCULATE ( [Saldo (fin periodo)], vwBalanceMensual[subgrupo_cod] = `"11`" )" "\`$ #,0,,`" M`"" $m0 += Meas "Pasivo Corriente" "CALCULATE ( [Saldo (fin periodo)], vwBalanceMensual[subgrupo_cod] IN {`"20`",`"21`"} )" "\`$ #,0,,`" M`"" $m0 += Meas "Razon Corriente" "DIVIDE ( [Activo Corriente], [Pasivo Corriente] )" "0.00" $m0 += Meas "Capital de Trabajo" "[Activo Corriente] - [Pasivo Corriente]" "\`$ #,0,,`" M`"" $m0 += Meas "Endeudamiento %" "DIVIDE ( [Pasivos], [Activos] )" "0.0%" # insertar medidas antes de la particion (deben ir dentro de la tabla, antes de 'partition') $pl = $pl -replace "(?s)(${TAB}partition vwPLMensual = m)", ($m0 + "`$1") W (Join-Path $SM "definition\tables\vwPLMensual.tmdl") $pl W (Join-Path $SM "definition\tables\vwGastosDepto.tmdl") (Table-Sql "vwGastosDepto" $gsCols) W (Join-Path $SM "definition\tables\vwBalanceMensual.tmdl") (Table-Sql "vwBalanceMensual" $blCols) # ---------------- DimCalendario (tabla calculada) ---------------- $dc = @() $dc += "table DimCalendario" foreach($c in @("Date|dateTime","Anio|int64","MesNo|int64","Mes|string","AnioMes|string","PrimerDiaMes|dateTime","Trimestre|string")){ $p=$c.Split("|");$cn=$p[0];$ct=$p[1] $dc += "${TAB}column $cn" $dc += "${TAB}${TAB}dataType: $ct" $dc += "${TAB}${TAB}sourceColumn: [$cn]" if($ct -eq "dateTime"){ $dc += "${TAB}${TAB}formatString: General Date" } $dc += "${TAB}${TAB}summarizeBy: none" $dc += "" } $dax = 'ADDCOLUMNS ( CALENDAR ( DATE ( 2015, 1, 1 ), DATE ( 2028, 12, 31 ) ), "Anio", YEAR ( [Date] ), "MesNo", MONTH ( [Date] ), "Mes", FORMAT ( [Date], "MMM" ), "AnioMes", FORMAT ( [Date], "yyyy-MM" ), "PrimerDiaMes", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ), "Trimestre", "T" & FORMAT ( [Date], "Q" ) )' $dc += "${TAB}partition DimCalendario = calculated" $dc += "${TAB}${TAB}mode: import" $dc += "${TAB}${TAB}source = $dax" $dc += "" W (Join-Path $SM "definition\tables\DimCalendario.tmdl") (($dc -join "`n") + "`n") # ---------------- relationships.tmdl ---------------- $rel = @() foreach($t in @("vwPLMensual","vwGastosDepto","vwBalanceMensual")){ $rel += "relationship $(G)" $rel += "${TAB}fromColumn: $t.fecha_mes" $rel += "${TAB}toColumn: DimCalendario.Date" $rel += "" } W (Join-Path $SM "definition\relationships.tmdl") (($rel -join "`n") + "`n") Write-Output "SemanticModel escrito." # ===================================================================== # REPORTE (PBIR) # ===================================================================== W (Join-Path $RP ".platform") @" { "`$schema": "https://developer.microsoft.com/json-schemas/fabric/gitIntegration/platformProperties/2.0.0/schema.json", "metadata": { "type": "Report", "displayName": "$Name" }, "config": { "version": "2.0", "logicalId": "$(G)" } } "@ W (Join-Path $RP "definition.pbir") @" { "version": "4.0", "datasetReference": { "byPath": { "path": "../$Name.SemanticModel" } } } "@ # PBIR exige version.json dentro de definition/ W (Join-Path $RP "definition\version.json") @" { "version": "2.0.0" } "@ W (Join-Path $RP "definition\report.json") @" { "`$schema": "https://developer.microsoft.com/json-schemas/fabric/item/report/definition/report/1.0.0/schema.json", "themeCollection": { "baseTheme": { "name": "CY24SU10" } }, "layoutOptimization": "None" } "@ # ---- helpers de visuales PBIR ---- function Field-Measure($entity,$prop){ return @" { "field": { "Measure": { "Expression": { "SourceRef": { "Entity": "$entity" } }, "Property": "$prop" } }, "queryRef": "$entity.$prop", "nativeQueryRef": "$prop" } "@ } function Field-Column($entity,$prop){ return @" { "field": { "Column": { "Expression": { "SourceRef": { "Entity": "$entity" } }, "Property": "$prop" } }, "queryRef": "$entity.$prop", "nativeQueryRef": "$prop" } "@ } # roles: hashtable rol -> array de proyecciones json function Visual($pageDir,$vtype,$x,$y,$w,$h,$roles,$title){ $vid = G $vdir = Join-Path $pageDir "visuals\$vid" New-Item -ItemType Directory -Force -Path $vdir | Out-Null $projBlocks = @() foreach($r in $roles.Keys){ $items = ($roles[$r] -join ",`n") $projBlocks += "`"$r`": { `"projections`": [ $items ] }" } $queryState = ($projBlocks -join ",`n") $titleObj = "" if($title){ $titleObj = @" , "objects": { "title": [ { "properties": { "text": { "expr": { "Literal": { "Value": "'$title'" } } }, "show": { "expr": { "Literal": { "Value": "true" } } } } } ] } "@ } $json = @" { "`$schema": "https://developer.microsoft.com/json-schemas/fabric/item/report/definition/visualContainer/1.0.0/schema.json", "name": "$vid", "position": { "x": $x, "y": $y, "z": 0, "width": $w, "height": $h, "tabOrder": 0 }, "visual": { "visualType": "$vtype", "query": { "queryState": { $queryState } }, "drillFilterOtherVisuals": true$titleObj } } "@ W (Join-Path $vdir "visual.json") $json return $vid } $pageIds = @() # ============ PAGINA 1: Radiografia ============ $p1 = G; $pageIds += $p1 $p1dir = Join-Path $RP "definition\pages\$p1" New-Item -ItemType Directory -Force -Path $p1dir | Out-Null W (Join-Path $p1dir "page.json") @" { "`$schema": "https://developer.microsoft.com/json-schemas/fabric/item/report/definition/page/1.0.0/schema.json", "name": "$p1", "displayName": "Radiografia", "displayOption": "FitToPage", "height": 720, "width": 1280 } "@ [void](Visual $p1dir "card" 16 16 280 140 @{ "Values" = @((Field-Measure "vwPLMensual" "Ventas Netas")) } "Ventas Netas") [void](Visual $p1dir "card" 312 16 280 140 @{ "Values" = @((Field-Measure "vwPLMensual" "Utilidad Bruta")) } "Utilidad Bruta") [void](Visual $p1dir "card" 688 16 280 140 @{ "Values" = @((Field-Measure "vwPLMensual" "Utilidad Operativa")) } "Utilidad Operativa") [void](Visual $p1dir "card" 984 16 280 140 @{ "Values" = @((Field-Measure "vwPLMensual" "Utilidad Neta")) } "Utilidad Neta") [void](Visual $p1dir "waterfallChart" 16 170 620 300 @{ "Category" = @((Field-Column "vwPLMensual" "linea")); "Y" = @((Field-Measure "vwPLMensual" "Utilidad Neta")) } "Estado de Resultados (cascada)") [void](Visual $p1dir "lineClusteredColumnComboChart" 652 170 612 300 @{ "Category" = @((Field-Column "DimCalendario" "Mes")); "Y" = @((Field-Measure "vwPLMensual" "Ventas Netas")); "Y2" = @((Field-Measure "vwPLMensual" "Utilidad Neta")) } "Comportamiento del periodo (Ventas vs Utilidad)") [void](Visual $p1dir "clusteredColumnChart" 16 484 620 220 @{ "Category" = @((Field-Column "DimCalendario" "Anio")); "Y" = @((Field-Measure "vwPLMensual" "Ventas Netas"),(Field-Measure "vwPLMensual" "Utilidad Neta")) } "Trayectoria anual") [void](Visual $p1dir "slicer" 652 484 300 120 @{ "Values" = @((Field-Column "DimCalendario" "Anio")) } "Ano") # ============ PAGINA 2: Gastos ============ $p2 = G; $pageIds += $p2 $p2dir = Join-Path $RP "definition\pages\$p2" New-Item -ItemType Directory -Force -Path $p2dir | Out-Null W (Join-Path $p2dir "page.json") @" { "`$schema": "https://developer.microsoft.com/json-schemas/fabric/item/report/definition/page/1.0.0/schema.json", "name": "$p2", "displayName": "Analisis de Gastos", "displayOption": "FitToPage", "height": 720, "width": 1280 } "@ [void](Visual $p2dir "card" 16 16 300 140 @{ "Values" = @((Field-Measure "vwPLMensual" "Gasto Total")) } "Gastos Operativos") [void](Visual $p2dir "card" 332 16 300 140 @{ "Values" = @((Field-Measure "vwPLMensual" "Gasto % de Ventas")) } "Gasto / Ventas") [void](Visual $p2dir "barChart" 16 170 620 300 @{ "Category" = @((Field-Column "vwGastosDepto" "gerencia")); "Y" = @((Field-Measure "vwPLMensual" "Gasto Total")) } "Gasto por gerencia") [void](Visual $p2dir "tableEx" 652 170 612 300 @{ "Values" = @((Field-Column "vwGastosDepto" "subgrupo_desc"),(Field-Measure "vwPLMensual" "Gasto Total"),(Field-Measure "vwPLMensual" "Gasto % de Ventas")) } "Naturaleza del gasto") [void](Visual $p2dir "columnChart" 16 484 1248 220 @{ "Category" = @((Field-Column "DimCalendario" "Mes")); "Y" = @((Field-Measure "vwPLMensual" "Gasto Total")) } "Gasto mensual") # ============ PAGINA 3: Estructura ============ $p3 = G; $pageIds += $p3 $p3dir = Join-Path $RP "definition\pages\$p3" New-Item -ItemType Directory -Force -Path $p3dir | Out-Null W (Join-Path $p3dir "page.json") @" { "`$schema": "https://developer.microsoft.com/json-schemas/fabric/item/report/definition/page/1.0.0/schema.json", "name": "$p3", "displayName": "Estructura Financiera", "displayOption": "FitToPage", "height": 720, "width": 1280 } "@ [void](Visual $p3dir "card" 16 16 300 140 @{ "Values" = @((Field-Measure "vwPLMensual" "Activos")) } "Activos Totales") [void](Visual $p3dir "card" 332 16 300 140 @{ "Values" = @((Field-Measure "vwPLMensual" "Razon Corriente")) } "Razon Corriente") [void](Visual $p3dir "card" 648 16 300 140 @{ "Values" = @((Field-Measure "vwPLMensual" "Capital de Trabajo")) } "Capital de Trabajo") [void](Visual $p3dir "card" 964 16 300 140 @{ "Values" = @((Field-Measure "vwPLMensual" "Endeudamiento %")) } "Endeudamiento") [void](Visual $p3dir "clusteredColumnChart" 16 170 620 300 @{ "Category" = @((Field-Column "vwBalanceMensual" "grupo_desc")); "Y" = @((Field-Measure "vwPLMensual" "Saldo (fin periodo)")) } "Activos / Pasivos / Patrimonio") [void](Visual $p3dir "tableEx" 652 170 612 300 @{ "Values" = @((Field-Column "vwBalanceMensual" "subgrupo_desc"),(Field-Measure "vwPLMensual" "Saldo (fin periodo)")) } "Composicion") # ---------------- pages.json ---------------- $order = ($pageIds | ForEach-Object { "`"$_`"" }) -join ", " W (Join-Path $RP "definition\pages\pages.json") @" { "`$schema": "https://developer.microsoft.com/json-schemas/fabric/item/report/definition/pagesMetadata/1.0.0/schema.json", "pageOrder": [ $order ], "activePageName": "$($pageIds[0])" } "@ Write-Output "Reporte escrito." Write-Output ("PROYECTO GENERADO EN: " + $Root) Get-ChildItem -Recurse $Root | Measure-Object | ForEach-Object { Write-Output ("Archivos: " + $_.Count) }