SQL SERVER – Create Database and BI (SSAS, SSRS, SSIS) Documentation

ApexSQL Doc is a simple but powerful tool which is used for documenting SQL Server databases, SSIS packages, SSAS cubes and SSRS items. It enables users to customize the documentation by including/excluding needed database objects and settings and to generate the documentation in various output formats like, CHM, HTML, PDF and Word formats. Let us learn about how to create BI (SSAS, SSRS, SSIS) documentation.

In addition to a user friendly UI, ApexSQL Doc offers a command line interface aka CLI which can be used to automate the process of documentation entirely and helps save time.

ApexSQL Doc components

ApexSQL Doc consists of three main components:

• GUI – a windows application used for manual documenting, selecting objects and options for documenting and customizing documentation output
• CLI – a console application which allows users to run the documentation process automatically and unattended
• SSMS Add-In – a SQL Server Management Studio add-in that allows documenting directly from SSMS

Please note thathe SSMS Add-In is an optional component which can be selected/deselected when installing ApexSQL Doc.

How does it work?

Documenting SQL Server databases and server objects

In order to start the documentation setup process, click on the Add button and select a SQL Server, input the needed details and connect.

Once the connection is established, the list of connected servers and databases will be shown separately in two sections.

At this point, the Server objects tab will be unlocked in the Datasources and objects pane. This feature is well organized and easily accessible.

Once the server object types are selected, you can select specific database objects and options which will be included in documentation. Clicking the Database objects tab, opens a list of all database object types.

Clicking the Database options tab, will open additional documentation settings at the database level. In this tab it is possible to choose which database settings/options need to be turned on and included in the documentation. These settings can be saved as personal defaults for later use. Some honourable option mentions are the documentation Data model diagrams and Graphical dependencies.

After the selection of database objects and settings is complete, the documentation can be created by clicking the Generate button in the Home tab.

Documenting SSIS packages

The documentation of SSIS packages is really simple and easy like every process in ApexSQL Doc. First of all, to document SSIS packages, ApexSQL Doc must be running with administrator privileges on the system.

To start the documentation process of SSIS packages click on the Add button in the Integration services tab under the Datasources and objects pane or click the Add SSIS package button. After that select a data source where the wanted packages are located and connect to it. The documentation of SSIS packages can be performed from three different data sources. SQL Server, SSIS package store or directly with package files from the File system.

Once connected, to a server or added packages from a file system, the list of packages and connected servers will be shown separately like in the picture below. Note that all three data sources will be displayed separately in the server section to the right.

When you have added all desired packages, you must select them in order to proceed to the next step, which is selecting the package details needed for documentation. Those details can be found in the Package details tab.

From this tab you can select the following SSIS package details:

• Connection managers
• Data flow diagrams
• Control flow diagrams
• Event handlers
• Executables
• Log providers
• Precedence constraints
• Variables
• Include package source (*.dtsx)

When you have specified the required options and settings for the SSIS documentation, click the Generate button to create the documentation. When automating the process of SSIS documentation ApexSQL Doc automatically detects any changes made in the package folders. So if there are some new packages added or there has been any change in the folder that is used for documentation, those files will be automatically detected and documented.

Documenting SSAS cube databases

Another feature in ApexSQL Doc is the documentation of SSAS databases. Since SSAS is a popular feature in the SQL server ecosystem, ApexSQL Doc offers the possibility of documenting both Multidimensional and Tabular databases with various included details.

To begin the documentation of SSAS databases, just click the Add SSAS database button in the Home tab or select the Analysis services tab and click the Add button. Input the name of the SSAS server and connect.

When the connection is established, select the desired database and then go to the Database details tab where you will see a list of both Multidimensional and Tabular database details.

Select the desired SSAS details, depending on the type of database you selected for documenting and click the Generate button.

Documenting SSRS items

With ApexSQL Doc it is also possible to document SSRS reports, shared datasets, shared data sources and projects from the file system and web services (native and SharePoint). Note that all versions of SSRS, from 2005 are  supported.

To document a SSRS report select the Reporting services tab and click the Add button or just simply click the Add SSRS item button on the Home tab. After that choose a data source which you will use and navigate to the location of needed SSRS items.

Once you’ve done that, you will get a list of connected items on the right with details like the Item type, so you can easily recognise the item when using it later for example.

When it comes to SSRS documentation options, you can select the following SSRS item details for documentation:

• Report items
  • Data sources
  • Datasets
  • Embedded images
  • Page body, header and footer
  • Parameters
• Shared items
  • Shared data sources
  • Shared datasets

To manage those options, select the Item details tab in the Datasources and objects pane and select the desired options.

When the selection of the SSRS options is complete, start the documentation with the already mentioned Generate button.

Customizing the documentation

In addition to multiple data source documentation features ApexSQL offers the ability to customize the documentation with various documentation styles, custom text and naming options. It also offers the creation of documentation in five different output formats (CHM, HTML, DOC, DOCX and PDF). All of these customizing options can be found under the Output options pane.

Reference: Pinal Dave (https://blog.sqlauthority.com)