extractModelFromPBIX.fsx

let fn = @"C:\Enter\Your\Filename\Here.pbix"

open System.Text.RegularExpressions
#if INTERACTIVE
#r @"System.IO.Compression"
#r @"System.IO.Compression.FileSystem"
#endif
open System
open System.IO
open System.IO.Compression
open System.Diagnostics
let tmpDir = Environment.GetEnvironmentVariable("TEMP")
let userName = Environment.GetEnvironmentVariable("USERNAME")
printfn "Running as:%s" userName  
let inline isNull value = obj.ReferenceEquals(value, null)
let inline isDBNull value = obj.ReferenceEquals(value, DBNull.Value)
let inline isNullish value = isNull value || isDBNull value

//let tmpMashup8 = sprintf "%s/pbiMashup.zip8" tmpDir 
let tmpMashup = sprintf "%s\\DataMashup" tmpDir 
let tmpModelDir = sprintf "%s\\pbimodel" tmpDir 
try IO.Directory.CreateDirectory(tmpModelDir) |> ignore with | _ -> ()

let parseMLine (ln:string) =
   //let rx1 = Regex("Source\s*=\s*([^(]+)\(("[^\"]) ")
    if ln.Contains(" = let") then 
       printfn "\r\n\r\n-- %s" ln
    else
       let rx2 = Regex("\[Query=\"(.*?)\"\]")
       let matches = rx2.Matches(ln)
       if matches.Count > 0 then
            let cap = matches.[0].Groups.[1].Captures.[0]
            let c2 = cap.Value.Replace("\"\"","\"").Replace("#(lf)","\n").Replace("#(cr)","\r").Replace("#(tab)","\t")
            printfn "%s" c2

// parseMLine """shared #"Unified Geography" = let
//     Source = AnalysisServices.Database("GCMPBIGateway.redmond.corp.microsoft.com", "GCM", [Query="EVALUATE SUMMARIZECOLUMNS(#(lf) 'Unified Geography'[Unified Geography SID]#(lf),'Unified Geography'[Time Zone]#(lf),'Unified Geography'[Area]#(lf),'Unified Geography'[Region]#(lf),'Unified Geography'[Sales Location]#(lf)#(lf),FILTER('Unified Geography','Unified Geography'[Unified Geography SID] <> Blank())#(lf),FILTER('Unified Geography','Unified Geography'[Time Zone] <> Blank())#(lf))"]),"""

let extractModel (datamodel:ZipArchiveEntry) =
    printfn "Calling ExtractToFile(%s)" tmpMashup
    datamodel.ExtractToFile(tmpMashup,true)
    
    // printfn "%s" datamodel.FullName
    // let dm = datamodel.Open()
    // let mash = new IO.FileStream(tmpMashup, FileMode.Create)
    // let buf:byte array = Array.zeroCreate 8192 
    // let mutable bytesRead = 1 // do not skip 8 bytes dm.Read(buf, 0, 8) 
    // while bytesRead > 0 do
    //     bytesRead <- dm.Read(buf, 0, buf.Length)
    //     if bytesRead > 0 then
    //         mash.Write(buf, 0, bytesRead)
    //         //printf "%d," bytesRead
    // dm.Close()
    // mash.Close()
    
    // this seems not to work:
    //ZipFile.ExtractToDirectory(tmpMashup, tmpModelDir) 
    // so I'm using 7z instead, which seems to work with some warnings about data at end of archive:
    let cmd = @"C:\Program Files\7-Zip\7z.exe"
    let args = sprintf @"x ""%s"" -o""%s"" -y" tmpMashup tmpModelDir 
    printfn "\"%s\" %s" cmd args
    let pi = new System.Diagnostics.Process()
    let si =  System.Diagnostics.ProcessStartInfo(cmd, args)
    si.UseShellExecute <- false
    si.RedirectStandardError <- true
    si.RedirectStandardOutput <- true
    pi.StartInfo <- si
    let printDataReceived (args:DataReceivedEventArgs) = 
        Console.Write args.Data
        Console.Out.Flush()
    pi.ErrorDataReceived.Add  printDataReceived
    pi.OutputDataReceived.Add printDataReceived
    let isStarted = pi.Start()
    printfn "isStarted=%A" isStarted
    pi.WaitForExit()
    // while not <| pi.HasExited do
    //     if pi.StandardOutput.BaseStream.CanRead then
    //         printfn "%s" <| pi.StandardOutput.ReadToEnd()    
    //     if pi.StandardError.BaseStream.CanRead then
    //         printfn "%s" <| pi.StandardError.ReadToEnd() 
    //     System.Threading.Thread.Sleep(10)
    printfn "ExitCode=%d" <| pi.ExitCode 
    let filesToFind = sprintf "%s\\Formulas" tmpModelDir
    printfn "filesToFind:%s" <| filesToFind
    for fn in IO.Directory.EnumerateFiles(filesToFind) do
        if fn.EndsWith(".m") then
            printfn "-- file:%s" <| fn
            let lines = File.ReadAllLines(fn)
            lines |> Array.iter (fun s -> 
                // printfn "-- %s" s
                parseMLine s )
    Console.Out.Flush()
    Console.Error.Flush()
       
let arch = ZipFile.Open(fn, ZipArchiveMode.Read, Text.Encoding.Default)
arch.Entries 
|> Seq.filter (fun x -> String.Compare( x.FullName, "DataMashup", StringComparison.OrdinalIgnoreCase) = 0)
|> Seq.iter extractModel