Efficient Tabular Automation with TMSL and PowerShell
Senior Consultant at Adatis, Ust Oldfield, delves into the world of Tabular Automation using TMSL and PowerShell. Explore the power of in-memory SSDT Semantic Layer and discover how Tabular in a Warehouse Architecture revolutionizes data analytics. Learn the benefits of using Tabular as a Corporate Data Fast Analytics Engine and dive into the challenges and solutions in Tabular modeling. Featuring a deep dive into Tabular Model Scripting Language (TMSL) and the various objects in a tabular model, this session is a must for those seeking to streamline their data processes efficiently.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Tabular Automation With TMSL and PowerShell Ust Oldfield | Adatis 15/02/2018
Ust Oldfield Senior Consultant at Adatis Love to travel Enjoy trying to make things more efficient
Tabular In-Memory SSDT Semantic Layer
Why Use Tabular Corporate Data Fast Analytics Engine Easy To Develop Models
The Problems I ve got 99 problems and the DAX ain t one
Agenda Business Built Tabular Model Now Don t know SSAS What s Visual Studio? / DAX
Agenda Slow Development Fast Basics of a model Less time doing mundane tasks Development
Agenda Large Data Set Volumes too big Selective analysis Quick and Dynamic period
Agenda Agenda Intro TMSL Intro PowerShell Demos
Tabular Model Scripting Language (TMSL) Introduced in SQL Server Analysis Services 2016 JSON Format Description of objects in a tabular model Commands to send to Analysis Services Commands to manage a tabular model
Tabular Objects Data Sources Tables Perspectives Cultures Roles Relationships
Tabular Object Dependencies Model Data Sources Relationships Tables Perspectives Cultures Roles Perspective Tables Perspective Columns Columns Translations Members Measures Permissions Perspective Measures Partitions Perspective Hierarchies Hierarchies
Data Source Object "dataSource": { "name": "SqlServer localhost AdventureWorksDW2014", "connectionString": "Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=AdventureWorksDW2014;Integrated Security=SSPI;Persist Security Info=false", "impersonationMode": "impersonateAccount", "account": DOMAIN\\USER" } impersonateAnonymous impersonateCurrentUser impersonateServiceAccount impersonateUnattendedAccount default impersonateAccount
Table Object "tables": [ { "name": "Table Name", "isHidden": false, "columns": [<...>], "partitions": [<...>], "measures": [<...>], "hierarchies": [<...>] } ]
Column Object description alignment dataCategory displayFolder displayOrdinal formatString isAvailableInMDX isDefaultImage isDefaultLabel isHidden isKey isNullable isUnique keepUniqueRows sortByColumn summarizeBy tableDetailPosition type "columns": [ { "name": "Column Name", "dataType": "int64", "sourceColumn": "ColumnName", "sourceProviderType": "Integer" } ] CALCULATED COLUMNS isDataTypeInferred expression CALCULATED TABLES sourceColumn isNameInferred columnOriginTable columnOriginColumn
Partition Object "partitions": [ { "name": "Table Name", "dataView": "full", "source": { "query": " SELECT [SchemaName].[TableName].* FROM [SchemaName].[TableName] ", "dataSource": "SqlServer AdventureWorksDW2014" } } ]
Measure Object "measures": [ { "name": "Measure Name", "expression": " COUNTX(InternetSales, InternetSales[OrderDateKey])" } ]
Hierarchy Object "hierarchies": [ { "name": "Hierarchy Name", "levels": [ { "name": "Hierarchy Column1", "ordinal": 0, "column": "Table Column1" }, { "name": "Hierarchy Column2", "ordinal": 1, "column": "Table Column2" ] } ]
Perspective Object "perspectives": [ { "name": "Perspective Name", "tables": [ { "name": "Table Name", "columns": [<...>], "measures": [<...>], "hierarchies": [<...>] } ] } ]
Culture Object "cultures": [ { "name": "fr-FR", "translations": [ { "model": { "name": "Model", "translatedCaption": "Mod le", "tables": [ { "name": "TableName", "translatedCaption": "NomDeLaTable", "columns": [<...>] } ] } } ] } ]
Role Object "role": { "name": "Role", "modelPermissions": "read", "members": [ { "memberName": "ujo", "memberId": "S-1-5-21-2328473335-1214001268-1226958112-1001" } ] }
Relationship Object "relationships": [ { "name": "ManyTableNameToOneTableName", "fromTable": "ManyTableName", "fromColumn": "ForeignKey", "toTable": "OneTableName", "toColumn": "PrimaryKey" } ]
TMSL Commands "create": "createOrReplace": "refresh": "alter": "attach": "backup": "delete": "detach": "mergePartitions": "restore": "sequence": "synchronize":
PowerShell Command-line language Used to administer operating systems Used to automate processes Uses cmdlets to extend functionality
Invoke-ASCmd cmdlet #Uses an input file to issue a command to an AS server Invoke-ASCmd InputFile "C:\folder-name\TMSLScript.json" -Server "localhost" #Uses a Query to issue a command to an AS server Invoke-ASCmd -Server "localhost" -Query "{""refresh"":{""type"": ""full"", ""objects"": [{""database"": ""AdventureWorks2014""}]}}"
The Dream Team Metadata driven Templates Extendable TMSL
The Problems, again Business Built Tabular Slow Development Large Data Set
Filtered Model From Template Flow Find and Replace Values
Demo Filtered Model From Template
Demo Tabular Generation No Template
Tabular Generation From Template Flow object Find and Replace Values
Demo Tabular Generation From Template
Summary Filtered Model From Template Tabular Generation No Template Tabular Generation From Template
References https://docs.microsoft.com/en-us/sql/analysis- services/tabular-models-scripting-language- objects/tmsl-reference-tabular-objects https://docs.microsoft.com/en-us/sql/analysis- services/tabular-models-scripting-language- commands/tmsl-reference-commands https://docs.microsoft.com/en-us/sql/analysis- services/powershell/analysis-services-powershell- reference
Thanks for Listening Ust Oldfield @uoldfield https://github.com/uoldfield/TabularAutomation blogs.adatis.co.uk/ustoldfield
