Ejemplo n.º 1
0
        private static DataTable GetClusterCharacteristics(string strModel, string strClusterUniqueID, double dThreshold)
        {
            //if we don't know the path to the system data mining sprocs assembly, get it
            if (_cachedSystemDataMiningSprocsPath.Length == 0)
            {
                Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
                svr.Connect(Context.CurrentServerID);
                ClrAssembly ass = (ClrAssembly)svr.Assemblies.GetByName("System");
                if (ass == null)
                {
                    throw new Exception("System (data mining sprocs) assembly not found");
                }
                foreach (ClrAssemblyFile file in ass.Files)
                {
                    if (file.Type == ClrAssemblyFileType.Main)
                    {
                        lock (_cachedSystemDataMiningSprocsPath) _cachedSystemDataMiningSprocsPath = file.Name;
                        break;
                    }
                }
                svr.Disconnect();
            }

            //get the DataMining sprocs assembly and call the GetClusterCharacteristics function
            System.Reflection.Assembly asAss = System.Reflection.Assembly.LoadFile(_cachedSystemDataMiningSprocsPath);
            Type   t           = asAss.GetType("Microsoft.AnalysisServices.System.DataMining.Clustering");
            object oClustering = t.GetConstructor(new Type[] { }).Invoke(new object[] { });

            return((DataTable)t.InvokeMember("GetClusterCharacteristics", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.InvokeMethod, null, oClustering, new object[] { strModel, strClusterUniqueID, dThreshold }));
        }
Ejemplo n.º 2
0
 public void Disconnect()
 {
     if (Server != null & Server.Connected == true)
     {
         Server.Disconnect();
     }
 }
Ejemplo n.º 3
0
        private Server ConnectAnalysisServices(string strDBServerName, string strProviderName)
        {
            try
            {
                AppendLogLine("");
                AppendLogLine(string.Format("Connecting to the Analysis Services: {0}...", strDBServerName));

                Server objServer     = new Server();
                string strConnection = string.Empty;
                if (CbIntegratedSecurity)
                {
                    //Direct Connection
                    strConnection = string.Format("Data Source={0};Integrated Security=SSPI;Provider={1}", strDBServerName, strProviderName);
                }
                else
                {
                    //HTTP connection
                    strConnection = strConnection +
                                    string.Format(@"Data Source=http://{0}/;Provider={1};User Id={2};Password={3}",
                                                  strDBServerName, strProviderName, CbUserId, CbPassword);
                }

                AppendLogLine(string.Format("Analysis services connection string: {0}", strConnection));
                //Disconnect from current connection if it's currently connected.
                if (objServer.Connected)
                {
                    objServer.Disconnect();
                }
                else
                {
                    objServer.Connect(strConnection);
                }

                AppendLogLine(string.Format("  Product name    : {0}", objServer.ProductName));
                AppendLogLine(string.Format("  Product edition : {0}", objServer.Edition));
                AppendLogLine(string.Format("  Version         : {0}", objServer.Version));
                foreach (Database aAnalysisDatabase in objServer.Databases)
                {
                    AppendLogLine(string.Format("  Database        : [{0}] with cubes:", aAnalysisDatabase.Name));
                    foreach (Cube aCube in aAnalysisDatabase.Cubes)
                    {
                        AppendLogLine(string.Format("             Cube : [{0}]", aCube.Name));
                    }
                }
                AppendLogLine("");

                return(objServer);
            }
            catch (Exception ex)
            {
                AppendLogLine("Error in Connecting to the Analysis Services. Error Message -> " + ex.Message);
                return(null);
            }
        }
Ejemplo n.º 4
0
 private void DisConnectSSASServer()
 {
     if (ssasServer != null)
     {
         if (ssasServer.GetConnectionState(true) == System.Data.ConnectionState.Open)
         {
             ssasServer.Disconnect(true);
         }
         ssasServer.Dispose();
     }
     ssasServer = null;
 }
Ejemplo n.º 5
0
        public void TableCount1103()
        {
            using (Amo.Server server = new Amo.Server())
            {
                server.Connect("localhost\\tb");

                Amo.Database db = server.Databases.FindByName("Test1103_Target");
                Assert.IsNotNull(db);

                Assert.AreEqual(3, db.Cubes[0].Dimensions.Count);
                server.Disconnect();
            }
        }
Ejemplo n.º 6
0
 public DataTable ListFunctions()
 {
     Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
     svr.Connect(Context.CurrentServerID);
     try
     {
         return(getFunctionList(svr.Assemblies));
     }
     finally
     {
         svr.Disconnect();
     }
 }
Ejemplo n.º 7
0
        static void ProcessDimensions()
        {
            string srv_name = @"SCRBSQLDEFRM637";
            string db_name  = "Intermodal_TEST";

            //TextWriter tw = new StreamWriter("date.txt", true);

            Microsoft.AnalysisServices.Server   srv;
            Microsoft.AnalysisServices.Database db;

            srv = new Microsoft.AnalysisServices.Server();
            try
            {
                srv.Connect(srv_name);
                logMessageFmt("Databases on [{0}]: {1}", srv_name, srv.Databases.Count);

                db = srv.Databases.FindByName(db_name);
                foreach (Dimension d in db.Dimensions)
                {
                    logMessageFmt("Processing {0}(ID=[{1}])...", d.Name, d.ID);
                    try
                    {
                        if (d.State != AnalysisState.Processed)
                        {
                            d.Process(ProcessType.ProcessFull);
                            logMessage("---> Processed successfully.");
                        }
                        else
                        {
                            logMessage("---> Already processed.");
                        }
                    }
                    catch (Exception e)
                    {
                        logMessageFmt("---> ERROR: {0}", e.Message);
                    }
                }
                logMessage("Done.");
                Console.ReadKey();
            }
            finally
            {
                if (srv.Connected == true)
                {
                    srv.Disconnect();
                }
            }
        }
        // Clears the cache for the current database
        // This method uses Server.Execute() as I need to establish an AMO
        // connection anyway in order to find the CubeID from the CubeName
        // Other methods in this class use the XmlaClient objects to execute
        // the Xmla requests.
        public void ClearCache(string cubeName)
        {
            // only way to get a DatabaseID from a Database name appears to be to use AMO
            string dbId = "";

            Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
            using (svr)
            {
                svr.Connect(Context.CurrentServerID);
                try
                {
                    Database db = svr.Databases.FindByName(Context.CurrentDatabaseName);
                    dbId = db.ID;
                    if (cubeName.Length != 0)
                    {
                        Cube c = db.Cubes.FindByName(cubeName);
                        if (c != null)
                        {
                            cubeName = "<CubeID>" + c.ID + "</CubeID>";
                        }
                        else
                        {
                            throw new Exception("The cube '" + cubeName + "' does not exist in the " + Context.CurrentDatabaseName + " database.");
                        }
                    }

                    // execute clear cache based on the ID
                    string clearCmd = string.Format(CLEARCACHE_TEMPLATE, dbId, cubeName);
                    svr.Execute(clearCmd);
                }
                catch
                {   // re-throw any exception
                    throw;
                }
                finally
                {
                    // clean up
                    svr.Disconnect();
                }
            }
        }
Ejemplo n.º 9
0
 public DataTable ListFunctions(string databaseName)
 {
     Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
     svr.Connect(Context.CurrentServerID);
     Microsoft.AnalysisServices.Database db = svr.Databases.GetByName(databaseName);
     if (db == null)
     {
         throw new Exception(string.Format("Unable to find a database called '{0}'", databaseName));
     }
     else
     {
         try
         {
             return(getFunctionList(db.Assemblies));
         }
         finally
         {
             svr.Disconnect();
         }
     }
 }
Ejemplo n.º 10
0
 private bool VerifyServer()
 {
     try
     {
         if (Options == null)
         {
             return(false);
         }
         if (!srv.Connected || srv.ConnectionInfo.Server != txtServer.Text.Trim())
         {
             if (srv.Connected)
             {
                 srv.Disconnect();
             }
             srv.Connect("Integrated Security=SSPI;Persist Security Info=False;Data Source=" + txtServer.Text.Trim());
         }
         return(true);
     }
     catch (Exception exc)
     {
         throw exc;
     }
 }
        public static void AssignValue(string subCube, MDXValue valueToAssign)
        {
            Microsoft.AnalysisServices.Server mServer;
            Database mDB;
            Cube mCube;
            MdxScript mMdxScript;
            mServer = new Microsoft.AnalysisServices.Server();
            try
            {
                mServer.Connect("*");
                mDB = mServer.Databases.GetByName(Context.CurrentDatabaseName);
                mCube = mDB.Cubes.GetByName(Context.CurrentCube.Name);

                mMdxScript = mCube.DefaultMdxScript;

                mMdxScript.Commands.Add(new Command(scriptComment() + subCube + " = " + valueToAssign.ToString() + ";" + System.Environment.NewLine));
                mMdxScript.Update();
            }
            finally
            {
                mServer.Disconnect();
            }
        }
Ejemplo n.º 12
0
        private static void ReadPartitionInfo()
        {
            string srv_name  = @"SCRBMSBDK000660";
            string db_name   = "BICC_Intermodal";
            string cube_name = "Intermodal Cube";

            //TextWriter tw = new StreamWriter("date.txt", true);

            Microsoft.AnalysisServices.Server       srv;
            Microsoft.AnalysisServices.Database     db;
            Microsoft.AnalysisServices.Cube         cube;
            Microsoft.AnalysisServices.MeasureGroup mg;
            Microsoft.AnalysisServices.Partition    part;
            srv = new Microsoft.AnalysisServices.Server();
            try
            {
                srv.Connect(srv_name);
                logMessageFmt("Connected to {0}", srv_name);
                db   = srv.Databases.FindByName(db_name);
                cube = db.Cubes.FindByName(cube_name);
                string query, slice;

                mg = cube.MeasureGroups.FindByName("USD - Equipment Level");

                logMessageFmt("Database: [{0}], cube: [{1}], measure group: [{2}].", db_name, cube_name, mg.Name);
                //DropMeasureGroupPartitions(mg);
                //CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2016, 1, 12, 120000000);
            }
            finally
            {
                if (srv.Connected == true)
                {
                    srv.Disconnect();
                }
            }
        }
