Exemplo n.º 1
0
        /// <summary>
        /// Connects to a SQL Server 2016 Analysis Services instance and loads a tabular model
        /// from one of the deployed databases on the instance.
        /// </summary>
        /// <param name="serverName"></param>
        /// <param name="databaseName"></param>
        public TabularModelHandler(string serverName, string databaseName, TabularModelHandlerSettings settings = null) : this(settings)
        {
            this.serverName = serverName;
            _disableUpdates = true;

            server = new TOM.Server();

            var connectionString = TabularConnection.GetConnectionString(serverName, applicationName);
            server.Connect(connectionString);

            if (databaseName == null)
            {
                if (server.Databases.Count >= 1)
                {
                    database = server.Databases[0];
                }
                else
                {
                    throw new InvalidOperationException("This instance does not contain any databases, or the user does not have access.");
                }
            }
            else
            {
                database = server.Databases.FindByName(databaseName);
                if (database == null)
                {
                    database = server.Databases[databaseName];
                }
            }
            if (CompatibilityLevel < 1200)
            {
                throw new InvalidOperationException("Only databases with Compatibility Level 1200 or higher can be loaded in Tabular Editor.");
            }

            SourceType = ModelSourceType.Database;
            Source     = database.Server.Name + "." + database.Name;

            Status  = "Connected succesfully.";
            Version = database.Version;
            Init();
            UndoManager.Suspend();

            Model.ClearTabularEditorAnnotations();

            _disableUpdates = false;
            UndoManager.Resume();
            PowerBIGovernance.UpdateGovernanceMode();
            CheckErrors();

            try
            {
                ExternalChangeTrace.Cleanup();
                trace = new ExternalChangeTrace(database, applicationName, XEventCallback);
                if (Settings.ChangeDetectionLocalServers)
                {
                    trace.Start();
                }
            }
            catch (Exception ex)
            {
                Log("Exception while configuring AS trace: " + ex.Message);
            }
        }
Exemplo n.º 2
0
        private static void DynamicRoleCreation(string roleName,
                                                string serverAddress, string databaseId, string filterExpression)
        {
            string ConnectionString = @"Provider=MSOLAP;Data Source=" + serverAddress +
                                      @";Initial Catalog=" + databaseId + ";Integrated Security=SSPI;ImpersonationLevel = Impersonate; persist security info = True; ";
            var RoleIDNamePair = new Dictionary <int, string>();
            // Get Roles Where Rolename == 'NewlycreatedRoleName=@Param'
            var GetRolesIDXMLA = @"<Batch xmlns
=""http://schemas.microsoft.com/analysisservices/2003/engine""
Transaction=""true""><Discover xmlns = ""urn:schemas-microsoft-com:xmlanalysis""><RequestType>TMSCHEMA_ROLES</RequestType><Restrictions/><Properties/></Discover
></Batch>";
            // Take Param: @RoleName for use in XMLA to create role
            var xmlaCreateRole = @"<Batch Transaction=""false""
xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><Create
xmlns=""http://schemas.microsoft.com/analysisservices/2014/engine""><DatabaseID>" + databaseId + @"</DatabaseID><Roles><xs:schema xmlns:xs=""http://www.w3.org/2001/XMLSchema""
xmlns:sql=""urn:schemas-microsoft-com:xmlsql""><xs:element><xs:complexType><xs:sequence><xs:element
type=""row""/></xs:sequence></xs:complexType></xs:element><xs:complexType
name=""row""><xs:sequence><xs:element name=""Name"" type=""xs:string"" sql:field=""Name""
minOccurs=""0""/><xs:element name=""Description"" type=""xs:string""
sql:field=""Description"" minOccurs=""0""/><xs:element name=""ModelPermission""
type=""xs:long"" sql:field=""ModelPermission""
minOccurs=""0""/></xs:sequence></xs:complexType></xs:schema><row xmlns=""urn:schemasmicrosoft-com:xmlanalysis:rowset""><Name>" + roleName + @"</Name><ModelPermission>2</ModelPermission></row></Ro
les></Create></Batch>";
            // Get Role ID Where Rolename == @PRoleName: XMLA??
            var objServer = new Microsoft.AnalysisServices.Tabular.Server();

            objServer.Connect(ConnectionString);
            objServer.Execute(xmlaCreateRole);
            var reader = objServer.ExecuteReader(GetRolesIDXMLA, out XmlaResultCollection
                                                 resultsOut, null, true);

            // Add all roles to Cached List
            while (reader.Read())
            {
                RoleIDNamePair.Add(int.Parse(reader[0].ToString()), reader[2].ToString());
            }
            reader.Close();
            reader.Dispose();
            var RoleIDByRoleName = "";//= RoleIDNamePair.Single(s => s.Value == roleName).Key.ToString();

            foreach (var pair in RoleIDNamePair)
            {
                if (pair.Value == roleName)
                {
                    RoleIDByRoleName = pair.Key.ToString();
                    break;
                }
            }
            var AddFilterTorole = @"<Batch Transaction=""false""
xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><Create
xmlns=""http://schemas.microsoft.com/analysisservices/2014/engine""><DatabaseID>" + databaseId + @"</DatabaseID><TablePermissions><xs:schema
xmlns:xs=""http://www.w3.org/2001/XMLSchema"" xmlns:sql=""urn:schemas-microsoft-com:xmlsql""><xs:element><xs:complexType><xs:sequence><xs:element
type=""row""/></xs:sequence></xs:complexType></xs:element><xs:complexType
name=""row""><xs:sequence><xs:element name=""RoleID"" type=""xs:unsignedLong""
sql:field=""RoleID"" minOccurs=""0""/><xs:element name=""RoleID.Role"" type=""xs:string""
sql:field=""RoleID.Role"" minOccurs=""0""/><xs:element name=""TableID""
type=""xs:unsignedLong"" sql:field=""TableID"" minOccurs=""0""/><xs:element
name=""TableID.Table"" type=""xs:string"" sql:field=""TableID.Table""
minOccurs=""0""/><xs:element name=""FilterExpression"" type=""xs:string""
sql:field=""FilterExpression"" minOccurs=""0""/><xs:element name=""MetadataPermission""
type=""xs:long"" sql:field=""MetadataPermission""
minOccurs=""0""/></xs:sequence></xs:complexType></xs:schema><row xmlns=""urn:schemasmicrosoft-com:xml-analysis:rowset""><RoleID>" + RoleIDByRoleName +
                                  @"</RoleID><TableID>364</TableID><FilterExpression>" + filterExpression + @"</FilterExpression
></row></TablePermissions></Create><SequencePoint
xmlns=""http://schemas.microsoft.com/analysisservices/2014/engine""><DatabaseID>" +
                                  databaseId + @"</DatabaseID></SequencePoint></Batch>";
            var applyRoleQuery = AddFilterTorole;

            objServer.Execute(applyRoleQuery);
        }
Exemplo n.º 3
0
        internal static string DeployExistingTMSL(TOM.Database db, TOM.Server server, string dbId, DeploymentOptions options, bool includeRestricted)
        {
            var orgDb = server.Databases[dbId];

            orgDb.Refresh(true);

            var orgTables = orgDb.Model.Tables;
            var newTables = db.Model.Tables;

            var tmslJson = TOM.JsonScripter.ScriptCreateOrReplace(db, includeRestricted);
            var tmsl     = JObject.Parse(tmslJson).TransformCreateOrReplaceTmsl(db, orgDb, options).FixCalcGroupMetadata(db);
            var orgTmsl  = tmsl.DeepClone();

            var  tmslModel = tmsl["createOrReplace"]["database"]["model"] as JObject;
            bool needsTwoStepCreateOrReplace = false;

            // Detect tables/columns that are change from imported to calculated or vice versa:
            foreach (var newTable in newTables)
            {
                if (!orgTables.ContainsName(newTable.Name))
                {
                    continue;
                }
                var orgTable = orgTables[newTable.Name];

                // Remove tables that were changed from calculated to imported or vice versa:
                if (orgTable.GetSourceType() != newTable.GetSourceType())
                {
                    GetNamedObj(tmslModel["tables"], newTable.Name).Remove();

                    // Make sure we remove all metadata that points to this table as well
                    // Note, we should be careful not to remove any objects that can hold
                    // processed data:
                    if (tmslModel["perspectives"] != null)
                    {
                        foreach (JObject perspective in tmslModel["perspectives"])
                        {
                            GetNamedObj(perspective["tables"], newTable.Name)?.Remove();
                        }
                    }
                    if (tmslModel["cultures"] != null)
                    {
                        foreach (JObject culture in tmslModel["cultures"])
                        {
                            GetNamedObj(culture["translations"]["model"]["tables"], newTable.Name)?.Remove();
                        }
                    }
                    if (tmslModel["relationships"] != null)
                    {
                        foreach (JObject relationship in tmslModel["relationships"].Where(r => r.Value <string>("fromTable").EqualsI(newTable.Name) ||
                                                                                          r.Value <string>("toTable").EqualsI(newTable.Name)).ToList())
                        {
                            relationship.Remove();
                        }
                    }
                    if (tmslModel["roles"] != null)
                    {
                        foreach (JObject modelRole in tmslModel["roles"])
                        {
                            GetNamedObj(modelRole["tablePermissions"], newTable.Name)?.Remove();
                        }
                    }
                    // Todo: Variants, Alternates, (other objects that can reference a table?)

                    needsTwoStepCreateOrReplace = true;
                    continue;
                }

                foreach (var newColumn in newTable.Columns)
                {
                    if (newColumn.Type == TOM.ColumnType.RowNumber ||
                        newColumn.Type == TOM.ColumnType.CalculatedTableColumn ||
                        !orgTable.Columns.ContainsName(newColumn.Name))
                    {
                        continue;
                    }
                    var orgColumn = orgTable.Columns[newColumn.Name];

                    // Remove columns that were changed from calculated to data or vice versa:
                    if (orgColumn.Type != newColumn.Type)
                    {
                        var table = GetNamedObj(tmslModel["tables"], newTable.Name);
                        GetNamedObj(table["columns"], newColumn.Name).Remove();

                        // Make sure we remove all references to this column as well:
                        if (tmslModel["perspectives"] != null)
                        {
                            foreach (JObject perspective in tmslModel["perspectives"])
                            {
                                var tablePerspective = GetNamedObj(perspective["tables"], newTable.Name);
                                if (tablePerspective == null)
                                {
                                    continue;
                                }
                                GetNamedObj(tablePerspective["columns"], newColumn.Name)?.Remove();
                            }
                        }
                        if (tmslModel["cultures"] != null)
                        {
                            foreach (JObject culture in tmslModel["cultures"])
                            {
                                var tableTranslation = GetNamedObj(culture["translations"]["model"]["tables"], newTable.Name);
                                if (tableTranslation == null)
                                {
                                    continue;
                                }
                                GetNamedObj(tableTranslation["columns"], newColumn.Name)?.Remove();
                            }
                        }
                        if (table["columns"] != null)
                        {
                            foreach (JObject column in table["columns"].Where(c => c.Value <string>("sortByColumn").EqualsI(newColumn.Name)))
                            {
                                column["sortByColumn"].Remove();
                            }
                        }
                        if (table["hierarchies"] != null)
                        {
                            foreach (JObject hierarchy in table["hierarchies"].Where(h => h["levels"].Any(l => l.Value <string>("column").EqualsI(newColumn.Name))).ToList())
                            {
                                hierarchy.Remove();
                            }
                        }
                        if (tmslModel["relationships"] != null)
                        {
                            foreach (JObject relationship in tmslModel["relationships"].Where(r => r.Value <string>("fromColumn").EqualsI(newColumn.Name) ||
                                                                                              r.Value <string>("toColumn").EqualsI(newColumn.Name)).ToList())
                            {
                                relationship.Remove();
                            }
                        }
                        if (tmslModel["roles"] != null)
                        {
                            foreach (JObject modelRole in tmslModel["roles"])
                            {
                                GetNamedObj(modelRole["tablePermissions"], newTable.Name)?.Remove();
                            }
                        }
                        // Todo: Variants, Alternates, (other objects that can reference a column?)

                        needsTwoStepCreateOrReplace = true;
                        continue;
                    }
                }
            }

            if (needsTwoStepCreateOrReplace)
            {
                return(new JObject(
                           new JProperty("sequence",
                                         new JObject(
                                             new JProperty("operations",
                                                           new JArray(tmsl, orgTmsl))))).ToString());
            }

            return(tmsl.ToString());
        }
        /// <summary>
        /// Loads an Analysis Services tabular database (Compatibility Level 1200 or newer) from a file
        /// or folder.
        /// </summary>
        /// <param name="path"></param>
        public TabularModelHandler(string path, TabularModelHandlerSettings settings = null)
        {
            Settings = settings ?? TabularModelHandlerSettings.Default;

            Singleton = this;
            server    = null;

            var    fi = new FileInfo(path);
            string data;

            if (fi.Exists && fi.Extension == ".pbit")
            {
                pbit       = new PowerBiTemplate(path);
                data       = pbit.ModelJson;
                SourceType = ModelSourceType.Pbit;
                Source     = path;
            }
            else

            if (!fi.Exists || fi.Name == "database.json")
            {
                if (fi.Name == "database.json")
                {
                    path = fi.DirectoryName;
                }

                if (Directory.Exists(path))
                {
                    data = CombineFolderJson(path);
                }
                else
                {
                    throw new FileNotFoundException();
                }

                SourceType = ModelSourceType.Folder;
                Source     = path;
            }
            else
            {
                data       = File.ReadAllText(path);
                SourceType = ModelSourceType.File;
                Source     = path;
            }
            database           = TOM.JsonSerializer.DeserializeDatabase(data);
            CompatibilityLevel = database.CompatibilityLevel;

            Status = "File loaded succesfully.";
            Init();

            var serializeOptionsAnnotation = Model.GetAnnotation("TabularEditor_SerializeOptions");

            if (serializeOptionsAnnotation != null)
            {
                SerializeOptions = JsonConvert.DeserializeObject <SerializeOptions>(serializeOptionsAnnotation);
            }

            // Check if translations / perspectives are stored locally in the model:
            if (SourceType == ModelSourceType.Folder && (SerializeOptions.LocalTranslations || SerializeOptions.LocalPerspectives))
            {
                UndoManager.Enabled = false;
                BeginUpdate("Apply translations and perspectives from annotations");

                var translationsJson = Model.GetAnnotation("TabularEditor_Cultures");
                if (SerializeOptions.LocalTranslations && translationsJson != null)
                {
                    Model.Cultures.FromJson(translationsJson);

                    foreach (var item in AllTranslatableObjects)
                    {
                        item.LoadTranslations();
                    }
                }

                var perspectivesJson = Model.GetAnnotation("TabularEditor_Perspectives");
                if (SerializeOptions.LocalPerspectives && perspectivesJson != null)
                {
                    Model.Perspectives.FromJson(perspectivesJson);

                    foreach (var item in AllPerspectiveObjects)
                    {
                        item.LoadPerspectives();
                    }
                }

                EndUpdate();
                UndoManager.Enabled = true;
            }
        }
Exemplo n.º 5
0
        void Deploy(string serverName, string databaseID, int doDeploy)
        {
            // Perform direct save:
            if (serverName == null)
            {
                var nextSwitch     = upperArgList.Skip(doDeploy + 1).FirstOrDefault();
                var deploySwitches = new[] { "-L", "-LOGIN", "-O", "-OVERWRITE", "-C", "-CONNECTIONS", "-P", "-PARTITIONS", "-Y", "-SKIPPOLICY", "-R", "-ROLES", "-M", "-MEMBERS", "-X", "-XMLA" };
                if (deploySwitches.Contains(nextSwitch))
                {
                    Error("Invalid argument syntax.");
                    OutputUsage();
                    throw new CommandLineException();
                }

                Console.WriteLine("Saving model metadata back to source...");
                if (Handler.SourceType == ModelSourceType.Database)
                {
                    try
                    {
                        Handler.SaveDB();
                        Console.WriteLine("Model metadata saved.");

                        var deploymentResult = Handler.GetLastDeploymentResults();
                        foreach (var err in deploymentResult.Issues)
                        {
                            if (errorOnDaxErr)
                            {
                                Error(err);
                            }
                            else
                            {
                                Warning(err);
                            }
                        }
                        foreach (var err in deploymentResult.Warnings)
                        {
                            Warning(err);
                        }
                        foreach (var err in deploymentResult.Unprocessed)
                        {
                            if (warnOnUnprocessed)
                            {
                                Warning(err);
                            }
                            else
                            {
                                Console.WriteLine(err);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Error("Save failed: " + ex.Message);
                    }
                }
                else
                {
                    try
                    {
                        Handler.Save(Handler.Source, Handler.SourceType == ModelSourceType.Folder ? SaveFormat.TabularEditorFolder : Handler.SourceType == ModelSourceType.Pbit ? SaveFormat.PowerBiTemplate : SaveFormat.ModelSchemaOnly, Handler.SerializeOptions, true);
                        Console.WriteLine("Model metadata saved.");
                    }
                    catch (Exception ex)
                    {
                        Error("Save failed: " + ex.Message);
                    }
                }
                throw new CommandLineException();
            }

            var conn = upperArgList.IndexOf("-CONNECTIONS");

            if (conn == -1)
            {
                conn = upperArgList.IndexOf("-C");
            }
            if (conn > -1)
            {
                var replaces = argList.Skip(conn + 1).TakeWhile(s => s[0] != '-').ToList();

                if (replaces.Count > 0 && replaces.Count % 2 == 0)
                {
                    // Placeholder replacing:
                    for (var index = 0; index < replaces.Count; index = index + 2)
                    {
                        replaceMap.Add(replaces[index], replaces[index + 1]);
                    }
                }
            }

            string userName = null;
            string password = null;
            var    options  = DeploymentOptions.StructureOnly;

            var switches = argList.Skip(doDeploy + 1).Where(arg => arg.StartsWith("-")).Select(arg => arg.ToUpper()).ToList();

            if (string.IsNullOrEmpty(serverName) || string.IsNullOrEmpty(databaseID))
            {
                Error("Invalid argument syntax.\n");
                OutputUsage();
                throw new CommandLineException();
            }
            if (switches.Contains("-L") || switches.Contains("-LOGIN"))
            {
                var switchPos = upperArgList.IndexOf("-LOGIN"); if (switchPos == -1)
                {
                    switchPos = upperArgList.IndexOf("-L");
                }
                userName = argList.Skip(switchPos + 1).FirstOrDefault(); if (userName != null && userName.StartsWith("-"))
                {
                    userName = null;
                }
                password = argList.Skip(switchPos + 2).FirstOrDefault(); if (password != null && password.StartsWith("-"))
                {
                    password = null;
                }
                if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(password))
                {
                    Error("Missing username or password.\n");
                    OutputUsage();
                    throw new CommandLineException();
                }
                switches.Remove("-L"); switches.Remove("-LOGIN");
            }
            if (switches.Contains("-O") || switches.Contains("-OVERWRITE"))
            {
                options.DeployMode = DeploymentMode.CreateOrAlter;
                switches.Remove("-O"); switches.Remove("-OVERWRITE");
            }
            else
            {
                options.DeployMode = DeploymentMode.CreateDatabase;
            }
            if (switches.Contains("-P") || switches.Contains("-PARTITIONS"))
            {
                options.DeployPartitions = true;
                switches.Remove("-P"); switches.Remove("-PARTITIONS");

                if (switches.Contains("-Y") || switches.Contains("-SKIPPOLICY"))
                {
                    options.SkipRefreshPolicyPartitions = true;
                    switches.Remove("-Y"); switches.Remove("-SKIPPOLICY");
                }
            }
            if (switches.Contains("-C") || switches.Contains("-CONNECTIONS"))
            {
                options.DeployConnections = true;
                switches.Remove("-C"); switches.Remove("-CONNECTIONS");
            }
            if (switches.Contains("-R") || switches.Contains("-ROLES"))
            {
                options.DeployRoles = true;
                switches.Remove("-R"); switches.Remove("-ROLES");

                if (switches.Contains("-M") || switches.Contains("-MEMBERS"))
                {
                    options.DeployRoleMembers = true;
                    switches.Remove("-M"); switches.Remove("-MEMBERS");
                }
            }
            var    xmla_scripting_only = switches.Contains("-X") || switches.Contains("-XMLA");
            string xmla_script_file    = null;

            if (xmla_scripting_only)
            {
                var switchPos = upperArgList.IndexOf("-XMLA"); if (switchPos == -1)
                {
                    switchPos = upperArgList.IndexOf("-X");
                }
                xmla_script_file = argList.Skip(switchPos + 1).FirstOrDefault(); if (String.IsNullOrWhiteSpace(xmla_script_file) || xmla_script_file.StartsWith("-"))
                {
                    xmla_script_file = null;
                }
                if (string.IsNullOrEmpty(xmla_script_file))
                {
                    Error("Missing xmla_script_file.\n");
                    OutputUsage();
                    throw new CommandLineException();
                }
                switches.Remove("-X");
                switches.Remove("-XMLA");
            }

            try
            {
                if (replaceMap.Count > 0)
                {
                    Console.WriteLine("Switching connection string placeholders...");
                    foreach (var map in replaceMap)
                    {
                        Handler.Model.DataSources.SetPlaceholder(map.Key, map.Value);
                    }
                }

                var cs = string.IsNullOrEmpty(userName) ? TabularConnection.GetConnectionString(serverName, Program.ApplicationName) :
                         TabularConnection.GetConnectionString(serverName, userName, password, Program.ApplicationName);
                if (xmla_scripting_only)
                {
                    Console.WriteLine("Generating XMLA/TMSL script...");
                    var s = new TOM.Server();
                    s.Connect(cs);
                    var xmla = TabularDeployer.GetTMSL(Handler.Database, s, databaseID, options);
                    using (var sw = new StreamWriter(xmla_script_file))
                    {
                        sw.Write(xmla);
                    }
                    Console.WriteLine("XMLA/TMSL script generated.");
                }
                else
                {
                    Console.WriteLine("Deploying...");
                    Handler.Model.UpdateDeploymentMetadata(DeploymentModeMetadata.CLI);
                    var deploymentResult = TabularDeployer.Deploy(Handler, cs, databaseID, options);
                    Console.WriteLine("Deployment succeeded.");
                    foreach (var err in deploymentResult.Issues)
                    {
                        if (errorOnDaxErr)
                        {
                            Error(err);
                        }
                        else
                        {
                            Warning(err);
                        }
                    }
                    foreach (var err in deploymentResult.Warnings)
                    {
                        Warning(err);
                    }
                    foreach (var err in deploymentResult.Unprocessed)
                    {
                        if (warnOnUnprocessed)
                        {
                            Warning(err);
                        }
                        else
                        {
                            Console.WriteLine(err);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Error($"{(xmla_scripting_only ? "Script generation" : "Deployment")} failed! {ex.Message}");
            }
        }
Exemplo n.º 6
0
 public DeploymentResult(IEnumerable <string> issues, IEnumerable <string> warnings, IEnumerable <string> unprocessed, TOM.Server destinationServer)
 {
     this.Issues            = issues.ToList();
     this.Warnings          = warnings.ToList();
     this.Unprocessed       = unprocessed.ToList();
     this.DestinationServer = destinationServer;
 }
Exemplo n.º 7
0
        static bool HandleCommandLine(string[] args)
        {
            var upperArgList = args.Select(arg => arg.ToUpper()).ToList();
            var argList      = args.Select(arg => arg).ToList();

            if (upperArgList.Contains("-?") || upperArgList.Contains("/?") || upperArgList.Contains("-H") || upperArgList.Contains("/H") || upperArgList.Contains("HELP"))
            {
                OutputUsage();
                return(true);
            }

            enableVSTS = upperArgList.IndexOf("-VSTS") > -1 || upperArgList.IndexOf("-V") > -1;
            var warnOnUnprocessed = upperArgList.IndexOf("-WARN") > -1 || upperArgList.IndexOf("-W") > -1;
            var errorOnDaxErr     = upperArgList.IndexOf("-ERR") > -1 || upperArgList.IndexOf("-E") > -1;

            TabularModelHandler h;

            if (args.Length == 2 || args[2].StartsWith("-"))
            {
                // File argument provided (either alone or with switches), i.e.:
                //      TabularEditor.exe myfile.bim
                //      TabularEditor.exe myfile.bim -...

                if (!File.Exists(args[1]) && !File.Exists(args[1] + "\\database.json"))
                {
                    Error("File not found: {0}", args[1]);
                    return(true);
                }
                else
                {
                    // If nothing else was specified on the command-line, open the UI:
                    if (args.Length == 2)
                    {
                        return(false);
                    }
                }

                try
                {
                    h = new TOMWrapper.TabularModelHandler(args[1]);
                }
                catch (Exception e)
                {
                    Error("Error loading file: " + e.Message);
                    return(true);
                }
            }
            else if (args.Length == 3 || args[3].StartsWith("-"))
            {
                // Server + Database argument provided (either alone or with switches), i.e.:
                //      TabularEditor.exe localhost AdventureWorks
                //      TabularEditor.exe localhost AdventureWorks -...
                // If nothing else was specified on the command-line, open the UI:
                if (args.Length == 3)
                {
                    return(false);
                }

                try
                {
                    h = new TOMWrapper.TabularModelHandler(args[1], args[2]);
                }
                catch (Exception e)
                {
                    Error("Error loading model: " + e.Message);
                    return(true);
                }
            }
            else
            {
                // Otherwise, it's nonsensical
                return(false);
            }

            string script     = null;
            string scriptFile = null;

            var    doTestRun   = upperArgList.IndexOf("-T");
            string testRunFile = null;

            if (doTestRun == -1)
            {
                doTestRun = upperArgList.IndexOf("-TRX");
            }
            if (doTestRun > -1)
            {
                if (upperArgList.Count <= doTestRun || upperArgList[doTestRun + 1].StartsWith("-"))
                {
                    Error("Invalid argument syntax.\n");
                    OutputUsage();
                    return(true);
                }
                testRun     = new TestRun(h.Database?.Name ?? h.Source);
                testRunFile = argList[doTestRun + 1];
            }

            var doScript = upperArgList.IndexOf("-SCRIPT");

            if (doScript == -1)
            {
                doScript = upperArgList.IndexOf("-S");
            }
            if (doScript > -1)
            {
                if (upperArgList.Count <= doScript)
                {
                    Error("Invalid argument syntax.\n");
                    OutputUsage();
                    return(true);
                }
                scriptFile = argList[doScript + 1];
                if (!File.Exists(scriptFile))
                {
                    Error("Specified script file not found.\n");
                    return(true);
                }

                script = File.ReadAllText(scriptFile);
            }

            var doCheckDs = upperArgList.IndexOf("-SCHEMACHECK");

            if (doCheckDs == -1)
            {
                doCheckDs = upperArgList.IndexOf("-SC");
            }

            string saveToFolderOutputPath = null;
            string saveToFolderReplaceId  = null;

            var doSaveToFolder = upperArgList.IndexOf("-FOLDER");

            if (doSaveToFolder == -1)
            {
                doSaveToFolder = upperArgList.IndexOf("-F");
            }
            if (doSaveToFolder > -1)
            {
                if (upperArgList.Count <= doSaveToFolder)
                {
                    Error("Invalid argument syntax.\n");
                    OutputUsage();
                    return(true);
                }
                saveToFolderOutputPath = argList[doSaveToFolder + 1];
                if (doSaveToFolder + 2 < argList.Count && !argList[doSaveToFolder + 2].StartsWith("-"))
                {
                    saveToFolderReplaceId = argList[doSaveToFolder + 2];
                }
                var directoryName = new FileInfo(saveToFolderOutputPath).Directory.FullName;
                Directory.CreateDirectory(saveToFolderOutputPath);
            }

            string buildOutputPath = null;
            string buildReplaceId  = null;

            var doSave = upperArgList.IndexOf("-BUILD");

            if (doSave == -1)
            {
                doSave = upperArgList.IndexOf("-B");
            }
            if (doSave == -1)
            {
                doSave = upperArgList.IndexOf("-BIM");
            }
            if (doSave > -1)
            {
                if (upperArgList.Count <= doSave)
                {
                    Error("Invalid argument syntax.\n");
                    OutputUsage();
                    return(true);
                }
                buildOutputPath = argList[doSave + 1];
                if (doSave + 2 < argList.Count && !argList[doSave + 2].StartsWith("-"))
                {
                    buildReplaceId = argList[doSave + 2];
                }
                var directoryName = new FileInfo(buildOutputPath).Directory.FullName;
                Directory.CreateDirectory(directoryName);
            }

            if (doSaveToFolder > -1 && doSave > -1)
            {
                Error("-FOLDER and -BUILD arguments are mutually exclusive.\n");
                OutputUsage();
                return(true);
            }

            // Load model:
            Console.WriteLine("Loading model...");

            if (!string.IsNullOrEmpty(script))
            {
                Console.WriteLine("Executing script...");

                System.CodeDom.Compiler.CompilerResults result;
                Scripting.ScriptOutputForm.Reset(false);
                var dyn = ScriptEngine.CompileScript(script, out result);
                //nUnit.StartSuite("Script Compilation");
                if (result.Errors.Count > 0)
                {
                    Error("Script compilation errors:");
                    var errIndex = 0;
                    foreach (System.CodeDom.Compiler.CompilerError err in result.Errors)
                    {
                        errIndex++;
                        ErrorX(err.ErrorText, scriptFile, err.Line, err.Column, err.ErrorNumber);
                        //nUnit.Failure("Script Compilation", $"Compilation Error #{errIndex}", err.ErrorText, $"{scriptFile} line {err.Line}, column {err.Column}");
                    }
                    return(true);
                }
                try
                {
                    h.BeginUpdate("script");
                    dyn.Invoke(h.Model, null);
                    h.EndUpdateAll();
                }
                catch (Exception ex)
                {
                    Error("Script execution error: " + ex.Message);
                    return(true);
                }
            }

            if (doCheckDs > -1)
            {
                Console.WriteLine("Checking source schema...");
                ScriptHelper.SchemaCheck(h.Model);
            }

            if (!string.IsNullOrEmpty(buildOutputPath))
            {
                Console.WriteLine("Building Model.bim file...");
                if (buildReplaceId != null)
                {
                    h.Database.Name = buildReplaceId; h.Database.ID = buildReplaceId;
                }
                h.Save(buildOutputPath, SaveFormat.ModelSchemaOnly, SerializeOptions.Default);
            }
            else if (!string.IsNullOrEmpty(saveToFolderOutputPath))
            {
                Console.WriteLine("Saving Model.bim file to Folder Output Path ...");
                if (buildReplaceId != null)
                {
                    h.Database.Name = buildReplaceId; h.Database.ID = buildReplaceId;
                }

                //Note the last parameter, we use whatever SerializeOptions are already in the file
                h.Save(saveToFolderOutputPath, SaveFormat.TabularEditorFolder, null, true);
            }

            var replaceMap = new Dictionary <string, string>();

            var analyze = upperArgList.IndexOf("-ANALYZE");

            if (analyze == -1)
            {
                analyze = upperArgList.IndexOf("-A");
            }
            if (analyze > -1)
            {
                var rulefile = analyze + 1 < argList.Count ? argList[analyze + 1] : "";
                if (rulefile.StartsWith("-") || string.IsNullOrEmpty(rulefile))
                {
                    rulefile = null;
                }

                Console.WriteLine("Running Best Practice Analyzer...");
                Console.WriteLine("=================================");

                var analyzer = new BPA.Analyzer();
                analyzer.SetModel(h.Model, h.SourceType == ModelSourceType.Database ? null : FileSystemHelper.DirectoryFromPath(h.Source));

                BPA.BestPracticeCollection suppliedRules = null;
                if (!string.IsNullOrEmpty(rulefile))
                {
                    if (!File.Exists(rulefile))
                    {
                        Error("Rulefile not found: {0}", rulefile);
                        return(true);
                    }
                    try
                    {
                        suppliedRules = BPA.BestPracticeCollection.GetCollectionFromFile(Environment.CurrentDirectory, rulefile);
                    }
                    catch
                    {
                        Error("Invalid rulefile: {0}", rulefile);
                        return(true);
                    }
                }

                IEnumerable <BPA.AnalyzerResult> bpaResults;
                if (suppliedRules == null)
                {
                    bpaResults = analyzer.AnalyzeAll();
                }
                else
                {
                    var effectiveRules = analyzer.GetEffectiveRules(false, false, true, true, suppliedRules);
                    bpaResults = analyzer.Analyze(effectiveRules);
                }

                bool none = true;
                foreach (var res in bpaResults.Where(r => !r.Ignored))
                {
                    if (res.InvalidCompatibilityLevel)
                    {
                        Console.WriteLine("Skipping rule '{0}' as it does not apply to Compatibility Level {1}.", res.RuleName, h.CompatibilityLevel);
                    }
                    else
                    if (res.RuleHasError)
                    {
                        none = false;
                        Error("Error on rule '{0}': {1}", res.RuleName, res.RuleError);
                    }
                    else
                    {
                        none = false;
                        if (res.Object != null)
                        {
                            var text = string.Format("{0} {1} violates rule \"{2}\"",
                                                     res.Object.GetTypeName(),
                                                     (res.Object as IDaxObject)?.DaxObjectFullName ?? res.ObjectName,
                                                     res.RuleName
                                                     );
                            if (res.Rule.Severity <= 1)
                            {
                                Console.WriteLine(text);
                            }
                            else if (res.Rule.Severity == 2)
                            {
                                Warning(text);
                            }
                            else if (res.Rule.Severity >= 3)
                            {
                                Error(text);
                            }
                        }
                    }
                }
                if (none)
                {
                    Console.WriteLine("No objects in violation of Best Practices.");
                }
                Console.WriteLine("=================================");
            }

            var deploy = upperArgList.IndexOf("-DEPLOY");

            if (deploy == -1)
            {
                deploy = upperArgList.IndexOf("-D");
            }
            if (deploy > -1)
            {
                var serverName = argList.Skip(deploy + 1).FirstOrDefault(); if (serverName != null && serverName.StartsWith("-"))
                {
                    serverName = null;
                }
                var databaseID = argList.Skip(deploy + 2).FirstOrDefault(); if (databaseID != null && databaseID.StartsWith("-"))
                {
                    databaseID = null;
                }

                var conn = upperArgList.IndexOf("-CONNECTIONS");
                if (conn == -1)
                {
                    conn = upperArgList.IndexOf("-C");
                }
                if (conn > -1)
                {
                    var replaces = argList.Skip(conn + 1).TakeWhile(s => s[0] != '-').ToList();

                    if (replaces.Count > 0 && replaces.Count % 2 == 0)
                    {
                        // Placeholder replacing:
                        for (var index = 0; index < replaces.Count; index = index + 2)
                        {
                            replaceMap.Add(replaces[index], replaces[index + 1]);
                        }
                    }
                }

                string userName = null;
                string password = null;
                var    options  = DeploymentOptions.StructureOnly;

                var switches = args.Skip(deploy + 1).Where(arg => arg.StartsWith("-")).Select(arg => arg.ToUpper()).ToList();

                if (string.IsNullOrEmpty(serverName) || string.IsNullOrEmpty(databaseID))
                {
                    Error("Invalid argument syntax.\n");
                    OutputUsage();
                    return(true);
                }
                if (switches.Contains("-L") || switches.Contains("-LOGIN"))
                {
                    var switchPos = upperArgList.IndexOf("-LOGIN"); if (switchPos == -1)
                    {
                        switchPos = upperArgList.IndexOf("-L");
                    }
                    userName = argList.Skip(switchPos + 1).FirstOrDefault(); if (userName != null && userName.StartsWith("-"))
                    {
                        userName = null;
                    }
                    password = argList.Skip(switchPos + 2).FirstOrDefault(); if (password != null && password.StartsWith("-"))
                    {
                        password = null;
                    }
                    if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(password))
                    {
                        Error("Missing username or password.\n");
                        OutputUsage();
                        return(true);
                    }
                    switches.Remove("-L"); switches.Remove("-LOGIN");
                }
                if (switches.Contains("-O") || switches.Contains("-OVERWRITE"))
                {
                    options.DeployMode = DeploymentMode.CreateOrAlter;
                    switches.Remove("-O"); switches.Remove("-OVERWRITE");
                }
                else
                {
                    options.DeployMode = DeploymentMode.CreateDatabase;
                }
                if (switches.Contains("-P") || switches.Contains("-PARTITIONS"))
                {
                    options.DeployPartitions = true;
                    switches.Remove("-P"); switches.Remove("-PARTITIONS");
                }
                if (switches.Contains("-C") || switches.Contains("-CONNECTIONS"))
                {
                    options.DeployConnections = true;
                    switches.Remove("-C"); switches.Remove("-CONNECTIONS");
                }
                if (switches.Contains("-R") || switches.Contains("-ROLES"))
                {
                    options.DeployRoles = true;
                    switches.Remove("-R"); switches.Remove("-ROLES");

                    if (switches.Contains("-M") || switches.Contains("-MEMBERS"))
                    {
                        options.DeployRoleMembers = true;
                        switches.Remove("-M"); switches.Remove("-MEMBERS");
                    }
                }
                var    xmla_scripting_only = switches.Contains("-X") || switches.Contains("-XMLA");
                string xmla_script_file    = null;
                if (xmla_scripting_only)
                {
                    var switchPos = upperArgList.IndexOf("-XMLA"); if (switchPos == -1)
                    {
                        switchPos = upperArgList.IndexOf("-X");
                    }
                    xmla_script_file = argList.Skip(switchPos + 1).FirstOrDefault(); if (String.IsNullOrWhiteSpace(xmla_script_file) || xmla_script_file.StartsWith("-"))
                    {
                        xmla_script_file = null;
                    }
                    if (string.IsNullOrEmpty(xmla_script_file))
                    {
                        Error("Missing xmla_script_file.\n");
                        OutputUsage();
                        return(true);
                    }
                    switches.Remove("-X");
                    switches.Remove("-XMLA");
                }

                /*if(switches.Count > 0)
                 * {
                 *  Error("Unknown switch {0}\n", switches[0]);
                 *  OutputUsage();
                 *  return true;
                 * }*/

                try
                {
                    if (replaceMap.Count > 0)
                    {
                        Console.WriteLine("Switching connection string placeholders...");
                        foreach (var map in replaceMap)
                        {
                            h.Model.DataSources.SetPlaceholder(map.Key, map.Value);
                        }
                    }

                    var cs = string.IsNullOrEmpty(userName) ? TabularConnection.GetConnectionString(serverName) :
                             TabularConnection.GetConnectionString(serverName, userName, password);
                    if (xmla_scripting_only)
                    {
                        Console.WriteLine("Generating XMLA/TMSL script...");
                        var s = new TOM.Server();
                        s.Connect(cs);
                        var xmla = TabularDeployer.GetTMSL(h.Database, s, databaseID, options);
                        using (var sw = new StreamWriter(xmla_script_file))
                        {
                            sw.Write(xmla);
                        }
                        Console.WriteLine("XMLA/TMSL script generated.");
                    }
                    else
                    {
                        Console.WriteLine("Deploying...");
                        UpdateDeploymentMetadata(h.Model, DeploymentModeMetadata.CLI);
                        var deploymentResult = TabularDeployer.Deploy(h, cs, databaseID, options);
                        Console.WriteLine("Deployment succeeded.");
                        foreach (var err in deploymentResult.Issues)
                        {
                            if (errorOnDaxErr)
                            {
                                Error(err);
                            }
                            else
                            {
                                Warning(err);
                            }
                        }
                        foreach (var err in deploymentResult.Warnings)
                        {
                            Warning(err);
                        }
                        foreach (var err in deploymentResult.Unprocessed)
                        {
                            if (warnOnUnprocessed)
                            {
                                Warning(err);
                            }
                            else
                            {
                                Console.WriteLine(err);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Error($"{(xmla_scripting_only ? "Script generation" : "Deployment")} failed! {ex.Message}");
                }
            }
            if (testRun != null)
            {
                testRun.SerializeAsVSTest(testRunFile);
                Console.WriteLine("VSTest XML file saved: " + testRunFile);
            }

            return(true);
        }
Exemplo n.º 8
0
        private static string DeployExistingTMSL(TOM.Database db, TOM.Server server, string dbId, DeploymentOptions options)
        {
            var orgDb = server.Databases[dbId];

            var rawScript = TOM.JsonScripter.ScriptCreateOrReplace(db);
            var jObj      = JObject.Parse(rawScript);

            jObj["createOrReplace"]["object"]["database"] = dbId;
            jObj["createOrReplace"]["database"]["id"]     = dbId;
            jObj["createOrReplace"]["database"]["name"]   = orgDb.Name;

            var model = jObj.SelectToken("createOrReplace.database.model");

            var roles = model["roles"] as JArray;

            if (!options.DeployRoles)
            {
                // Remove roles if present and add original:
                roles          = new JArray();
                model["roles"] = roles;
                foreach (var role in orgDb.Model.Roles)
                {
                    roles.Add(JObject.Parse(TOM.JsonSerializer.SerializeObject(role)));
                }
            }
            else if (roles != null && !options.DeployRoleMembers)
            {
                foreach (var role in roles)
                {
                    var members = new JArray();
                    role["members"] = members;

                    // Remove members if present and add original:
                    var roleName = role["name"].Value <string>();
                    if (orgDb.Model.Roles.Contains(roleName))
                    {
                        foreach (var member in orgDb.Model.Roles[roleName].Members)
                        {
                            members.Add(JObject.Parse(TOM.JsonSerializer.SerializeObject(member)));
                        }
                    }
                }
            }

            if (!options.DeployConnections)
            {
                // Remove dataSources if present
                var dataSources = new JArray();
                model["dataSources"] = dataSources;
                foreach (var ds in orgDb.Model.DataSources)
                {
                    dataSources.Add(JObject.Parse(TOM.JsonSerializer.SerializeObject(ds)));
                }
            }

            if (!options.DeployPartitions)
            {
                var tables = jObj.SelectToken("createOrReplace.database.model.tables") as JArray;
                foreach (var table in tables)
                {
                    var tableName = table["name"].Value <string>();
                    if (orgDb.Model.Tables.Contains(tableName))
                    {
                        var t = orgDb.Model.Tables[tableName];

                        var partitions = new JArray();
                        table["partitions"] = partitions;
                        foreach (var pt in t.Partitions)
                        {
                            partitions.Add(JObject.Parse(TOM.JsonSerializer.SerializeObject(pt)));
                        }
                    }
                }
            }

            return(jObj.ToString());
        }
Exemplo n.º 9
0
        public static SortedDictionary <string, double> ExecuteGroupByDad(List <string> slicerList, Dictionary <string, object> filterList, DeviceType deviceType)
        {
            SortedDictionary <string, double> dadDictionary = new SortedDictionary <string, double>();

            var algtelPassword = KeyVaultUtil.GetSecretInPlaintext(KeyVaultUtil.SharedAccountName);

            string userId = KeyVaultUtil.SharedAccountName + "@microsoft.com";

            string ssasServer = "asazure://centralus.asazure.windows.net/datapipelinesaas";

            string ConnectionString = $"Password={algtelPassword};Persist Security Info=True;User ID={userId};Data Source = " + ssasServer + ";";

            using (Microsoft.AnalysisServices.Tabular.Server server = new Microsoft.AnalysisServices.Tabular.Server())
            {
                server.Connect(ConnectionString);

                string databaseName = deviceType == DeviceType.Hololens ? "HoloLensHackathon" : "OasisHackathon";

                Microsoft.AnalysisServices.Tabular.Database tabularDatabase = null;

                tabularDatabase = server.Databases.FindByName(databaseName);

                string tableName = deviceType == DeviceType.Hololens ? "HoloLens Product Engagement" : "Oasis Product Engagement";

                var oasisProductEngagamentTable = tabularDatabase.Model.Tables.Find(tableName);

                string measureName = deviceType == DeviceType.Hololens ? "DAD (R28)" : "PC DAD (R28)";

                var dadMeasure = oasisProductEngagamentTable.Measures.Where(e => e.Name == measureName).FirstOrDefault();

                StringBuilder stbr = new StringBuilder();

                foreach (var slicer in slicerList)
                {
                    stbr.Append($"'{tableName}'[" + slicer + "]");
                    stbr.Append(" , ");
                }

                string str = stbr.ToString();
                str = str.TrimEnd();
                str = str.Remove(str.LastIndexOf(","));

                string newTableName = (filterList.Count > 0) ? GetStringForGroupBy(tableName, filterList) : $"'{tableName}'";

                string queryString = $"EVALUATE SUMMARIZE({newTableName}, {str}, \"Group by measure\"," + dadMeasure.Expression + ")";

                string msolapConnectionString = $"Provider=MSOLAP;Data Source={ssasServer};Initial Catalog={databaseName};User ID = {userId};Password = {algtelPassword};Persist Security Info=True; Impersonation Level=Impersonate;";

                using (var connection = new OleDbConnection(msolapConnectionString))
                {
                    connection.Open();
                    using (var command = new OleDbCommand(queryString, connection))
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            int columns = reader.FieldCount;

                            while (reader.Read())
                            {
                                string keyString = string.Empty;
                                for (int i = 0; i < columns - 1; i++)
                                {
                                    keyString += reader[i].ToString() + ", ";
                                }

                                keyString = keyString.TrimEnd();
                                keyString = keyString.Remove(keyString.LastIndexOf(","));

                                double value = double.Parse(reader[columns - 1].ToString());
                                value = Math.Round(value, 0);
                                dadDictionary.Add(keyString, value);
                            }
                        }
                    }
                }
            }

            return(dadDictionary);
        }
Exemplo n.º 10
0
        public static void CreateNewDadMeasure(string measureName, string measureDescription, Dictionary <string, object> filterKeyValuePairs, DeviceType deviceType)
        {
            var algtelPassword = KeyVaultUtil.GetSecretInPlaintext(KeyVaultUtil.SharedAccountName);

            string userId = KeyVaultUtil.SharedAccountName + "@microsoft.com";

            string ssasServer = "asazure://centralus.asazure.windows.net/datapipelinesaas";

            string ConnectionString = $"Password={algtelPassword};Persist Security Info=True;User ID={userId};Data Source = " + ssasServer + ";";

            using (Microsoft.AnalysisServices.Tabular.Server server = new Microsoft.AnalysisServices.Tabular.Server())
            {
                server.Connect(ConnectionString);

                string databaseName = deviceType == DeviceType.Hololens ? "HoloLensHackathon" : "OasisHackathon";

                Microsoft.AnalysisServices.Tabular.Database tabularDatabase = null;

                tabularDatabase = server.Databases.FindByName(databaseName);

                string tableName = deviceType == DeviceType.Hololens ? "HoloLens Product Engagement" : "Oasis Product Engagement";

                var oasisProductEngagementTable = tabularDatabase.Model.Tables.Find(tableName);

                var newDadMeasure = oasisProductEngagementTable.Measures.Where(e => e.Name == measureName).FirstOrDefault();

                if (newDadMeasure != null)
                {
                    oasisProductEngagementTable.Measures.Remove(newDadMeasure);
                }

                StringBuilder stbr = new StringBuilder();
                foreach (var k in filterKeyValuePairs)
                {
                    if (k.Key == "Is Mainstream")
                    {
                        stbr.Append($"'{tableName}'[" + k.Key + "] = " + k.Value);
                    }
                    else
                    {
                        stbr.Append($"'{tableName}'[" + k.Key + "] = \"" + k.Value + "\"");
                    }

                    stbr.Append(" , ");
                }

                string str = stbr.ToString();
                str = str.TrimEnd();
                str = str.Remove(str.LastIndexOf(","));

                string oldDadMeasureName = deviceType == DeviceType.Hololens ? "DAD (R28)" : "PC DAD (R28)";

                var oldDadMeasure = oasisProductEngagementTable.Measures.Where(e => e.Name == oldDadMeasureName).FirstOrDefault();

                string newDadMeasureExpression = string.Empty;

                if (deviceType == DeviceType.Hololens)
                {
                    newDadMeasureExpression = GetLatestDadExpression(oldDadMeasure.Expression, filterKeyValuePairs, tableName);
                }
                else
                {
                    string oldDadMeasureExpression = oldDadMeasure.Expression;
                    oldDadMeasureExpression = oldDadMeasureExpression.Substring(oldDadMeasureExpression.IndexOf("AVERAGEX"));
                    oldDadMeasureExpression = oldDadMeasureExpression.Remove(oldDadMeasureExpression.LastIndexOf(")"));

                    newDadMeasureExpression = GetLatestDadExpression(oldDadMeasureExpression, filterKeyValuePairs, tableName);
                }

                string displayFolder = deviceType == DeviceType.Hololens ? @"[Measures]\Device Count" : @"[Measures]\PC Counting";

                oasisProductEngagementTable.Measures.Add(CreateMeasure(measureName, newDadMeasureExpression, measureDescription, displayFolder));

                tabularDatabase.Model.SaveChanges();
            }
        }