- #Database modeling tools open source sql server manual
- #Database modeling tools open source sql server code
IsActive property.Ĭomplete code for getting the relationships is as follows Get the Boolean value of whether the relationship is active or not using. If the property value is true then it is an “Inner Join” else “Outer” One main important property of a relationship is the joining type based on. Get the cardinality of the relationships using. Repeat steps f to I for “Relationship To Table Name” properties. Get the Physical key name for From Table name used in joining the table based on the Column type – Data or Calculated using. Get the Logical key name for From Table name used in joining the table using.
Get the Physical table name of the From Table name using. If it is same, get the Relationship name, From Table name using. Get all the relationships from the table by iterating on Model.Relationships property in object.Ĭompare the “Relationship To table name” with the Logical name extracted in step 1.c. If the table is hidden or not, get that Boolean flag using. Get the source query of the physical table used in the model using property. Get the Physical or the Actual table name using the annotations on the model using. Get the Table Description property which can be a data dictionary definition given for a table using. Get the different properties for the table like Logical Table Name which is a Friendly table name or Displayed table name provided to the table in the model using. Once the model name is available, iterate on the tables present on the model.įetch all the tables in the model using in () property and iterate over them one by one using a Table object. To get the first main output report for Tables, follow the steps below
Use the Connect method to connect to server where the input parameter is “Provider = MSOLAP Data Source = “” Ensure sufficient access to the user for server access is available. Get the Server connectivity for the models to read the metadata.Ĭonnect to the server provided.
Once we receive the input, the code steps will be as follows It should have capability to generate document for multiple models at one time too. Now what should be the input to the tool? In order to make this tool friendly with any end user, the input should be simple like the name of Tabular model, which needs documentation with the server name from where the model is present. Perspectives and their corresponding tables Partitions and their corresponding tables Tables and their relationships with other tables Here are the different objects exposed via TOM library referenced from MSDN.Īs a part of design documentation, we need mainly four objects, which can be derived from the green highlighted nodes. Logically, in a Tabular model, everything is driven from a Model which acts like a root that in turn is descendant of a Database (same as in Multidimensional). The model database is nothing but a Json object based definition that can be accessed via TOM object library. This library is under assembly.Īn SSAS Tabular model is a database that run in-memory or in DirectQuery mode, accessing data directly from backend relational data sources. Microsoft has provided Tabular Object Model (TOM) library, which extracts all the metadata and properties for the model from SQL Server 2016 onwards, which is an extension of the AMO library used to extract Multidimensional cube metadata. I have used C#.NET to build up the documentation tool referring MSDN help for the libraries used to fetch the detailed properties for the Model. In addition, help the end users to see and review the latest change on the models quickly without even bothering the development team.
#Database modeling tools open source sql server manual
I decided to automate the documentation of the SSAS model, to ease the manual effort here as well as save time. Adding the changes on the documentation is a time consuming process and sometimes loses track or remains inconsistent with the code built which is a critical issue when the system is in production for years. In addition, development always demands change or enhancements that incurs changes on the documentation that is a continuous process. We have been developing SQL Server Analysis Tabular Model’s, which were quite a many in numbers and documenting 50 or 60 models manually is a big effort. There has been an ever-growing discomfort on documenting things especially when it’s very dynamic in nature and I was at one time undergoing the same.
0 kommentar(er)