Ejemplo n.º 13
0
        public static void AssignMDXExpression(string subCube, string expressionToAssign)
        {
            Microsoft.AnalysisServices.Server mServer;
            Database  mDB;
            Cube      mCube;
            MdxScript mMdxScript;

            mServer = new Microsoft.AnalysisServices.Server();
            try
            {
                mServer.Connect("*");
                mDB   = mServer.Databases.GetByName(Context.CurrentDatabaseName);
                mCube = mDB.Cubes.GetByName(Context.CurrentCube.Name);

                mMdxScript = mCube.DefaultMdxScript;

                mMdxScript.Commands.Add(new Command(scriptComment() + subCube + " = " + expressionToAssign.ToString() + ";" + System.Environment.NewLine));
                mMdxScript.Update();
            }
            finally
            {
                mServer.Disconnect();
            }
        }
Ejemplo n.º 14
0
        public static void DeployPerspectives(ProjectItem projItem, DTE2 ApplicationObject)
        {
            Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;

            if (oCube.Perspectives.Count == 0)
            {
                MessageBox.Show("There are no perspectives defined in this cube yet.");
                return;
            }

            try
            {
                ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 1, 5);

                FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1));
                if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly )
                {
                    //Save the cube
                    projItem.Save("");
                }

                ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 2, 5);

                // extract deployment information
                DeploymentSettings deploySet = new DeploymentSettings(projItem);

                // use xlst to create xmla alter command
                XslCompiledTransform xslt = new XslCompiledTransform();
                XmlReader xsltRdr;
                XmlReader xrdr;

                // read xslt from embedded resource
                xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployPerspectives));
                using ((xsltRdr))
                {
                    // read content from .cube file
                    xrdr = XmlReader.Create(projItem.get_FileNames(1));
                    using (xrdr)
                    {


                        
                        ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 3, 5);
                        // Connect to Analysis Services
                        Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
                        svr.Connect(deploySet.TargetServer);
                        ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 4, 5);
                        // execute the xmla
                        try
                        {
                            // Build up the Alter perspectives command using XSLT against the .cube file
                            XslCompiledTransform xslta = new XslCompiledTransform();
                            StringBuilder sb = new StringBuilder();
                            XmlWriterSettings xws = new XmlWriterSettings();
                            xws.OmitXmlDeclaration = true;
                            xws.ConformanceLevel = ConformanceLevel.Fragment;
                            XmlWriter xwrtr = XmlWriter.Create(sb, xws);

                            xslta.Load(xsltRdr);
                            XsltArgumentList xslarg = new XsltArgumentList();

                            Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
                            if (targetDB == null)
                            {
                                throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
                            }
                            string targetDatabaseID = targetDB.ID;
                            xslarg.AddParam("TargetDatabase", "", targetDatabaseID);
                            xslta.Transform(xrdr, xslarg, xwrtr);

                            Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
                            if (oServerCube == null)
                            {
                                throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
                            }

                            //drop any perspectives which don't exist
                            svr.CaptureXml = true;
                            for (int i = 0; i < oServerCube.Perspectives.Count; i++)
                            {
                                Perspective p = oServerCube.Perspectives[i];
                                if (!oCube.Perspectives.Contains(p.ID))
                                {
                                    p.Drop();
                                    i--;
                                }
                            }
                            svr.CaptureXml = false;
                            try
                            {
                                if (svr.CaptureLog.Count > 0)
                                    svr.ExecuteCaptureLog(true, false);
                            }
                            catch (System.Exception ex)
                            {
                                throw new System.Exception("Error dropping perspective that were deleted in the source code. " + ex.Message);
                            }


                            // update the perspectives
                            XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());

                            StringBuilder sbErr = new StringBuilder();
                            for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
                            {
                                for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
                                {
                                    sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
                                }
                            }
                            if (sbErr.Length > 0)
                            {
                                MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Perspectives");
                            }



                            try
                            {
                                // Building the project means that the .asdatabase file gets re-built so that
                                // we do not break the Deployment Wizard.
                                projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);

                            }
                            catch (System.Exception ex)
                            {
                                MessageBox.Show(ex.Message);
                            }
                        }
                        catch (System.Exception ex)
                        {
                            if (MessageBox.Show("The following error occured while trying to deploy the perspectives\r\n"
                                                + ex.Message
                                                + "\r\n\r\nDo you want to see a stack trace?"
                                            , "BIDS Helper - Deploy Perspectives"
                                            , MessageBoxButtons.YesNo
                                            , MessageBoxIcon.Error
                                            , MessageBoxDefaultButton.Button2) == DialogResult.Yes)
                            {
                                MessageBox.Show(ex.StackTrace);
                            }
                        }
                        finally
                        {
                            ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 5, 5);
                            // report any results back (status bar?)
                            svr.Disconnect();
                        }
                    }
                }
            }
            finally
            {
                ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(false, "Deploying perspectives", 5, 5);
            }
        }
        public static void DeployAggDesigns(ProjectItem projItem, DTE2 ApplicationObject)
        {
            Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;

            bool bFoundAggDesign = false;
            foreach (MeasureGroup mg in oCube.MeasureGroups)
            {
                if (mg.AggregationDesigns.Count > 0)
                {
                    bFoundAggDesign = true;
                    break;
                }
            }
            if (!bFoundAggDesign)
            {
                MessageBox.Show("There are no aggregation designs defined in this cube yet.");
                return;
            }

            if (MessageBox.Show("This command deploys just the aggregation designs in this cube. It does not change which aggregation design is assigned to each partition.\r\n\r\nYou should run a ProcessIndex command from Management Studio on this cube after aggregation designs have been deployed.\r\n\r\nDo you wish to continue?", "BIDS Helper - Deploy Aggregation Designs", MessageBoxButtons.YesNo) != DialogResult.Yes)
            {
                return;
            }

            try
            {
                ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 1, 5);

                string sPartitionsFileName = projItem.get_FileNames(1);
                sPartitionsFileName = sPartitionsFileName.Substring(0, sPartitionsFileName.Length - 5) + ".partitions";

                // Check if the file is read-only (and probably checked in to a source control system)
                // before attempting to save. (issue: 10327 )
                FileAttributes fa = System.IO.File.GetAttributes(sPartitionsFileName);
                if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly)
                {
                    //TODO - prompt before saving?
                    //Save the cube
                    projItem.Save("");
                }

                ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 2, 5);

                // extract deployment information
                DeploymentSettings deploySet = new DeploymentSettings(projItem);

                // use xlst to create xmla alter command
                XslCompiledTransform xslt = new XslCompiledTransform();
                XmlReader xsltRdr;
                XmlReader xrdr;

                // read xslt from embedded resource
                xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployAggDesigns));
                using ((xsltRdr))
                {
                    // read content from .partitions file
                    xrdr = XmlReader.Create(sPartitionsFileName);
                    using (xrdr)
                    {
                        ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 3, 5);
                        // Connect to Analysis Services
                        Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
                        svr.Connect(deploySet.TargetServer);
                        ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 4, 5);
                        // execute the xmla
                        try
                        {
                            // Build up the Alter MdxScript command using XSLT against the .partitions file
                            XslCompiledTransform xslta = new XslCompiledTransform();
                            StringBuilder sb = new StringBuilder();
                            XmlWriterSettings xws = new XmlWriterSettings();
                            xws.OmitXmlDeclaration = true;
                            xws.ConformanceLevel = ConformanceLevel.Fragment;
                            XmlWriter xwrtr = XmlWriter.Create(sb, xws);

                            xslta.Load(xsltRdr);
                            XsltArgumentList xslarg = new XsltArgumentList();

                            Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
                            if (targetDB == null)
                            {
                                throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
                            }
                            xslarg.AddParam("TargetDatabase", "", targetDB.ID);
                            xslarg.AddParam("TargetCubeID", "", oCube.ID);
                            xslta.Transform(xrdr, xslarg, xwrtr);

                            Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
                            if (oServerCube == null)
                            {
                                throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
                            }

                            // update the agg designs
                            XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
                            StringBuilder sbErr = new StringBuilder();
                            for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
                            {
                                for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
                                {
                                    if (!string.IsNullOrEmpty(xmlaRC[iRC].Messages[iMsg].Description))
                                        sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
                                }
                            }
                            if (sbErr.Length > 0)
                                MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Aggregation Designs");

                            projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
                        }
                        catch (System.Exception ex)
                        {
                            if (MessageBox.Show("The following error occured while trying to deploy the aggregation designs\r\n"
                                                + ex.Message
                                                + "\r\n\r\nDo you want to see a stack trace?"
                                            , "BIDSHelper - Deploy Aggregation Designs"
                                            , MessageBoxButtons.YesNo
                                            , MessageBoxIcon.Error
                                            , MessageBoxDefaultButton.Button2) == DialogResult.Yes)
                            {
                                MessageBox.Show(ex.StackTrace);
                            }
                        }
                        finally
                        {
                            ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 5, 5);
                            svr.Disconnect();
                        }
                    }
                }
            }
            finally
            {
                ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(false, "Deploying Aggregation Designs", 5, 5);
            }
        }
        public static void DeployScript(ProjectItem projItem, DTE2 ApplicationObject)
        {
            Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
            try
            {
                //validate the script because deploying an invalid script makes cube unusable
                Microsoft.AnalysisServices.Design.Scripts script = new Microsoft.AnalysisServices.Design.Scripts(oCube);

            }
            catch (Microsoft.AnalysisServices.Design.ScriptParsingFailed ex)
            {
                string throwaway = ex.Message;
                MessageBox.Show("MDX Script in " + oCube.Name + " is not valid.", "Problem Deploying MDX Script");
                return;
            }

            if (oCube.MdxScripts.Count == 0)
            {
                MessageBox.Show("There is no MDX script defined in this cube yet.");
                return;
            }

            try
            {
                ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 1, 5);

                // Check if the file is read-only (and probably checked in to a source control system)
                // before attempting to save. (issue: 10327 )
                FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1));
                if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly )
                {
                    //TODO - can I check and maybe prompt before saving?
                    //Save the cube
                    projItem.Save("");
                }

                ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 2, 5);

                // extract deployment information
                DeploymentSettings deploySet = new DeploymentSettings(projItem);

                // use xlst to create xmla alter command
                XslCompiledTransform xslt = new XslCompiledTransform();
                XmlReader xsltRdr;
                XmlReader xrdr;

                // read xslt from embedded resource
                xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployMdxScript));
                using ((xsltRdr))
                {
                    // read content from .cube file
                    xrdr = XmlReader.Create(projItem.get_FileNames(1));
                    using (xrdr)
                    {

                        ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 3, 5);
                        // Connect to Analysis Services
                        Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
                        svr.Connect(deploySet.TargetServer);
                        ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 4, 5);
                        // execute the xmla
                        try
                        {
                            Microsoft.AnalysisServices.Scripter scr = new Microsoft.AnalysisServices.Scripter();

                            // Build up the Alter MdxScript command using XSLT against the .cube file
                            XslCompiledTransform xslta = new XslCompiledTransform();
                            StringBuilder sb = new StringBuilder();
                            XmlWriterSettings xws = new XmlWriterSettings();
                            xws.OmitXmlDeclaration = true;
                            xws.ConformanceLevel = ConformanceLevel.Fragment;
                            XmlWriter xwrtr = XmlWriter.Create(sb, xws);

                            xslta.Load(xsltRdr);
                            XsltArgumentList xslarg = new XsltArgumentList();

                            Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
                            if (targetDB == null)
                            {
                                throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
                            }
                            string targetDatabaseID = targetDB.ID;
                            xslarg.AddParam("TargetDatabase", "", targetDatabaseID);
                            xslta.Transform(xrdr, xslarg, xwrtr);

                            // Extract the current script from the server and keep a temporary backup copy of it
                            StringBuilder sbBackup = new StringBuilder();
                            XmlWriterSettings xwSet = new XmlWriterSettings();
                            xwSet.ConformanceLevel = ConformanceLevel.Fragment;
                            xwSet.OmitXmlDeclaration = true;
                            xwSet.Indent = true;
                            XmlWriter xwScript = XmlWriter.Create(sbBackup,xwSet);

                            Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
                            if (oServerCube == null)
                            {
                                throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
                            }
                            else if (oServerCube.State == AnalysisState.Unprocessed)
                            {
                                throw new System.Exception(string.Format("The {0} cube is not processed the {1} server.", oCube.Name, deploySet.TargetServer));
                            }
                            if (oServerCube.MdxScripts.Count == 0)
                            {
                                scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { oServerCube }, xwScript, true);
                            }
                            else
                            {
                                MdxScript mdxScr = oServerCube.MdxScripts[0];
                                scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { mdxScr }, xwScript, true);
                            }
                            xwScript.Close();

                            // update the MDX Script
                            XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
                            if (xmlaRC.Count == 1 && xmlaRC[0].Messages.Count == 0)
                            {
                                // all OK - 1 result - no messages
                            }
                            else
                            {
                                    StringBuilder sbErr = new StringBuilder();
                                for (int iRC = 0; iRC < xmlaRC.Count;iRC ++)
                                {
                                    for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
                                    {
                                        sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
                                    }
                                }
                                MessageBox.Show(sbErr.ToString(),"BIDSHelper - Deploy MDX Script" );
                            }

                            // Test the MDX Script
                            AdomdConnection cn = new AdomdConnection("Data Source=" + deploySet.TargetServer + ";Initial Catalog=" + deploySet.TargetDatabase);
                            cn.Open();
                            AdomdCommand cmd = cn.CreateCommand();
                            string qry = "SELECT {} ON 0 FROM [" + oCube.Name +"];";
                            cmd.CommandText = qry;
                            try
                            {
                                // test that we can query the cube without errors
                                cmd.Execute();

                                // Building the project means that the .asdatabase file gets re-built so that
                                // we do not break the Deployment Wizard.
                                // --
                                // This line is included in this try block so that it is only executed if we can
                                // successfully query the cube without errors.
                                projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);

                            }
                            catch (System.Exception ex)
                            {
                                // undo the deployment if we caught an exception during the deployment
                                svr.Execute(sbBackup.ToString());
                                MessageBox.Show(ex.Message);
                            }
                        }
                        catch (System.Exception ex)
                        {
                            if (MessageBox.Show("The following error occured while trying to deploy the MDX Script\r\n"
                                                + ex.Message
                                                + "\r\n\r\nDo you want to see a stack trace?"
                                            ,"BIDSHelper - Deploy MDX Script"
                                            , MessageBoxButtons.YesNo
                                            , MessageBoxIcon.Error
                                            , MessageBoxDefaultButton.Button2) == DialogResult.Yes)
                            {
                                MessageBox.Show(ex.StackTrace);
                            }
                        }
                        finally
                        {
                            ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 5, 5);
                            // report any results back (status bar?)
                            svr.Disconnect();
                        }
                    }
                }
            }
            finally
            {
                ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(false, "Deploying MdxScript", 5, 5);
            }
        }
        // Clears the cache for the current database
        // This method uses Server.Execute() as I need to establish an AMO
        // connection anyway in order to find the CubeID from the CubeName
        // Other methods in this class use the XmlaClient objects to execute
        // the Xmla requests.
        public void ClearCache(string cubeName)
        {
            // only way to get a DatabaseID from a Database name appears to be to use AMO
            string dbId = "";
            Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
            using (svr)
            {
                svr.Connect(Context.CurrentServerID);
                try
                {
                    Database db = svr.Databases.FindByName(Context.CurrentDatabaseName);
                    dbId = db.ID;
                    if (cubeName.Length != 0)
                    {
                        Cube c = db.Cubes.FindByName(cubeName);
                        if (c != null)
                        {
                            cubeName = "<CubeID>" + c.ID + "</CubeID>";
                        }
                        else
                        {
                            throw new Exception("The cube '" + cubeName + "' does not exist in the " + Context.CurrentDatabaseName + " database.");
                        }
                    }

                    // execute clear cache based on the ID
                    string clearCmd = string.Format(CLEARCACHE_TEMPLATE, dbId, cubeName);
                    svr.Execute(clearCmd);
                }
                catch
                {   // re-throw any exception
                    throw;
                }
                finally
                {
                    // clean up
                    svr.Disconnect();
                }
            }
        }
        public DataTable DiscoverPartitionSlices(string cubeName, string measureGroupName)
        {
            DataTable dt;
            DataTable dtTemp;
            string props;
            XmlaDiscover xd = new XmlaDiscover();
            int dimCount;
            int i;
            DataRow[] sameDimRows;

            string overlapText;
            bool notFirstDim = false;

            Microsoft.AnalysisServices.Server server = new Microsoft.AnalysisServices.Server();
            server.Connect("*");
            Database db = server.Databases.GetByName(Context.CurrentDatabaseName);
            Cube cube = db.Cubes.GetByName(cubeName);
            MeasureGroup mg = cube.MeasureGroups.GetByName(measureGroupName);
            props = "<DATABASE_NAME>" + Context.CurrentDatabaseName + "</DATABASE_NAME>";
            props += "<CUBE_NAME>" + cubeName + "</CUBE_NAME><MEASURE_GROUP_NAME>" + measureGroupName + "</MEASURE_GROUP_NAME>";

            //get info for the first partition in the measure group
            dt = xd.Discover("DISCOVER_PARTITION_DIMENSION_STAT", props + "<PARTITION_NAME>" + mg.Partitions[0].Name + "</PARTITION_NAME>");
            dt.Columns.Add("Overlap", System.Type.GetType("System.String"));
            dt.AcceptChanges();

            dimCount = dt.Rows.Count;

            //get info for other partitions, if they exist
            if (mg.Partitions.Count > 1)
            {
                for (i = 1; i < mg.Partitions.Count; i++)
                {
                    Context.CheckCancelled();

                    dtTemp = xd.Discover("DISCOVER_PARTITION_DIMENSION_STAT", props + "<PARTITION_NAME>" + mg.Partitions[i].Name + "</PARTITION_NAME>");
                    dtTemp.Columns.Add("Overlap", System.Type.GetType("System.String"));
                    dtTemp.AcceptChanges();

                    dt.Merge(dtTemp);
                }
                //work out if partitions overlap
                foreach(DataRow currentRow in dt.Rows)
                {
                    Context.CheckCancelled();

                    if (currentRow["ATTRIBUTE_INDEXED"].ToString() == "true")
                    {
                        overlapText = "";
                        sameDimRows = dt.Select("DIMENSION_NAME='" + currentRow["DIMENSION_NAME"] + "' AND ATTRIBUTE_NAME='" + currentRow["ATTRIBUTE_NAME"] + "' AND PARTITION_NAME<>'" + currentRow["PARTITION_NAME"] + "' AND ATTRIBUTE_INDEXED='true'");
                        notFirstDim = false;
                        foreach (DataRow dr in sameDimRows)
                        {
                            Context.CheckCancelled();

                            if (
                                (
                                (Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()))
                                &&
                                (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString()))
                                )
                                ||
                                (
                                (Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()))
                                &&
                                (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString()))
                                )
                                ||
                                (
                                (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString())) && (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()) >= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString()))
                                )
                               )
                            {
                                if (notFirstDim)
                                    overlapText+=", ";
                                overlapText+=dr["PARTITION_NAME"];
                                notFirstDim = true;
                            }

                        }

                        currentRow["Overlap"] = overlapText;

                    }
                    dt.AcceptChanges();
                }

            }

            server.Disconnect();

            return dt;
        }
        public static void DeployScript(ProjectItem projItem, DTE2 ApplicationObject)
        {
            Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
            try
            {
                //validate the script because deploying an invalid script makes cube unusable
                Microsoft.AnalysisServices.Design.Scripts script = new Microsoft.AnalysisServices.Design.Scripts(oCube);
            }
            catch (Microsoft.AnalysisServices.Design.ScriptParsingFailed ex)
            {
                string throwaway = ex.Message;
                MessageBox.Show("MDX Script in " + oCube.Name + " is not valid.", "Problem Deploying MDX Script");
                return;
            }

            if (oCube.MdxScripts.Count == 0)
            {
                MessageBox.Show("There is no MDX script defined in this cube yet.");
                return;
            }

            try
            {
                ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 1, 5);


                // Check if the file is read-only (and probably checked in to a source control system)
                // before attempting to save. (issue: 10327 )
                FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1));
                if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly)
                {
                    //TODO - can I check and maybe prompt before saving?
                    //Save the cube
                    projItem.Save("");
                }

                ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 2, 5);

                // extract deployment information
                DeploymentSettings deploySet = new DeploymentSettings(projItem);

                // use xlst to create xmla alter command
                XslCompiledTransform xslt = new XslCompiledTransform();
                XmlReader            xsltRdr;
                XmlReader            xrdr;

                // read xslt from embedded resource
                xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployMdxScript));
                using ((xsltRdr))
                {
                    // read content from .cube file
                    xrdr = XmlReader.Create(projItem.get_FileNames(1));
                    using (xrdr)
                    {
                        ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 3, 5);
                        // Connect to Analysis Services
                        Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
                        svr.Connect(deploySet.TargetServer);
                        ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 4, 5);
                        // execute the xmla
                        try
                        {
                            Microsoft.AnalysisServices.Scripter scr = new Microsoft.AnalysisServices.Scripter();

                            // Build up the Alter MdxScript command using XSLT against the .cube file
                            XslCompiledTransform xslta = new XslCompiledTransform();
                            StringBuilder        sb    = new StringBuilder();
                            XmlWriterSettings    xws   = new XmlWriterSettings();
                            xws.OmitXmlDeclaration = true;
                            xws.ConformanceLevel   = ConformanceLevel.Fragment;
                            XmlWriter xwrtr = XmlWriter.Create(sb, xws);

                            xslta.Load(xsltRdr);
                            XsltArgumentList xslarg = new XsltArgumentList();

                            Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
                            if (targetDB == null)
                            {
                                throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
                            }
                            string targetDatabaseID = targetDB.ID;
                            xslarg.AddParam("TargetDatabase", "", targetDatabaseID);
                            xslta.Transform(xrdr, xslarg, xwrtr);

                            // Extract the current script from the server and keep a temporary backup copy of it
                            StringBuilder     sbBackup = new StringBuilder();
                            XmlWriterSettings xwSet    = new XmlWriterSettings();
                            xwSet.ConformanceLevel   = ConformanceLevel.Fragment;
                            xwSet.OmitXmlDeclaration = true;
                            xwSet.Indent             = true;
                            XmlWriter xwScript = XmlWriter.Create(sbBackup, xwSet);

                            Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
                            if (oServerCube == null)
                            {
                                throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
                            }
                            else if (oServerCube.State == AnalysisState.Unprocessed)
                            {
                                throw new System.Exception(string.Format("The {0} cube is not processed the {1} server.", oCube.Name, deploySet.TargetServer));
                            }
                            if (oServerCube.MdxScripts.Count == 0)
                            {
                                scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { oServerCube }, xwScript, true);
                            }
                            else
                            {
                                MdxScript mdxScr = oServerCube.MdxScripts[0];
                                scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { mdxScr }, xwScript, true);
                            }
                            xwScript.Close();

                            // update the MDX Script
                            XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
                            if (xmlaRC.Count == 1 && xmlaRC[0].Messages.Count == 0)
                            {
                                // all OK - 1 result - no messages
                            }
                            else
                            {
                                StringBuilder sbErr = new StringBuilder();
                                for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
                                {
                                    for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
                                    {
                                        sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
                                    }
                                }
                                MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy MDX Script");
                            }


                            // Test the MDX Script
                            AdomdConnection cn = new AdomdConnection("Data Source=" + deploySet.TargetServer + ";Initial Catalog=" + deploySet.TargetDatabase);
                            cn.Open();
                            AdomdCommand cmd = cn.CreateCommand();
                            string       qry = "SELECT {} ON 0 FROM [" + oCube.Name + "];";
                            cmd.CommandText = qry;
                            try
                            {
                                // test that we can query the cube without errors
                                cmd.Execute();

                                // Building the project means that the .asdatabase file gets re-built so that
                                // we do not break the Deployment Wizard.
                                // --
                                // This line is included in this try block so that it is only executed if we can
                                // successfully query the cube without errors.
                                projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
                            }
                            catch (System.Exception ex)
                            {
                                // undo the deployment if we caught an exception during the deployment
                                svr.Execute(sbBackup.ToString());
                                MessageBox.Show(ex.Message);
                            }
                            finally
                            {
                                cmd.Dispose();
                                cn.Close();
                                cn.Dispose();
                            }
                        }
                        catch (System.Exception ex)
                        {
                            if (MessageBox.Show("The following error occured while trying to deploy the MDX Script\r\n"
                                                + ex.Message
                                                + "\r\n\r\nDo you want to see a stack trace?"
                                                , "BIDSHelper - Deploy MDX Script"
                                                , MessageBoxButtons.YesNo
                                                , MessageBoxIcon.Error
                                                , MessageBoxDefaultButton.Button2) == DialogResult.Yes)
                            {
                                MessageBox.Show(ex.StackTrace);
                            }
                        }
                        finally
                        {
                            ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 5, 5);
                            // report any results back (status bar?)
                            svr.Disconnect();
                            svr.Dispose();
                        }
                    }
                }
            }
            finally
            {
                ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(false, "Deploying MdxScript", 5, 5);
            }
        }
Ejemplo n.º 20
0
        public static void DeployPerspectives(ProjectItem projItem, DTE2 ApplicationObject)
        {
            Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;

            if (oCube.Perspectives.Count == 0)
            {
                MessageBox.Show("There are no perspectives defined in this cube yet.");
                return;
            }

            try
            {
                ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 1, 5);

                FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1));
                if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly)
                {
                    //Save the cube
                    projItem.Save("");
                }

                ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 2, 5);

                // extract deployment information
                DeploymentSettings deploySet = new DeploymentSettings(projItem);

                // use xlst to create xmla alter command
                XslCompiledTransform xslt = new XslCompiledTransform();
                XmlReader            xsltRdr;
                XmlReader            xrdr;

                // read xslt from embedded resource
                xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployPerspectives));
                using ((xsltRdr))
                {
                    // read content from .cube file
                    xrdr = XmlReader.Create(projItem.get_FileNames(1));
                    using (xrdr)
                    {
                        ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 3, 5);
                        // Connect to Analysis Services
                        Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
                        svr.Connect(deploySet.TargetServer);
                        ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 4, 5);
                        // execute the xmla
                        try
                        {
                            // Build up the Alter perspectives command using XSLT against the .cube file
                            XslCompiledTransform xslta = new XslCompiledTransform();
                            StringBuilder        sb    = new StringBuilder();
                            XmlWriterSettings    xws   = new XmlWriterSettings();
                            xws.OmitXmlDeclaration = true;
                            xws.ConformanceLevel   = ConformanceLevel.Fragment;
                            XmlWriter xwrtr = XmlWriter.Create(sb, xws);

                            xslta.Load(xsltRdr);
                            XsltArgumentList xslarg = new XsltArgumentList();

                            Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
                            if (targetDB == null)
                            {
                                throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
                            }
                            string targetDatabaseID = targetDB.ID;
                            xslarg.AddParam("TargetDatabase", "", targetDatabaseID);
                            xslta.Transform(xrdr, xslarg, xwrtr);

                            Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
                            if (oServerCube == null)
                            {
                                throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
                            }

                            //drop any perspectives which don't exist
                            svr.CaptureXml = true;
                            for (int i = 0; i < oServerCube.Perspectives.Count; i++)
                            {
                                Perspective p = oServerCube.Perspectives[i];
                                if (!oCube.Perspectives.Contains(p.ID))
                                {
                                    p.Drop();
                                    i--;
                                }
                            }
                            svr.CaptureXml = false;
                            try
                            {
                                if (svr.CaptureLog.Count > 0)
                                {
                                    svr.ExecuteCaptureLog(true, false);
                                }
                            }
                            catch (System.Exception ex)
                            {
                                throw new System.Exception("Error dropping perspective that were deleted in the source code. " + ex.Message);
                            }


                            // update the perspectives
                            XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());

                            StringBuilder sbErr = new StringBuilder();
                            for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
                            {
                                for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
                                {
                                    sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
                                }
                            }
                            if (sbErr.Length > 0)
                            {
                                MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Perspectives");
                            }



                            try
                            {
                                // Building the project means that the .asdatabase file gets re-built so that
                                // we do not break the Deployment Wizard.
                                projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
                            }
                            catch (System.Exception ex)
                            {
                                MessageBox.Show(ex.Message);
                            }
                        }
                        catch (System.Exception ex)
                        {
                            if (MessageBox.Show("The following error occured while trying to deploy the perspectives\r\n"
                                                + ex.Message
                                                + "\r\n\r\nDo you want to see a stack trace?"
                                                , "BIDS Helper - Deploy Perspectives"
                                                , MessageBoxButtons.YesNo
                                                , MessageBoxIcon.Error
                                                , MessageBoxDefaultButton.Button2) == DialogResult.Yes)
                            {
                                MessageBox.Show(ex.StackTrace);
                            }
                        }
                        finally
                        {
                            ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 5, 5);
                            // report any results back (status bar?)
                            svr.Disconnect();
                        }
                    }
                }
            }
            finally
            {
                ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(false, "Deploying perspectives", 5, 5);
            }
        }
