Skip to content

Instantly share code, notes, and snippets.

@SqlWaldorf
Last active June 19, 2024 10:21
Show Gist options
  • Save SqlWaldorf/4505037fa659cbd998c47b770ebefe53 to your computer and use it in GitHub Desktop.
Save SqlWaldorf/4505037fa659cbd998c47b770ebefe53 to your computer and use it in GitHub Desktop.
This script uses graphviz to create diagrams from a Power BI project
# Code provided "as-is". Use at your own risk
#
# Requires graphviz python package:
# pip install graphviz
#
# Unless you run with the -s option, you must also install the graphviz software from
# https://www.graphviz.org/download/
#
# Basic use: python CreateDiagram.py <path to folder with .SemanticModel>
#
# For advanced options: python CreateDiagram.py -h
#
import json
import graphviz
import argparse
import os
def load_diagrams (basefolder):
diagrams_folder = basefolder + "/diagramLayout.json"
f_diagrams = open(diagrams_folder)
diagrams = json.load(f_diagrams)
diags = {}
for it in diagrams["diagrams"]:
name = it["name"]
nodes = it["nodes"]
tables = [node["nodeIndex"] for node in nodes]
diags[name] =tables
return diags
def load_table (basefolder, tablename):
table_file = f"{basefolder}/definition/tables/{tablename}.tmdl"
tabletmdl = open(table_file).readlines()
collist = []
for l in tabletmdl:
if l.strip().startswith("column "):
if l.rfind("=") > -1:
# calculated column with formula, only name is copied
collist.append(l[l.find("column")+6:l.rfind("=")].strip())
else:
collist.append(l[l.find("column")+6:].strip())
return collist
def load_relationships (basefolder):
relationship_folder = basefolder + "/definition/relationships.tmdl"
f_relationships = open(relationship_folder)
relationships = f_relationships.readlines()
rellist = []
isactive = True
crossfilter = False
tomany = False
for l in relationships:
if l.lstrip().startswith("crossFilteringBehavior: bothDirections"):
crossfilter = True
if l.lstrip().startswith("toCardinality: many"):
tomany = True
if l.lstrip().startswith("isActive: false"):
isactive = False
if l.lstrip().startswith("fromColumn:"):
starttable = l[l.find(": ")+2:l.find(".")].strip()
startcol = l[l.find(".")+1:].strip()
if l.lstrip().startswith("toColumn:"):
endtable = l[l.find(": ")+2:l.find(".")].strip()
endcol = l[l.find(".")+1:].strip()
rellist.append((starttable,startcol,endtable, endcol,isactive,crossfilter,tomany))
isactive = True
crossfilter = False
tomany = False
return rellist
parser=argparse.ArgumentParser(prog="CreateDiagram", description="Create simple diagrams from Power BI Project folder.\nProvide the path to the .SemanticModel folder, and optionally choose the desired graphic format.")
parser.add_argument("folder", help="Path to the <projectname>.SemanticModel folder of your Power BI Project. This folder is normally in the same folder where the .pbip file is stored.")
parser.add_argument("-f", "--format", default="svg", help="Graphic render format (gif, png, jpg, pdf, bmp, tif, webp, svg), defaults to svg")
parser.add_argument("-s", "--skiprender", help="Skip rendering the diagrams", action='store_true')
parser.add_argument("-r","--renderfolder", default=os.getcwd(), help=f"Folder where output files are produced. Defaults to {os.getcwd()}")
parser.add_argument("-m","--onlymain", action='store_true', help="Skip generating subdiagrams")
parser.add_argument("-i","--inactive", action='store_true', help="Also include inactive relationships in the diagram")
parser.add_argument("-b", "--generatebus", action="store_true", help="Generate a csv file with a data warehouse bus, using heuristics to determine facts and dimensions")
parser.add_argument("-c", "--showcardinality", action="store_true", help="Display cardinality on edges")
args=parser.parse_args()
folderpath = args.folder.replace("\\","/").rstrip("/")
if folderpath.find(".SemanticModel") == -1:
print(f"Failed to find the .SemanticModel folder, you provided as folder: {args.folder}")
exit()
projectname = folderpath[folderpath.rfind("/")+1:folderpath.rfind(".")]
rels = load_relationships(args.folder)
diags = load_diagrams(args.folder)
tableinfo = dict()
for t in diags['All tables']:
tableinfo[t] = load_table(args.folder, t)
# print(rels)
# exit()
# Rendering with dot -- needs to be installed in OS
graphattributes = dict()
graphattributes["overlap"] = "false"
graphattributes["ratio"] = "0.56"
graphattributes["comment"] = "Basic model for implicit measures"
graphattributes["layout"] = "neato"
graphattributes["splines"] = "curved"
nodeattributes = dict()
nodeattributes["shape"] = "box"
edgeattributes = dict()
edgeattributes["len"] = ("2" if args.showcardinality else "1.2")
edgeattributes["labeldistance"] = "1.8"
edgeattributes["labelangle"] = "20"
if args.generatebus:
facts = set()
for (starttable, startcol,endtable,endcol,isactive,crossfilter,tomany) in rels:
facts.add(starttable)
for (starttable, startcol,endtable,endcol,isactive,crossfilter,tomany) in rels:
if (endtable in facts) and (not tomany):
facts.remove(endtable)
dims = set()
for (starttable, startcol,endtable,endcol,isactive,crossfilter,tomany) in rels:
if starttable in facts:
dims.add(endtable)
bus = [["" for x in dims] for y in facts]
factlist = list(facts)
dimlist = list(dims)
for (starttable, startcol,endtable,endcol,isactive,crossfilter,tomany) in rels:
if (starttable in facts) and (endtable in dims) and isactive:
bus[factlist.index(starttable)][dimlist.index(endtable)] = ("m" if tomany else "1")
busfile = open(f"{args.renderfolder}/{projectname}_dwhbus.csv","w")
busfile.write(f"Facts,{",".join(dimlist)}\n")
for f in factlist:
busfile.write(f"{f},{",".join(bus[factlist.index(f)])}\n")
busfile.close()
if args.onlymain:
diaglist = ['All tables']
else:
diaglist = [d for d in diags]
for diagram in diaglist:
dot = graphviz.Digraph(f"Power BI diagram {diagram}", engine="neato",graph_attr=graphattributes,node_attr=nodeattributes, edge_attr=edgeattributes)
#consider nop2 for fixed locations (with pos)
for (starttable, startcol,endtable,endcol,isactive,crossfilter,tomany) in rels:
starttable = starttable.replace("'","")
endtable = endtable.replace("'","")
if (starttable in diags[diagram]) and (endtable in diags[diagram]):
dir = ("both" if crossfilter else "forward")
headlabel = taillabel = ""
if args.showcardinality:
taillabel = ("*" if tomany else "1")
headlabel = "*"
if isactive:
dot.edge(tail_name=endtable, head_name=starttable,dir=dir,headlabel=headlabel,taillabel=taillabel)
elif args.inactive: # show inactive relationships with dashed edge
dot.edge(tail_name=endtable, head_name=starttable,dir=dir, style="dashed",headlabel=headlabel,taillabel=taillabel)
if args.skiprender:
dot.save(f"{args.renderfolder}/{projectname}_{diagram}.gv")
else:
dot.render(format=args.format, view=False, outfile=f"{args.renderfolder}/{projectname}_{diagram}.{args.format}")
@SqlWaldorf
Copy link
Author

More details on why and how to use it on linkedin: https://www.linkedin.com/pulse/exporting-power-bi-diagrams-nico-jacobs-esdge/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment