[Question] Documenting a Model

[Daandamhuis]

Hi,

I was looking into the PyTabular package, and first of thanks, finally a worthy implementation! On top of this package, I started "building" a Documenter for our models, because we use Docusaurus with generated markdown files to create our documentation site, which users can use to see how things are calculated for instance.

It seems a bit "strange" to build it on top of the PyTabular package, instead of including it in the package. Is this something you have envisioned for PyTabular? Otherwise, I'll continue building it on top of PyTabular.

Simple Example

Thanks!

[Curts0]

@Daandamhuis I love this idea. It would be great for a user to be able to run a simple command in their CICD to document the model. Would love to see what you got, or feel free to go ahead and pull request some items.

The example below should be pretty easy to get with PyTabular. I've started wrapping all the basic Tabular model objects (Tables, Columns, Measures, etc) with a PyObject class, that way I can write my own methods, but also take advantage of the methods and attributes already built in the .net library

import pytabular as p
model = Tabular(CONNECTION_STR)

#Below will get you the measure in question
measure = model.Measures[MEASURE_NAME]

#You then have access to what's in the .net library
measure.Name
measure.Description
measure.IsHidden

#But because it's a PyObject I can start to wrap my own methods to it.
For example:
#Performs a DAX query to do a simple Row Count on Table
model.Tables[TABLE_NAME].Row_Count()

#Or get Distinct Count of Column
model.Tables[TABLE_NAME].Columns[COLUMN_NAME].Distinct_Count()

I'm sure I've got bugs hiding, working to fix them in my free time plus adding to the documentation.

[Daandamhuis]

Thanks for the info, just found the time to start working on this. Is there a way to also retrieve the translations in an easy way?

[Daandamhuis]

from pytabular.measure import PyMeasure

