Repository files navigation

SQL Server Analysis Services (SSAS) features extensions.

    ____        __________ ___   _____   / __\__  __/ ___/ ___//| / ___/  / /_/ / / / /\__\\__\/ /||\__\ / ____/ /_/ /___/ /__/ / ___|___/ / /_/\__, //____/____/_/|_/____/        /____/                        SQL Server Analysis Services with Python

This project is tested with:

Project SSAS name must contains one of these words:

• ssas
• bi
• olap
• tabular

pip install pyssas

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

cd<project_ssas>pyssas --func metadata_exporter

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

cd examples/pyssas --func metadata_exporter

I created these scripts to ensure standardized and fullyreplicable 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 wholeproject 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 nameID different rules will apply.

# ----- COLS: general params -----# summarizeBy -> none# formatString -> 0# dataType -> string# displayFolder -> Colunas# description -> data lineagedata['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())ifdata['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 namedURL 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.

cd<project_ssas>pyssas --func cube_formatter

cd examples/pyssas --func cube_formatter

• Change properties folder_columns, folder_measures, folder_calculated_columns and summarize
  • Open the fileconfig.py and add inCUBE_GENERAL_CONFIG
• Add list_table_exceptions or list_col_exceptions to not formatt
  • Open the fileconfig.py and add inEXCEPTIONS_PK_CONFIG

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

Open the fileconfig.py and add configuration your database inDATABASE_CONFIG

This scripts capture informations of file.bim