Ejemplo n.º 21
0
        public ActionResponse Execute()
        {
            var ids = new SSASIdentifiers(_input, _output);

            using (_server) {
                Database database;
                _server.Connect($"Data Source={_output.Connection.Server};Catalog=;");

                if (_server.Databases.Contains(ids.DatabaseId))
                {
                    database = _server.Databases.Find(ids.DatabaseId);
                    _output.Info($"Updating existing OLAP database: {ids.DatabaseId}");
                }
                else
                {
                    database = new Database()
                    {
                        Name = ids.DatabaseId,
                        ID   = ids.DatabaseId,
                        DataSourceImpersonationInfo = new ImpersonationInfo {
                            ImpersonationMode = ImpersonationMode.Default
                        }
                    };
                    _server.Databases.Add(database);
                    _output.Info($"Creating new OLAP database: {ids.DatabaseId}");
                    if (!SSAS.Save(_server, _output, database))
                    {
                        return(new ActionResponse(500, $"Could not save OLAP database: {ids.DatabaseId}"));
                    }
                }

                DataSource dataSource;
                if (database.DataSources.Contains(ids.DataSourceId))
                {
                    dataSource = database.DataSources.Find(ids.DataSourceId);
                    _output.Info($"Updating existing data source: {ids.DataSourceId}");
                }
                else
                {
                    dataSource = database.DataSources.AddNew(ids.DataSourceId);
                    _output.Info($"Creating new data source: {ids.DataSourceId}");
                }

                dataSource.ConnectionString  = $"Provider=SQLOLEDB.1;Data Source={_input.Connection.Server};Integrated Security=SSPI;Initial Catalog={_input.Connection.Database}";
                dataSource.ImpersonationInfo = new ImpersonationInfo {
                    ImpersonationMode = ImpersonationMode.ImpersonateServiceAccount
                };
                dataSource.Timeout = System.TimeSpan.FromSeconds(_input.Connection.RequestTimeout);

                if (!SSAS.Save(_server, _output, dataSource))
                {
                    return(new ActionResponse(500, $"Could not save data source: {ids.DataSourceId}"));
                }

                // view
                var            entity = _output.Entity;
                var            fields = SSAS.GetFields(_output);
                DataSourceView dataSourceView;
                if (database.DataSourceViews.Contains(ids.DataSourceViewId))
                {
                    dataSourceView = database.DataSourceViews.Find(ids.DataSourceViewId);
                    if (dataSourceView.Schema.Tables.Contains(entity.Alias))
                    {
                        dataSourceView.Schema.Tables.Remove(entity.Alias);
                    }
                    dataSourceView.Schema.Tables.Add(CreateDataTable(_output, fields));
                    _output.Info($"Updating existing data source view: {ids.DataSourceViewId}");
                }
                else
                {
                    _output.Info($"Creating new data source view: {ids.DataSourceViewId}");

                    dataSourceView = database.DataSourceViews.AddNew(ids.DataSourceViewId);
                    dataSourceView.DataSourceID = ids.DataSourceId;
                    dataSourceView.Schema       = new DataSet("Schema");
                    dataSourceView.Schema.Tables.Add(CreateDataTable(_output, fields));

                    if (!SSAS.Save(_server, _output, dataSourceView))
                    {
                        return(new ActionResponse(500, $"Could not save data source view: {ids.DataSourceViewId}"));
                    }
                }

                Dimension dimension;
                if (database.Dimensions.Contains(entity.Alias))
                {
                    dimension = database.Dimensions.Find(entity.Alias);
                    _output.Info($"Updating existing dimension: {entity.Alias}");
                }
                else
                {
                    dimension = database.Dimensions.AddNew(entity.Alias, entity.Alias);
                    dimension.WriteEnabled = false;
                    dimension.StorageMode  = DimensionStorageMode.Molap;
                    _output.Info($"Creating new dimension: {entity.Alias}");
                }
                dimension.Source = new DataSourceViewBinding(ids.DataSourceViewId);

                foreach (var field in fields)
                {
                    DimensionAttribute dimensionAttribute;
                    if (dimension.Attributes.Contains(field.Alias))
                    {
                        dimensionAttribute = dimension.Attributes.Find(field.Alias);
                    }
                    else
                    {
                        dimensionAttribute         = dimension.Attributes.Add(field.Label, field.Alias);
                        dimensionAttribute.OrderBy = OrderBy.Key; // for now
                    }

                    var optimize = field.PrimaryKey || field.Dimension == "true" || field.Dimension == "default" && !field.IsNumeric();
                    dimensionAttribute.AttributeHierarchyEnabled = optimize;
                    dimensionAttribute.IsAggregatable            = optimize;
                    dimensionAttribute.Usage = field.PrimaryKey ? AttributeUsage.Key : AttributeUsage.Regular;

                    dimensionAttribute.KeyColumns.Clear();
                    DataItem keyColumn;
                    if (field.Type == "string")
                    {
                        var length = field.Length == "max" ? int.MaxValue : Convert.ToInt32(field.Length);
                        keyColumn = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field), length)
                        {
                            Trimming = Trimming.None
                        };
                    }
                    else
                    {
                        keyColumn = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field));
                    }

                    dimensionAttribute.KeyColumns.Add(keyColumn);
                }

                if (!SSAS.Save(_server, _output, dimension))
                {
                    return(new ActionResponse(500, $"Could not save dimension: {entity.Alias}"));
                }

                Cube cube;
                if (database.Cubes.Contains(ids.CubeId))
                {
                    cube = database.Cubes.Find(ids.CubeId);
                    _output.Info($"Updating existing cube: {ids.CubeId}");
                }
                else
                {
                    cube = database.Cubes.AddNew(ids.CubeId, ids.CubeId);
                    _output.Info($"Creating new cube: {ids.CubeId}");
                }

                cube.Source = new DataSourceViewBinding(ids.DataSourceViewId);

                CubeDimension cubeDimension;
                if (cube.Dimensions.Contains(entity.Alias))
                {
                    cubeDimension = cube.Dimensions.Find(entity.Alias);
                }
                else
                {
                    cubeDimension = cube.Dimensions.Add(entity.Alias);
                }
                cubeDimension.Attributes.Clear();
                foreach (DimensionAttribute attribute in database.Dimensions.Find(entity.Alias).Attributes)
                {
                    cubeDimension.Attributes.Add(attribute.ID);
                }

                var normalMeasureGroup = GetMeasureGroup(cube, entity, ids.NormalMeasureGroupId, ids.DataSourceViewId);

                // standard count measure
                Measure countMeasure;
                if (normalMeasureGroup.Measures.Contains("Count"))
                {
                    countMeasure = normalMeasureGroup.Measures.Find("Count");
                }
                else
                {
                    countMeasure = normalMeasureGroup.Measures.Add("Count", "Count");
                }
                countMeasure.AggregateFunction = AggregationFunction.Count;
                countMeasure.Source            = new DataItem(new RowBinding(entity.Alias), OleDbType.Integer);

                // version measures
                var versionField = entity.GetVersionField();
                if (versionField != null)
                {
                    Measure versionMeasure;
                    if (normalMeasureGroup.Measures.Contains(ids.VersionId))
                    {
                        versionMeasure = normalMeasureGroup.Measures.Find(ids.VersionId);
                    }
                    else
                    {
                        versionMeasure = normalMeasureGroup.Measures.Add(ids.VersionId);
                    }
                    versionMeasure.AggregateFunction = AggregationFunction.Max;
                    versionMeasure.Source            = new DataItem(entity.Alias, versionField.Alias, SSAS.GetOleDbType(versionField));
                    versionMeasure.Visible           = false;
                }

                // defined measures
                var measureFields = entity.GetAllOutputFields().Where(f => f.Measure && f.Type != "byte[]");

                // normal measures
                foreach (var field in measureFields.Where(f => f.AggregateFunction != "distinctcount"))
                {
                    Measure measure;
                    var     function = (AggregationFunction)Enum.Parse(typeof(AggregationFunction), field.AggregateFunction, true);
                    if (normalMeasureGroup.Measures.Contains(field.Alias))
                    {
                        measure = normalMeasureGroup.Measures.Find(field.Alias);
                    }
                    else
                    {
                        measure = normalMeasureGroup.Measures.Add(field.Label, field.Alias);
                    }
                    measure.AggregateFunction = function;
                    measure.FormatString      = field.Format;
                    measure.Source            = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field));
                }

                // distinct measures
                foreach (var field in measureFields.Where(f => f.AggregateFunction == "distinctcount"))
                {
                    var type = SSAS.GetOleDbType(field);
                    if (SSAS.CanDistinctCount(type))
                    {
                        Measure measure;
                        var     distinctMeasureGroup = GetMeasureGroup(cube, entity, ids.DistinctMeasureGroupId, ids.DataSourceViewId);
                        if (distinctMeasureGroup.Measures.Contains(field.Alias))
                        {
                            measure = distinctMeasureGroup.Measures.Find(field.Alias);
                        }
                        else
                        {
                            measure = distinctMeasureGroup.Measures.Add(field.Label, field.Alias);
                        }
                        measure.AggregateFunction = AggregationFunction.DistinctCount;
                        measure.FormatString      = field.Format;
                        measure.Source            = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field));
                    }
                    else
                    {
                        _output.Warn($"Can not distinct count {field.Label} using {field.Type} type.");
                    }
                }

                if (!SSAS.Save(_server, _output, cube))
                {
                    return(new ActionResponse(500, $"Could not save cube: {ids.CubeId}"));
                }

                _server.Disconnect();
            }

            return(new ActionResponse());
        }
Ejemplo n.º 22
0
        static void CreateAllPartitions()
        {
            //https://msdn.microsoft.com/en-us/library/ms345091.aspx

            string srv_name =
                @"SCRBMSBDK000660\PREPRODQUERYSRV1";
            //@"SCRBSQLDEFRM637"; //this one is not enough for Intermodal, always fails during the processing
            string db_name = "Intermodal";
            //"Intermodal_TEST";
            string cube_name = "Intermodal Cube";

            //TextWriter tw = new StreamWriter("date.txt", true);

            Microsoft.AnalysisServices.Server       srv;
            Microsoft.AnalysisServices.Database     db;
            Microsoft.AnalysisServices.Cube         cube;
            Microsoft.AnalysisServices.MeasureGroup mg;
            Microsoft.AnalysisServices.Partition    part;
            srv = new Microsoft.AnalysisServices.Server();
            try
            {
                srv.Connect(srv_name);
                logMessageFmt("Connected to {0}", srv_name);
                db   = srv.Databases.FindByName(db_name);
                cube = db.Cubes.FindByName(cube_name);
                string query, slice;


                //-------- USD - Equipment Level
                mg = cube.MeasureGroups.FindByName("USD - Equipment Level");

                /*
                 *      select top 100 percent * from
                 *      (
                 *      ) x
                 *      order by Shipment_CODE_SSL, Equipment_Key
                 *
                 */
                query = @"
						select * from DTST12_SIT_DEV.IM_PnLFACTUSDAggrShpCnt
							where AccountMonth_CODE_SSL in (
								select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth
								where MonthYYYYMM_No = {0}
						)
				"                ;
                slice = "[Time Accounting period].[Unique Month].&[{0}]";
                logMessageFmt("Database: [{0}], cube: [{1}], measure group: [{2}].", db_name, cube_name, mg.Name);
                DropMeasureGroupPartitions(mg);
                CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2016, 1, 12, 120000000);
                CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2017, 1, 2, 120000000);

                //-------- FFE

                /*
                 *      select top 100 percent * from
                 *      (
                 *      ) x
                 *      order by Shipment_CODE_SSL, Equipment_CODE_SSL
                 *
                 */
                mg    = cube.MeasureGroups.FindByName("FFE");
                query = @"
						select * from DTST12_SIT_DEV.IM_PnL_FACT_FFEDisc_D
						where AccountMonth_CODE_SSL in (
							select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth
							where MonthYYYYMM_No = {0}
						)
						union all
						select * from DTST12_SIT_DEV.IM_PnL_FACT_FFEDisc_L
						where AccountMonth_CODE_SSL in (
							select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth
							where MonthYYYYMM_No = {0}
						)
				"                ;
                slice = "[Time Accounting period].[Unique Month].&[{0}]";
                logMessageFmt("Database: [{0}], cube: [{1}], measure group: [{2}].", db_name, cube_name, mg.Name);
                DropMeasureGroupPartitions(mg);
                CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2016, 1, 12, 2000000);
                CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2017, 1, 2, 2000000);

                //-------- Equipment Moves

                /*
                 *      select top 100 percent * from
                 *      (
                 *      ) x
                 *      order by Shipment_Key
                 *
                 */
                mg    = cube.MeasureGroups.FindByName("Equipment Moves");
                query = @"
						select * from DTST12_SIT_DEV.IM_tF_PnLContMovAggr
							where AccountingMonth_Key in (
								select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth
								where MonthYYYYMM_No = {0}
						)
				"                ;
                slice = "[Time Accounting period].[Unique Month].&[{0}]";
                logMessageFmt("Database: [{0}], cube: [{1}], measure group: [{2}].", db_name, cube_name, mg.Name);
                DropMeasureGroupPartitions(mg);
                CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2016, 1, 12, 10000000);
                CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2017, 1, 2, 10000000);

                //-------- USD - Shipment Level

                /*
                 * select top 100 percent* from
                 *      (
                 *      ) x
                 *      order by Shipment_CODE_SSL
                 *
                 */
                mg    = cube.MeasureGroups.FindByName("USD - Shipment Level");
                query = @"
						select * from DTST12_SIT_DEV.IM_tF_PnLFACTUSDAggr_Limited
							where AccountMonth_CODE_SSL in (
								select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth
								where MonthYYYYMM_No = {0}
						)
				"                ;
                slice = "[Time Accounting period].[Unique Month].&[{0}]";
                logMessageFmt("Database: [{0}], cube: [{1}], measure group: [{2}].", db_name, cube_name, mg.Name);
                DropMeasureGroupPartitions(mg);
                CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2016, 1, 12, 250000);
                CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2017, 1, 2, 250000);

                cube.Update();
            }
            finally
            {
                if (srv.Connected == true)
                {
                    srv.Disconnect();
                }
            }
        }
Ejemplo n.º 23
0
        static void PartitionInfo()
        {
            string srv_name  = @"SCRBMSBDK000660";
            string db_name   = "FBR_FYPnL_DPRD";
            string cube_name = "FYPnL Cube";
            string mg_name   = "USD";

            //TextWriter tw = new StreamWriter("date.txt", true);

            Microsoft.AnalysisServices.Server   srv;
            Microsoft.AnalysisServices.Database db;

            srv = new Microsoft.AnalysisServices.Server();
            try
            {
                srv.Connect(srv_name);
                logMessageFmt("Databases on [{0}]: {1}", srv_name, srv.Databases.Count);

                db = srv.Databases.FindByName(db_name);

                Cube cube = db.Cubes.FindByName(cube_name);

                CubeDimension      cubedim = cube.Dimensions[0];
                Dimension          dbdim   = cubedim.Dimension;
                DimensionAttribute dbattr  = dbdim.Attributes[0];

                //var Source = dbattr.Source;
                System.Data.DataSet ds = dbdim.DataSourceView.Schema;
                string    dsid         = db.DataSourceViews[0].DataSourceID;
                DataTable dt           = db.DataSourceViews[0].Schema.Tables[0];
                //db.DataSources[0].
                //DataTable dt = ds.Tables["SHARED_DimBrand"];
                //ep = dt.ExtendedProperties.

                MeasureGroup                 mg    = cube.MeasureGroups.FindByName(mg_name);
                MeasureGroupDimension        mgd   = mg.Dimensions[0];
                List <MeasureGroupAttribute> alist = new List <MeasureGroupAttribute>();

                if (mgd is RegularMeasureGroupDimension)
                {
                    RegularMeasureGroupDimension rmgd = (RegularMeasureGroupDimension)mgd;
                    foreach (MeasureGroupAttribute mgattr in rmgd.Attributes)
                    {
                        if (mgattr.Type == MeasureGroupAttributeType.Granularity)
                        {
                            alist.Add(mgattr);
                        }
                    }
                    //MeasureGroupAttribute mgattr = rmgd.Attributes.f["Key"];
                }
                Type t = alist[0].KeyColumns[0].Source.GetType();

                Measure msr = mg.Measures[0];

                foreach (Partition part in mg.Partitions)
                {
                    string         src;
                    TabularBinding tb = part.Source;
                    if (tb is QueryBinding)
                    {
                        src = String.Format("QUERY: {0}", ((QueryBinding)tb).QueryDefinition);
                    }
                    else if (tb is TableBinding)
                    {
                        src = String.Format("TABLE: {0}.{1}", ((TableBinding)tb).DbSchemaName, ((TableBinding)tb).DbTableName);
                    }
                    else if (tb is DsvTableBinding)
                    {
                        src = String.Format("DSV: {0}.{1}", ((DsvTableBinding)tb).DataSourceViewID, ((DsvTableBinding)tb).TableID);
                    }
                    else
                    {
                        src = String.Empty;
                    }

                    logMessageFmt("Partition [{0}]: {1}", part.Name, src /*part.EstimatedRows*/);
                    //part.Process()
                }
                //Partition part = mg.Partitions[0]; //.FindByName(part_name);

                logMessage("Done.");
                Console.ReadKey();
            }
            finally
            {
                if (srv.Connected == true)
                {
                    srv.Disconnect();
                }
            }
        }
Ejemplo n.º 24
0
        /// <summary>
        /// This method ensures the tabular model is online and populates the CompatibilityLevel property.
        /// </summary>
        /// <param name="closedBimFile">A Boolean specifying if the user cancelled the comparison. For the case where running in Visual Studio, the user has the option of cancelling if the project BIM file is open.</param>
        public void InitializeCompatibilityLevel(bool closedBimFile = false)
        {
            if (UseBimFile)
            {
                TOM.Database tomDatabase          = null;
                bool         exceptionLoadingFile = false;
                try
                {
                    tomDatabase = OpenDatabaseFromFile();
                }
                catch
                {
                    exceptionLoadingFile = true;
                }
                if (exceptionLoadingFile || tomDatabase == null)
                {
                    throw new ConnectionException($"Can't load file \"{_bimFile}\".");
                }

                _compatibilityLevel = tomDatabase.CompatibilityLevel;
                _dataSourceVersion  = tomDatabase.Model.DefaultPowerBIDataSourceVersion.ToString();
                _directQuery        = (tomDatabase.Model != null && tomDatabase.Model.DefaultMode == Microsoft.AnalysisServices.Tabular.ModeType.DirectQuery);

                return;
            }

            if (UseProject)
            {
                //Initialize _projectDirectoryInfo
                FileInfo projectFileInfo;
                if (_project == null)
                {
                    //Probably running in command-line mode
                    projectFileInfo = new FileInfo(_projectFile);
                }
                else
                {
                    projectFileInfo = new FileInfo(_project.FullName);
                }
                _projectDirectoryInfo = new DirectoryInfo(projectFileInfo.Directory.FullName);

                //Read settings file to get workspace server/db
                ReadSettingsFile();

                //Read project file to get deployment server/cube names, and bim file
                ReadProjectFile();

                //Overwrite the server if a workspace server provided
                if (_workspaceServerProvided)
                {
                    this.ServerName = _workspaceServer;
                }
            }

            Microsoft.AnalysisServices.Server amoServer = new Microsoft.AnalysisServices.Server();
            try
            {
                amoServer.Connect(BuildConnectionString());
            }
            catch (ConnectionException) when(UseProject)
            {
                //See if can find integrated workspace server

                bool foundServer = false;

                string tempDataDir = Path.GetTempPath() + @"Microsoft\Microsoft SQL Server\OLAP\LocalServer\Data";

                if (Directory.Exists(tempDataDir))
                {
                    var subDirs = Directory.GetDirectories(tempDataDir).OrderByDescending(d => new DirectoryInfo(d).CreationTime); //Need to order by descending in case old folders hanging around when VS was killed and SSDT didn't get a chance to clean up after itself
                    foreach (string subDir in subDirs)
                    {
                        string[] iniFilePath = Directory.GetFiles(subDir, "msmdsrv.ini");
                        if (iniFilePath.Length == 1 && File.ReadAllText(iniFilePath[0]).Contains("<DataDir>" + _projectDirectoryInfo.FullName + @"\bin\Data</DataDir>")) //Todo: proper xml lookup
                        {
                            //Assuming this must be the folder, so now get the port number
                            string[] portFilePath = Directory.GetFiles(subDir, "msmdsrv.port.txt");
                            if (portFilePath.Length == 1)
                            {
                                string port = File.ReadAllText(portFilePath[0]).Replace("\0", "");
                                this.ServerName = $"localhost:{Convert.ToString(port)}";
                                amoServer.Connect(BuildConnectionString());
                                foundServer = true;
                                break;
                            }
                        }
                    }
                }

                if (!foundServer)
                {
                    throw;
                }
            }

            ////non-admins can't see any ServerProperties: social.msdn.microsoft.com/Forums/sqlserver/en-US/3d0bf49c-9034-4416-9c51-77dc32bf8b73/determine-current-user-permissionscapabilities-via-amo-or-xmla
            //if (!(amoServer.ServerProperties.Count > 0)) //non-admins can't see any ServerProperties
            //{
            //    throw new Microsoft.AnalysisServices.ConnectionException($"Current user {WindowsIdentity.GetCurrent().Name} is not an administrator on the Analysis Server " + this.ServerName);
            //}

            if (amoServer.ServerMode != ServerMode.Tabular && amoServer.ServerMode != ServerMode.SharePoint) //SharePoint is what Power BI Desktop runs as
            {
                throw new ConnectionException($"Analysis Server {this.ServerName} is not running in Tabular mode");
            }

            Microsoft.AnalysisServices.Database amoDatabase = null;
            if (this.DatabaseName == "" && this.ServerName.ToUpper().StartsWith("localhost:".ToUpper()))
            {
                //PBI Desktop doesn't have db name yet
                if (amoServer.Databases.Count > 0)
                {
                    amoDatabase       = amoServer.Databases[0];
                    this.DatabaseName = amoDatabase.Name;
                }
            }
            else
            {
                amoDatabase = amoServer.Databases.FindByName(this.DatabaseName);
            }
            if (amoDatabase == null)
            {
                if (!this.UseProject)
                {
                    throw new ConnectionException("Could not connect to database " + this.DatabaseName);
                }
                else
                {
                    /* Check if folder exists using SystemGetSubdirs. If so attach. If not, do nothing - when execute BIM file below will create automatically.
                     * Using XMLA to run SystemGetSubdirs rather than ADOMD.net here don't want a reference to ADOMD.net Dll.
                     * Also, can't use Server.Execute method because it only takes XMLA execute commands (as opposed to XMLA discover commands), so need to submit the full soap envelope
                     */

                    string dataDir = amoServer.ServerProperties["DataDir"].Value;
                    if (dataDir.EndsWith("\\"))
                    {
                        dataDir = dataDir.Substring(0, dataDir.Length - 1);
                    }
                    string      commandStatement = String.Format("SystemGetSubdirs '{0}'", dataDir);
                    bool        foundFault       = false;
                    XmlNodeList rows             = Core.Comparison.ExecuteXmlaCommand(amoServer, "", commandStatement, ref foundFault);

                    string dbDir = "";
                    foreach (XmlNode row in rows)
                    {
                        XmlNode dirNode     = null;
                        XmlNode allowedNode = null;

                        foreach (XmlNode childNode in row.ChildNodes)
                        {
                            if (childNode.Name == "Dir")
                            {
                                dirNode = childNode;
                            }
                            else if (childNode.Name == "Allowed")
                            {
                                allowedNode = childNode;
                            }
                        }

                        if (dirNode != null && allowedNode != null && dirNode.InnerText.Length >= this.DatabaseName.Length && dirNode.InnerText.Substring(0, this.DatabaseName.Length) == this.DatabaseName && allowedNode.InnerText.Length > 0 && allowedNode.InnerText == "1")
                        {
                            dbDir = dataDir + "\\" + dirNode.InnerText;
                            break;
                        }
                    }

                    if (dbDir != "")
                    {
                        //attach
                        amoServer.Attach(dbDir);
                        amoServer.Refresh();
                        amoDatabase = amoServer.Databases.FindByName(this.DatabaseName);
                    }
                }
            }

            if (this.UseProject)
            {
                //_bimFileFullName = GetBimFileFullName();
                if (String.IsNullOrEmpty(_ssdtBimFile))
                {
                    throw new ConnectionException("Could not load BIM file for Project " + this.ProjectName);
                }

                if (!closedBimFile) //If just closed BIM file, no need to execute it
                {
                    //Execute BIM file contents as script on workspace database

                    //We don't know the compatibility level yet, so try parsing json, if fail, try xmla ...
                    try
                    {
                        //Replace "SemanticModel" with db name.
                        JObject jDocument = JObject.Parse(File.ReadAllText(_ssdtBimFile));

                        if (jDocument["name"] == null || jDocument["id"] == null)
                        {
                            throw new ConnectionException("Could not read JSON in BIM file " + _ssdtBimFile);
                        }

                        jDocument["name"] = DatabaseName;
                        jDocument["id"]   = DatabaseName;

                        //Todo: see if Tabular helper classes for this once documentation available after CTP
                        string command =
                            $@"{{
  ""createOrReplace"": {{
    ""object"": {{
      ""database"": ""{DatabaseName}""
    }},
    ""database"": {jDocument.ToString()}
    }}
}}
";
                        amoServer.Execute(command);
                    }
                    catch (JsonReaderException)
                    {
                        //Replace "SemanticModel" with db name.  Could do a global replace, but just in case it's not called SemanticModel, use dom instead
                        //string xmlaScript = File.ReadAllText(xmlaFileFullName);
                        XmlDocument document = new XmlDocument();
                        document.Load(_ssdtBimFile);
                        XmlNamespaceManager nsmgr = new XmlNamespaceManager(document.NameTable);
                        nsmgr.AddNamespace("myns1", "http://schemas.microsoft.com/analysisservices/2003/engine");

                        XmlNode objectDatabaseIdNode             = document.SelectSingleNode("//myns1:Object/myns1:DatabaseID", nsmgr);
                        XmlNode objectDefinitionDatabaseIdNode   = document.SelectSingleNode("//myns1:ObjectDefinition/myns1:Database/myns1:ID", nsmgr);
                        XmlNode objectDefinitionDatabaseNameNode = document.SelectSingleNode("//myns1:ObjectDefinition/myns1:Database/myns1:Name", nsmgr);

                        if (objectDatabaseIdNode == null || objectDefinitionDatabaseIdNode == null || objectDefinitionDatabaseNameNode == null)
                        {
                            throw new ConnectionException("Could not access XMLA in BIM file " + _ssdtBimFile);
                        }

                        objectDatabaseIdNode.InnerText             = DatabaseName;
                        objectDefinitionDatabaseIdNode.InnerText   = DatabaseName;
                        objectDefinitionDatabaseNameNode.InnerText = DatabaseName;

                        //1103, 1100 projects store the xmla as Alter (equivalent to createOrReplace), so just need to execute
                        amoServer.Execute(document.OuterXml);
                    }
                }

                //need next lines in case just created the db using the Execute method
                //amoServer.Refresh(); //todo workaround for bug 9719887 on 3/10/17 need to disconnect and reconnect
                amoServer.Disconnect();
                amoServer.Connect(BuildConnectionString());

                amoDatabase = amoServer.Databases.FindByName(this.DatabaseName);
            }

            if (amoDatabase == null)
            {
                throw new ConnectionException($"Can not load/find database {this.DatabaseName}.");
            }

            _compatibilityLevel = amoDatabase.CompatibilityLevel;
            if (_compatibilityLevel >= 1400)
            {
                _dataSourceVersion = amoDatabase.Model.DefaultPowerBIDataSourceVersion.ToString();
            }
            _serverMode  = amoServer.ServerMode;
            _directQuery = ((amoDatabase.Model != null && amoDatabase.Model.DefaultMode == Microsoft.AnalysisServices.Tabular.ModeType.DirectQuery) ||
                            amoDatabase.DirectQueryMode == DirectQueryMode.DirectQuery || amoDatabase.DirectQueryMode == DirectQueryMode.InMemoryWithDirectQuery || amoDatabase.DirectQueryMode == DirectQueryMode.DirectQueryWithInMemory);
        }
        private static DataTable GetClusterCharacteristics(string strModel, string strClusterUniqueID, double dThreshold)
        {
            //if we don't know the path to the system data mining sprocs assembly, get it
            if (_cachedSystemDataMiningSprocsPath.Length == 0)
            {
                Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
                svr.Connect(Context.CurrentServerID);
                ClrAssembly ass = (ClrAssembly)svr.Assemblies.GetByName("System");
                if (ass == null) throw new Exception("System (data mining sprocs) assembly not found");
                foreach (ClrAssemblyFile file in ass.Files)
                {
                    if (file.Type == ClrAssemblyFileType.Main)
                    {
                        lock (_cachedSystemDataMiningSprocsPath) _cachedSystemDataMiningSprocsPath = file.Name;
                        break;
                    }
                }
                svr.Disconnect();
            }

            //get the DataMining sprocs assembly and call the GetClusterCharacteristics function
            System.Reflection.Assembly asAss = System.Reflection.Assembly.LoadFile(_cachedSystemDataMiningSprocsPath);
            Type t = asAss.GetType("Microsoft.AnalysisServices.System.DataMining.Clustering");
            object oClustering = t.GetConstructor(new Type[] { }).Invoke(new object[] { });
            return (DataTable)t.InvokeMember("GetClusterCharacteristics", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.InvokeMethod, null, oClustering, new object[] { strModel, strClusterUniqueID, dThreshold });
        }
        public static void DeployAggDesigns(ProjectItem projItem, DTE2 ApplicationObject)
        {
            Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;

            bool bFoundAggDesign = false;

            foreach (MeasureGroup mg in oCube.MeasureGroups)
            {
                if (mg.AggregationDesigns.Count > 0)
                {
                    bFoundAggDesign = true;
                    break;
                }
            }
            if (!bFoundAggDesign)
            {
                MessageBox.Show("There are no aggregation designs defined in this cube yet.");
                return;
            }

            if (MessageBox.Show("This command deploys just the aggregation designs in this cube. It does not change which aggregation design is assigned to each partition.\r\n\r\nYou should run a ProcessIndex command from Management Studio on this cube after aggregation designs have been deployed.\r\n\r\nDo you wish to continue?", "BIDS Helper - Deploy Aggregation Designs", MessageBoxButtons.YesNo) != DialogResult.Yes)
            {
                return;
            }

            try
            {
                ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 1, 5);

                string sPartitionsFileName = projItem.get_FileNames(1);
                sPartitionsFileName = sPartitionsFileName.Substring(0, sPartitionsFileName.Length - 5) + ".partitions";

                // Check if the file is read-only (and probably checked in to a source control system)
                // before attempting to save. (issue: 10327 )
                FileAttributes fa = System.IO.File.GetAttributes(sPartitionsFileName);
                if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly)
                {
                    //TODO - prompt before saving?
                    //Save the cube
                    projItem.Save("");
                }

                ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 2, 5);

                // extract deployment information
                DeploymentSettings deploySet = new DeploymentSettings(projItem);

                // use xlst to create xmla alter command
                XslCompiledTransform xslt = new XslCompiledTransform();
                XmlReader            xsltRdr;
                XmlReader            xrdr;

                // read xslt from embedded resource
                xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployAggDesigns));
                using ((xsltRdr))
                {
                    // read content from .partitions file
                    xrdr = XmlReader.Create(sPartitionsFileName);
                    using (xrdr)
                    {
                        ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 3, 5);
                        // Connect to Analysis Services
                        Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
                        svr.Connect(deploySet.TargetServer);
                        ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 4, 5);
                        // execute the xmla
                        try
                        {
                            // Build up the Alter MdxScript command using XSLT against the .partitions file
                            XslCompiledTransform xslta = new XslCompiledTransform();
                            StringBuilder        sb    = new StringBuilder();
                            XmlWriterSettings    xws   = new XmlWriterSettings();
                            xws.OmitXmlDeclaration = true;
                            xws.ConformanceLevel   = ConformanceLevel.Fragment;
                            XmlWriter xwrtr = XmlWriter.Create(sb, xws);

                            xslta.Load(xsltRdr);
                            XsltArgumentList xslarg = new XsltArgumentList();

                            Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
                            if (targetDB == null)
                            {
                                throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
                            }
                            xslarg.AddParam("TargetDatabase", "", targetDB.ID);
                            xslarg.AddParam("TargetCubeID", "", oCube.ID);
                            xslta.Transform(xrdr, xslarg, xwrtr);

                            Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
                            if (oServerCube == null)
                            {
                                throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
                            }

                            // update the agg designs
                            XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
                            StringBuilder        sbErr  = new StringBuilder();
                            for (int iRC = 0; iRC < xmlaRC.Count; iRC++)
                            {
                                for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
                                {
                                    if (!string.IsNullOrEmpty(xmlaRC[iRC].Messages[iMsg].Description))
                                    {
                                        sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
                                    }
                                }
                            }
                            if (sbErr.Length > 0)
                            {
                                MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Aggregation Designs");
                            }

                            projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
                        }
                        catch (System.Exception ex)
                        {
                            if (MessageBox.Show("The following error occured while trying to deploy the aggregation designs\r\n"
                                                + ex.Message
                                                + "\r\n\r\nDo you want to see a stack trace?"
                                                , "BIDSHelper - Deploy Aggregation Designs"
                                                , MessageBoxButtons.YesNo
                                                , MessageBoxIcon.Error
                                                , MessageBoxDefaultButton.Button2) == DialogResult.Yes)
                            {
                                MessageBox.Show(ex.StackTrace);
                            }
                        }
                        finally
                        {
                            ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 5, 5);
                            svr.Disconnect();
                        }
                    }
                }
            }
            finally
            {
                ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(false, "Deploying Aggregation Designs", 5, 5);
            }
        }