def create_markdown_for_measure(object: PyMeasure):
    return f"""
### {object.Name} 
<dl>
  <dt>Description</dt>
  <dd>{object.Description}</dd>

  <dt>Display Folder</dt>
  <dd>{object.DisplayFolder}</dd>

  <dt>Format String</dt>
  <dd>{object.FormatString}</dd>

  <dt>Is Hidden</dt>
  <dd>{object.IsHidden}</dd>
  
</dl>

```dax title="Technical: {object.Name}"
{
    object.Expression
}

---

Output

msr_trns_multiple_sales_perc

Description

Display Folder

[m] Sales Performance

Format String

0.0%;-0.0%;0.0%

Is Hidden

False

VAR Receipts =
    DISTINCTCOUNT ( 'Transactions'[OrderNumber] )
VAR Result =
    COUNTAX (
        FILTER (
            SUMMARIZE (
                'Transactions',
                'Transactions'[OrderNumber],
                "Lines", [msr_trns_transaction_orderlines]
            ),
            [Lines] > 1
        ),
        [OrderNumber]
    )
RETURN
    DIVIDE ( Result, Receipts )

---

[Daandamhuis]

Cultures

I've been messing around with Cultures, and this would help me generate the docs. Is it possible to implement this? I tried to use the setup you created with the Tables and tried to recreate it with the so-called "PyCulture" and "PyObjectTranslation".

I don't know if this they it should be handled, but I've made a PR for feedback.

for culture in model.Cultures:
    for object in culture.ObjectTranslations:
        if object.Object.Name == 'msr_cs_avg_resolution_time_wh':
            """
            INFO     en-US > Measure > Table > msr_cs_avg_resolution_time_wh > Measure > DisplayFolder > [m] Customer Services                                                                                                                   main.py:20
            INFO     en-US > Measure > Table > msr_cs_avg_resolution_time_wh > Measure > Caption > Ø Resolution Time (Working Hours)
            """        
            logger.info(f'{object.Culture.Name} > {object.Object.Parent.Name} > {object.Object.Parent.ObjectType} > {object.Object.Name} > {object.Object.ObjectType} > {object.Property} > {object.Value}')

        if object.Object.Name == 'Transactions':
            """
            INFO     en-US > Model > Model > Transactions > ObjectTranslation > Table > [fact] Transactions  
            """        
            logger.info(f'{object.Culture.Name} > {object.Object.Parent.Name} > {object.Object.Parent.ObjectType} > {object.Object.Name} > {object.Object.ObjectType} > {object.Property} > {object.Value}')
        
        if object.Object.Name == 'ClickAndCollect':
            """
            INFO     en-US > Transactions > Table > ClickAndCollect > Column > Caption > Click And Collect
            INFO     en-US > Transactions > Table > ClickAndCollect > Column > DisplayFolder > Transaction Information  
            """        
            logger.info(f'{object.Culture.Name} > {object.Object.Parent.Name} > {object.Object.Parent.ObjectType} > {object.Object.Name} > {object.Object.ObjectType} > {object.Property} > {object.Value}')

import pytabular 
import logging

logger = logging.getLogger("PyTabular")

model = pytabular.Tabular(f'{SERVER};Catalog={INITIAL_CATALOG}')

for translation in model.Cultures['en-US'].ObjectTranslations:
    translation.__rich_repr__()

msr_trns_quantity_vs_py_perc
┏━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃       Properties ┃                              ┃
┡━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│             Name │ msr_trns_quantity_vs_py_perc │
│       ObjectType │ Measure                      │
│       ParentName │ Measure                      │
│ ParentObjectType │ Table                        │
├──────────────────┼──────────────────────────────┤
│  Object Property │ Caption                      │
│     Object Value │ Δ Units vs PY %              │
└──────────────────┴──────────────────────────────┘

[Daandamhuis]

In pytablular.py

def get_dependancies(self, object: str, object_table: str) -> pd.DataFrame:
    """Returns the dependant columns of a measure"""
    dmv_query = f"select * from $SYSTEM.DISCOVER_CALC_DEPENDENCY where [OBJECT] = '{object}' and [TABLE] = '{object_table}'"
    return self.Query(dmv_query)

def get_sample_values(
        self, column: str, table: str, top_n: int = 3
    ) -> pd.DataFrame:
        column_to_sample = f"'{table}'[{column}]"
        dax_query = f"""EVALUATE 
                            TOPNSKIP(
                                {top_n}, 
                                0, 
                                FILTER( 
                                    VALUES({column_to_sample}),  
                                    NOT ISBLANK({column_to_sample}) && LEN({column_to_sample}) > 0
                                ), 
                                1
                            ) 
                            ORDER BY {column_to_sample}
                    """
        return self.Query(dax_query)

Sample Usage

model = pytabular.Tabular(f"{SERVER};Catalog={INITIAL_CATALOG}")

columns = [
    "REFERENCED_OBJECT_TYPE",
    "REFERENCED_TABLE",
    "REFERENCED_OBJECT",
    "REFERENCED_EXPRESSION",
    "QUERY",
]

measure_dependancies = model.get_dependancies(
    object="msr_cs_avg_resolution_time_wh", object_table="Measure"
)

logger.info(measure_dependancies[columns])

column_sample_value = model.get_sample_values(column="DayName", table="Date", top_n=6)

logger.info(column_sample_value)

Result

INFO       REFERENCED_OBJECT_TYPE REFERENCED_TABLE                REFERENCED_OBJECT                              REFERENCED_EXPRESSION QUERY                                                                             test.py:23
0                  TABLE            Cases                            Cases                                               None  None
1                 COLUMN            Cases                         IsClosed                                               None  None
2            CALC_COLUMN            Cases  Resolution Time (Working Hours)  \n    /* \n        Assumption for this calcula...  None

Date[DayName]    
0      1 Monday
1   3 Wednesday
2    4 Thursday
3      5 Friday
4    6 Saturday
5      7 Sunday

[Daandamhuis]

I've added it to the PyMeasure Class and there it's stored as a pd.Dataframe this can be helpful to generate the documentation.

measure = model.Measures['msr_cs_avg_resolution_time_wh']
measure_deps = measure.Dependancies.to_dict('records')

[{k.replace("_", " ").title(): v for k, v in measure_dep.items()} for measure_dep in measure_deps]

[
    {
        "Database Name": "B2C",
        "Object Type": "MEASURE",
        "Table": "Measure",
        "Object": "msr_cs_avg_resolution_time_wh",
        "Expression": "\nCALCULATE (\n    AVERAGE ( 'Cases'[Resolution Time (Working Hours)] ),\n    KEEPFILTERS ( 'Cases'[IsClosed] = TRUE )\n)",
        "Referenced Object Type": "TABLE",
        "Referenced Table": "Cases",
        "Referenced Object": "Cases",
        "Referenced Expression": "None",
        "Query": "None"
    },
    {
        "Database Name": "B2C",
        "Object Type": "MEASURE",
        "Table": "Measure",
        "Object": "msr_cs_avg_resolution_time_wh",
        "Expression": "\nCALCULATE (\n    AVERAGE ( 'Cases'[Resolution Time (Working Hours)] ),\n    KEEPFILTERS ( 'Cases'[IsClosed] = TRUE )\n)",
        "Referenced Object Type": "COLUMN",
        "Referenced Table": "Cases",
        "Referenced Object": "IsClosed",
        "Referenced Expression": "None",
        "Query": "None"
    },
    {
        "Database Name": "B2C",
        "Object Type": "MEASURE",
        "Table": "Measure",
        "Object": "msr_cs_avg_resolution_time_wh",
        "Expression": "\nCALCULATE (\n    AVERAGE ( 'Cases'[Resolution Time (Working Hours)] ),\n    KEEPFILTERS ( 'Cases'[IsClosed] = TRUE )\n)",
        "Referenced Object Type": "CALC_COLUMN",
        "Referenced Table": "Cases",
        "Referenced Object": "Resolution Time (Working Hours)",
        "Referenced Expression": ".....",
        "Query": "None"
    }
]