brunocampos01/pyssas

Build and deploy automated to SQL Server Analysis Services (SSAS) with Python.

DEPRECATED !

PySSAS

SQL Server Analysis Services (SSAS) features extensions.

    ____        __________ ___   _____
   / __ \__  __/ ___/ ___//   | / ___/
  / /_/ / / / /\__ \\__ \/ /| | \__ \ 
 / ____/ /_/ /___/ /__/ / ___ |___/ / 
/_/    \__, //____/____/_/  |_/____/  
      /____/                        

SQL Server Analysis Services with Python

Requirements

This project is tested with:

Requisite	Version
Python	3.9.1
Pip	21.2.4
Microsoft SSAS	tabular 1200

Project SSAS name must contains one of these words:

• ssas
• bi
• olap
• tabular

Installation

pip install pyssas

---

⭐ FEATURE: metadata_exporter

To create dynamic documentation about the Business Intelligence project. With this feature you can enter a job in an ETL system that runs the pyssas --func metadata_exporter and thus update the documentation every time the ETL process is executed.

Usage

cd <project_ssas>
pyssas --func metadata_exporter

As output 3 directories (queries, measures and calculated_cols) will be created within the path_olap.

Test

cd examples/
pyssas --func metadata_exporter

⭐ FEATURE: cube_formatter

I created these scripts to ensure standardized and fully replicable OLAP projects. This way we are able to create projects from scratch with a basic structure and identify with other SSIS projects.
In addition, we have the great advantage of having the whole project documented through the code itself.
Example:

• In these cases bellow is shows that a common configuration is made to all but when it is a column that starts with the name ID different rules will apply.

            # ----- COLS: general params -----
            # summarizeBy -> none
            # formatString -> 0
            # dataType -> string
            # displayFolder -> Colunas
            # description -> data lineage
            data['model']['tables'][table]['columns'][col]['summarizeBy'] = 'none'
            data['model']['tables'][table]['columns'][col]['formatString'] = "0"
            data['model']['tables'][table]['columns'][col]['dataType'] = 'string'
            data['model']['tables'][table]['columns'][col]['displayFolder'] = 'Colunas'
            data['model']['tables'][table]['columns'][col]['description'] = \
                dict_tables.get(data['model']['tables'][table]['name'].lower())

            if data['model']['tables'][table]['columns'][col]['name'] \
                    .startswith('ID'):
                data['model']['tables'][table]['columns'][col]['formatString'] = '#,0'
                data['model']['tables'][table]['columns'][col]['dataType'] = 'int64'
                data['model']['tables'][table]['columns'][col]['isHidden'] = 'true'
                data['model']['tables'][table]['columns'][col]['isNullable'] = 'false

• A big problem I faced with building the SSAS was having to repeat several times the some confurations. For example, in PK columns, I need to configure some parameters. Unfortunately in Visual Studio (SSDT) there is no support for batch processes.
• There were cases that you need to configure the same properties for specific columns, for example, make sure that columns named URL are of type webUrl. Every time I added a dimension to the cube I had to keep looking for those specific properties, which made me lose a lot of time. Now, what I do is run the set script and that's it, the properties are applied.
• Another fact that motivated me was not to depend on Visual Studio (SSDT). Now I can work in any IDE and soon I don't have the dependency of having to work in Windows anymore.

Usage

cd <project_ssas>
pyssas --func cube_formatter

Test

cd examples/
pyssas --func cube_formatter

Personal Configuration

• Change properties folder_columns, folder_measures, folder_calculated_columns and summarize
  • Open the file config.py and add in CUBE_GENERAL_CONFIG
• Add list_table_exceptions or list_col_exceptions to not formatt
  • Open the file config.py and add in EXCEPTIONS_PK_CONFIG

NOTE: the next feature this project must need config.py by SSAS project. This do will the module work with dynamic configuration.

Add DATABASE_CONFIG to validate Lineage

Open the file config.py and add configuration your database in DATABASE_CONFIG

NOTES

This scripts capture informations of file .bim

---