Ejemplo n.º 27
0
        public DataTable DiscoverPartitionSlices(string cubeName, string measureGroupName)
        {
            DataTable    dt;
            DataTable    dtTemp;
            string       props;
            XmlaDiscover xd = new XmlaDiscover();
            int          dimCount;
            int          i;

            DataRow[] sameDimRows;

            string overlapText;
            bool   notFirstDim = false;


            Microsoft.AnalysisServices.Server server = new Microsoft.AnalysisServices.Server();
            server.Connect("*");
            Database     db   = server.Databases.GetByName(Context.CurrentDatabaseName);
            Cube         cube = db.Cubes.GetByName(cubeName);
            MeasureGroup mg   = cube.MeasureGroups.GetByName(measureGroupName);

            props  = "<DATABASE_NAME>" + Context.CurrentDatabaseName + "</DATABASE_NAME>";
            props += "<CUBE_NAME>" + cubeName + "</CUBE_NAME><MEASURE_GROUP_NAME>" + measureGroupName + "</MEASURE_GROUP_NAME>";

            //get info for the first partition in the measure group
            dt = xd.Discover("DISCOVER_PARTITION_DIMENSION_STAT", props + "<PARTITION_NAME>" + mg.Partitions[0].Name + "</PARTITION_NAME>");
            dt.Columns.Add("Overlap", System.Type.GetType("System.String"));
            dt.AcceptChanges();

            dimCount = dt.Rows.Count;

            //get info for other partitions, if they exist
            if (mg.Partitions.Count > 1)
            {
                for (i = 1; i < mg.Partitions.Count; i++)
                {
                    Context.CheckCancelled();

                    dtTemp = xd.Discover("DISCOVER_PARTITION_DIMENSION_STAT", props + "<PARTITION_NAME>" + mg.Partitions[i].Name + "</PARTITION_NAME>");
                    dtTemp.Columns.Add("Overlap", System.Type.GetType("System.String"));
                    dtTemp.AcceptChanges();

                    dt.Merge(dtTemp);
                }
                //work out if partitions overlap
                foreach (DataRow currentRow in dt.Rows)
                {
                    Context.CheckCancelled();

                    if (currentRow["ATTRIBUTE_INDEXED"].ToString() == "true")
                    {
                        overlapText = "";
                        sameDimRows = dt.Select("DIMENSION_NAME='" + currentRow["DIMENSION_NAME"] + "' AND ATTRIBUTE_NAME='" + currentRow["ATTRIBUTE_NAME"] + "' AND PARTITION_NAME<>'" + currentRow["PARTITION_NAME"] + "' AND ATTRIBUTE_INDEXED='true'");
                        notFirstDim = false;
                        foreach (DataRow dr in sameDimRows)
                        {
                            Context.CheckCancelled();

                            if (
                                (
                                    (Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()))
                                    &&
                                    (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString()))
                                )
                                ||
                                (
                                    (Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()))
                                    &&
                                    (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString()))
                                )
                                ||
                                (
                                    (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString())) && (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()) >= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString()))
                                )
                                )
                            {
                                if (notFirstDim)
                                {
                                    overlapText += ", ";
                                }
                                overlapText += dr["PARTITION_NAME"];
                                notFirstDim  = true;
                            }
                        }

                        currentRow["Overlap"] = overlapText;
                    }
                    dt.AcceptChanges();
                }
            }

            server.Disconnect();



            return(dt);
        }