GeneratePbiImportModelQueriesFromDataSource.fsx

// GeneratePbiImportModelQueriesFromDataSource.fsx

// Outline:
// Query tabular model metadata for all tables
// Build queries with DAX SUMMARIZECOLUMNS and PowerQuery's Table.RenameColumns
// open text file in notepad for user to paste from (and add FILTERs as desired)

// Usage: edit the two lines below, then run:
let tabServer = "SSAStabularServerName"
let tabDb = "SSAScatalog"
let generateSummarizeColumns = true
let tabConnStr = sprintf "Data Source=%s;Catalog=%s" tabServer tabDb 

#r "Microsoft.AnalysisServices.AdomdClient.dll"
open System
open System.Text
open Microsoft.AnalysisServices.AdomdClient
let tabConn = new AdomdConnection(tabConnStr)
tabConn.Open()

let genQuery (tn1:string) =
    let tn = tn1.Replace("$","")
    let tn_ = tn.Replace(" ", "_")
    let tnq = sprintf "'%s'" tn
    use tc = new AdomdCommand(sprintf "EVALUATE SAMPLE(0,'%s',0)" tn, tabConn)
    let rs = tc.ExecuteReader()
    let sb = StringBuilder()
    let fldNames = [| for j = 0 to rs.FieldCount - 1 do
                      yield rs.GetName(j)  |]
    let fixForSummarize (f:string) = 
        let plb = f.IndexOf("[")
        sprintf "'%s'%s" (f.Substring(0, plb)) (f.Substring(plb))  
    let fldNamesForSummarize = 
        fldNames |> Array.map fixForSummarize
    rs.Close()
    fldNames |> String.concat "," |> sb.Append |> ignore   
    //printfn "%s=%s" tn <| sb.ToString()
    let q = 
        if generateSummarizeColumns then
             sprintf "EVALUATE SUMMARIZECOLUMNS(%s, FILTER(%s, %s <> BLANK()) )" 
                (String.Join(",", fldNamesForSummarize))
                tnq fldNamesForSummarize.[0]
        else sprintf "EVALUATE FILTER(%s, %s <> BLANK())" tnq fldNamesForSummarize.[0]  
    let fldRename (f:string) = 
        let plb = f.IndexOf("[")
        let prb = f.LastIndexOf("]")
        let renamed = if plb >= 0 && prb > 0 then f.Substring(plb + 1, prb-plb - 1) else f 
        sprintf """{"%s", "%s"}""" f renamed
    let fieldRenames = Array.map fldRename fldNames
    let sFieldRenames = System.String.Join(",\r\n\t", fieldRenames)  
    let res = sprintf "// %s \r\nlet \r
      %s0 = AnalysisServices.Database(\"%s\", \"%s\", [Query=\"%s\"]), \r
      %s1 = Table.RenameColumns(%s0,{\r\n%s}) \r
      in %s1 \r\n" q tn_ tabServer tabDb q tn_ tn_ sFieldRenames tn_
    printf "%s" res
    res

let tabC1 =  new AdomdCommand("
 SELECT TABLE_NAME
 FROM $SYSTEM.DBSCHEMA_TABLES
 WHERE TABLE_TYPE='TABLE'  ", tabConn)
let r1 = tabC1.ExecuteReader()
let tabTables = [| while r1.Read() do yield r1.GetString(0) |]
r1.Close()
let tmpDir = seq { yield @"C:\temp" 
                   yield Environment.GetEnvironmentVariable("TEMP")
                   yield Environment.GetEnvironmentVariable("TMP")
                   yield Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\\TEMP"
                   yield @"C:\Windows\Temp"
                   yield "."
                 } |> Seq.find  IO.Directory.Exists 
let outputFileName = sprintf "%s\\%s_%s.m.txt" tmpDir tabServer tabDb 
let outFile = new IO.StreamWriter(outputFileName)
for t in tabTables do 
    genQuery t |> outFile.WriteLine
    outFile.Flush()
 
outFile.Close()
tabConn.Close()

printfn "now open %s" outputFileName
System.Diagnostics.Process.Start(outputFileName) |> ignore