· 6 years ago · Mar 29, 2019, 11:34 AM
1//-------------------------------------------------------------------------------------------------------------------------------------------------------------
2// MICROSOFT SSAS TABULAR - template TOM library
3// For model with compatibility level 1200+
4// 29/03/2019
5
6// Scope: shows how to connect and find a database on a remote SSAS Tabular instance
7// + some sample navigation on inside objects as Tables and Partitions.
8// Library organization overview:
9// https://docs.microsoft.com/en-us/bi-reference/tom/introduction-to-the-tabular-object-model-tom-in-analysis-services-amo
10// Complete library documentation
11// https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular?view=analysisservices-dotnet
12//
13// Author:
14// Andrea Dalla Valle
15// BI Consultant
16// Consoft Sistemi SpA
17// via Pio VII 127
18// Torino - Italy
19// <andrea.dallavalle@consoft.it>
20//-------------------------------------------------------------------------------------------------------------------------------------------------------------
21
22using System;
23using System.IO;
24using Microsoft.AnalysisServices.Tabular;
25
26namespace templateTOMlibrary
27{
28 class Program
29 {
30 #region LOGS
31 //Region containing methods for log and console output purposes
32
33 //Initializes log functionality
34 //Creates the necessary log folder and file to log the app behavior.
35 //Pay attention that, at first, it checks if same folder and file exists and eventually recreates the path
36 //for a new iteration, deleting already existing files.
37 //PARAMS:
38 // - <log_folder_path> absolute path where the log file will be generated
39 // - <log_file_name> name of the log file to create. Note that the file will have '.log' extension.
40 //RETURN: string - returns the absolute path and name of the log file created
41 static public string logInit(string log_folder_path, string log_file_name)
42 {
43 //define log file complete path + name
44 string log_file_path = log_folder_path + "\\" + log_file_name + ".log";
45
46 //check existence of log files + folder
47 if (Directory.Exists(log_folder_path) == false)
48 {
49 //create new log folder
50 Directory.CreateDirectory(log_folder_path);
51 }
52 if (File.Exists(log_file_path) == false)
53 {
54 //create new log file
55 File.Create(log_file_path);
56 }
57 else
58 {
59 //delete old file
60 File.Delete(log_file_path);
61 //create a new empty one
62 var file = File.Create(log_file_path);
63 file.Close();
64 }
65 return log_file_path;
66 }
67
68 //This method switches between log / console output for debug purposes.
69 //on log mode, writes a new file line for each call, on append to the already written lines.
70 //PARAM:
71 // <mode> - switch between output on log file ('/c'), and console ('/l'), or both ('/cl')
72 // <message> - the string to be written as output
73 // <log_file_path> - the absolute path of the log file to be written on
74 static public void WriteLogConsoleOutput(string mode, string message, string log_file_path)
75 {
76 //console
77 if (mode == "/c" || mode == "/cl")
78 {
79 Console.WriteLine(message);
80 }
81 //log
82 if (mode == "/l" || mode == "/cl")
83 {
84 //writes one line for each iteration on the file
85 File.AppendAllText(log_file_path, Environment.NewLine + message);
86 }
87 }
88 #endregion LOGS
89
90 static void Main(string[] args)
91 {
92 //init log file creation
93 string log_file_path = logInit("C:\\Temp\\TOM_log\\", "tom_log");
94
95 //This template uses args to include parameters from outside.
96 //The program call must be: ProjectName.exe "arg1" "arg2" "arg3" and so on as needed.
97
98
99 //-------------- CMD PARAMETERS HANDLING --------------
100
101 //manage command line arguments
102 switch (args.Length)
103 {
104 case 1: //manage help
105 //this switch is called using just one argument "?"
106
107 if (args[0] == "?")
108 {
109 string help = "help message";
110 //output
111 WriteLogConsoleOutput("/c", help, log_file_path);
112 }
113 else
114 { //argument value not valid
115 string error = Environment.NewLine + "Incorrect arguments provided. Please retry.";
116 //output
117 WriteLogConsoleOutput("/c", error, log_file_path);
118 }
119 break;
120
121 case 2:
122 //sample use of the app
123 //2 args: server_name and model_name
124
125 //-----------------INIT---------------------
126 string tabular_instance_name = args[0];
127 string tabular_database_name = args[1];
128
129
130 //-----------SERVER CONNECTION--------------
131
132 //create server
133 Server istanza = new Server();
134 //connection
135 try
136 {
137 istanza.Connect(tabular_instance_name);
138 }
139 catch (Exception e)
140 {
141 //exception management
142 string conn_error = Environment.NewLine + "A connection to server '" + tabular_instance_name + "' cannot be mode, check instance name if it is correct.";
143 //output
144 WriteLogConsoleOutput("/l", conn_error, log_file_path);
145 //retrieve exception
146 string exception = Environment.NewLine + e.ToString() + Environment.NewLine;
147 //output
148 WriteLogConsoleOutput("/l", exception, log_file_path);
149 }
150
151 //check if connection is open
152 if (istanza.Connected)
153 {
154 string conn_success = "Connection succeded.";
155 //output
156 WriteLogConsoleOutput("/l", conn_success, log_file_path);
157
158
159 //-----------SERVER NAVIGATION--------------
160
161 //create database
162 Database db = new Database();
163 //select database
164 try
165 {
166 string model_lookup = "Looking for model '" + tabular_database_name + "'...";
167 //output
168 WriteLogConsoleOutput("/l", model_lookup, log_file_path);
169
170 //retrive database
171 db = istanza.Databases.GetByName(tabular_database_name);
172 }
173 catch (Exception e)
174 {
175 //exception management
176 string model_nf = "Model not found. Check model name and try again.";
177 //output
178 WriteLogConsoleOutput("/l", model_nf, log_file_path);
179
180 //exception
181 string exception = e.ToString();
182 //output
183 WriteLogConsoleOutput("/l", exception, log_file_path);
184 }
185
186
187 //-----------DATABASE NAVIGATION--------------
188
189 //some examples on querying objects inside a database
190
191 //get tabular database model
192 Model m = new Model();
193 //NB: model is one per model usually
194 m = db.Model;
195
196 //get tables collection, iterable with a loop
197 TableCollection tc = m.Tables;
198 //get specific Table
199 Table t = tc.Find("test_file");
200
201 //get specific table's partitions collection, iterable with a loop
202 PartitionCollection pc = t.Partitions;
203 //get specific Table
204 Partition p = pc.Find("02_2018");
205
206
207 //-----------PUSH MODS ON SERVER--------------
208
209 //save database model changes done here to server
210 m.SaveChanges();
211 //OR
212 //save entire database changes on server
213 db.Update();
214
215
216 //-----------CLOSE CONNECTION--------------
217 istanza.Disconnect();
218 }
219 break;
220
221 default: //handles cases outside the previous definitions
222
223 string outside_def = "error";
224 //output
225 WriteLogConsoleOutput("/l", outside_def, log_file_path);
226 break;
227 }
228
229 //-------------- END PROGRAM --------------
230 }
231 }
232}