Пример #1
0
        public static DateTime GetCubeLastSchemaUpdateDate()
        {
            DateTime dtTemp = DateTime.MinValue;
            Exception exDelegate = null;

            string sServerName = Context.CurrentServerID;
            string sDatabaseName = Context.CurrentDatabaseName;
            string sCubeName = GetCurrentCubeName();

            System.Threading.Thread td = new System.Threading.Thread(delegate()
            {
                try {
                    Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
                    oServer.Connect("Data Source=" + sServerName);
                    Database db = oServer.Databases.GetByName(sDatabaseName);
                    Cube cube = db.Cubes.FindByName(sCubeName);

                    dtTemp = cube.LastSchemaUpdate;
                }
                catch (Exception ex)
                {
                    exDelegate = ex;
                }
            }
            );
            td.Start();
            while (!td.Join(1000))
            {
                Context.CheckCancelled();
            }

            if (exDelegate != null) throw exDelegate;

            return dtTemp;
        }
Пример #2
0
        public static Tuple <Version, string, string> GetServerInfo()
        {
            Version version;
            string  edition;
            string  config;

            using (var server = new Microsoft.AnalysisServices.Server())
            {
                server.Connect("*");
                version = Version.Parse(server.Version);
                edition = server.Edition.ToString();
            }

            Func <string> read = () =>
            {
                var path = Environment.GetCommandLineArgs()
                           .SkipWhile((a) => !"-s".Equals(a, StringComparison.OrdinalIgnoreCase))
                           .Skip(1)
                           .Take(1)
                           .FirstOrDefault();

                if (path == null)
                {
                    throw new FileNotFoundException("Unable to retrieve path from CommandLine", "msmdsrv.ini");
                }

                return(File.ReadAllText(Path.Combine(path, "msmdsrv.ini")));
            };

            using (var task = Task.Factory.StartNew(read))
                config = task.Result;

            return(Tuple.Create(version, edition, config));
        }
Пример #3
0
        public static bool Save(Microsoft.AnalysisServices.Server server, OutputContext output, IMajorObject obj)
        {
            var builder = new StringBuilder();

            using (var xmlWriter = XmlWriter.Create(builder, new XmlWriterSettings {
                OmitXmlDeclaration = true
            })) {
                Scripter.WriteAlter(xmlWriter, obj, true, true);
                xmlWriter.Flush();
            }

            var command = builder.ToString();

            output.Debug(() => command);
            var results = server.Execute(command);

            if (results.Count > 0)
            {
                foreach (XmlaResult result in results)
                {
                    if (result.Messages.Count > 0)
                    {
                        foreach (XmlaMessage message in result.Messages)
                        {
                            output.Error(message.Description);
                        }
                        return(false);
                    }
                }
            }
            return(true);
        }
Пример #4
0
 public SSASInitializer(InputContext input, OutputContext output, IConnectionFactory connectionFactory)
 {
     _input             = input;
     _output            = output;
     _server            = new Microsoft.AnalysisServices.Server();
     _connectionFactory = connectionFactory;
 }
Пример #5
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 }));
        }
Пример #6
0
        private void ExecInternal(ProjectItem projItem, DataModelingSandbox sandbox)
        {
#if DENALI || SQL2014
            var db = sandbox.Database;
#else
            var db = ((DataModelingSandboxAmo)sandbox.Impl).Database;
#endif
            // extract deployment information
            DeploymentSettings deploySet = new DeploymentSettings(projItem);

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

                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));
                }
                StringBuilder     sb  = new StringBuilder();
                XmlWriterSettings xws = new XmlWriterSettings();
                xws.OmitXmlDeclaration = true;
                xws.ConformanceLevel   = ConformanceLevel.Fragment;
                XmlWriter xwrtr = XmlWriter.Create(sb, xws);
                // TODO - do we need different code for JSON based models??
                scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { db }, xwrtr, true);

                // 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 Tabular Database");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "BIDSHelper - Deploy Tabular Database - Exception");
                package.Log.Exception("Deploy Tabular Database Failed", ex);
            }
        }
Пример #7
0
 public void Open()
 {
     if (!IsConnectionOpen)
     {
         Server = new AS.Server();
         Server.Connect(ConnectionString.GetConnectionWithoutCatalog().Value);
     }
 }
        public DataTable ForEachMeasureGroupInternal(string command, bool forEachPartition)
        {
            DataTable result = new DataTable();

            Microsoft.AnalysisServices.Server server = new Microsoft.AnalysisServices.Server();
            server.Connect("*");
            Database db = server.Databases.GetByName(Context.CurrentDatabaseName);

            foreach (Microsoft.AnalysisServices.Cube c in db.Cubes)
            {
                Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + server.Name + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Cube=" + c.Name);

                foreach (Microsoft.AnalysisServices.MeasureGroup mg in c.MeasureGroups)
                {
                    if (forEachPartition)
                    {
                        foreach (Microsoft.AnalysisServices.Partition p in mg.Partitions)
                        {
                            //parameters don't appear to work with some DMV queries, so use string substitution
                            string sNewCommand = command;
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_NAME", db.Name);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_ID", db.ID);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_NAME", c.Name);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_ID", c.ID);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_NAME", mg.Name);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_ID", mg.ID);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "PARTITION_NAME", p.Name);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "PARTITION_ID", p.ID);
                            Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(sNewCommand, conn);
                            cmd.CommandTimeout = 0;
                            Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter adp = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(cmd);
                            TimeoutUtility.FillAdomdDataAdapter(adp, result);
                        }
                    }
                    else
                    {
                        //parameters don't appear to work with some DMV queries, so use string substitution
                        string sNewCommand = command;
                        sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_NAME", db.Name);
                        sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_ID", db.ID);
                        sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_NAME", c.Name);
                        sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_ID", c.ID);
                        sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_NAME", mg.Name);
                        sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_ID", mg.ID);
                        Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(sNewCommand, conn);
                        cmd.CommandTimeout = 0;
                        Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter adp = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(cmd);
                        TimeoutUtility.FillAdomdDataAdapter(adp, result);
                    }
                }

                conn.Close();
            }
            return(result);
        }
Пример #9
0
        private static Microsoft.AnalysisServices.Database GetAmoDatabase(ADOTabularConnection cnn)
        {
            var serverName = cnn.ServerName;

            var server = new Microsoft.AnalysisServices.Server();

            server.Connect(serverName);
            var db = server.Databases.GetByName(cnn.Database.Name);

            return(db);
        }
Пример #10
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);
            }
        }
Пример #11
0
        /// <summary>
        /// Create SSAS Cube Server Connection
        /// </summary>
        /// <param name="cubeServerName"></param>
        /// <returns></returns>
        public static Microsoft.AnalysisServices.Server CREATE_OLAP_CONNECTION(String cubeServerName, String cubeName = null)
        {
            String ConnecteString = String.Format("Data Source ={0}; Provider=msolap", cubeServerName);

            if (cubeName != null)
            {
                ConnecteString = String.Format("Data Source ={0};;Initial Catalog={1}; Connect Timeout=600;Provider=msolap", cubeServerName, cubeName);
            }
            Microsoft.AnalysisServices.Server asServer = new Microsoft.AnalysisServices.Server();
            asServer.Connect(ConnecteString);
            return(asServer);
        }
Пример #12
0
        private void cmdConnect_Click(object sender, EventArgs e)
        {
            try
            {
                cboDatabaseName.Items.Clear();
                cboDatabaseName.Text = "";
                cboCubeName.Items.Clear();
                cboCubeName.Text = "";

                String ConnStr;
                OLAPServerName = txtServerName.Text;
                txtProgress.AppendText("");

                if (checkBoxCurrentCreds.Checked == true)
                {
                    ConnStr = "Provider=MSOLAP;Data Source=" + OLAPServerName + ";";
                }
                else
                {
                    // Provider = MSOLAP.8; Persist Security Info = True; User ID = sg3\msorn; Initial Catalog = SSASTOM; Data Source = sg3\sql2017; MDX Compatibility = 1; Safety Options = 2; MDX Missing Member Mode = Error; Update Isolation Level = 2
                    ConnStr = "Provider=MSOLAP;Data Source=" + OLAPServerName + ";User Id = " + textBoxUserName.Text + "; Password = "******";";
                }
                //Initial Catalog=Adventure Works DW 2008R2;";
                OLAPServer = new AMO.Server();

                TOMServer = new TOM.Server();



                OLAPServer.Connect(ConnStr);
                TOMServer.Connect(ConnStr);

                Console.WriteLine("ServerName : " + OLAPServerName);

                cboDatabaseName.Items.Clear();
                foreach (AMO.Database OLAPDatabase in OLAPServer.Databases)
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text  = OLAPDatabase.Name;
                    item.Value = OLAPDatabase.ID;

                    cboDatabaseName.Items.Add(item);
                }
            }
            catch (Exception err)
            {
                string errormsg = err.InnerException.ToString();
                txtProgress.AppendText("--------------------------------------------------------------------------------------" + Environment.NewLine);
                txtProgress.AppendText("Error Occured" + Environment.NewLine);
                txtProgress.AppendText(err.InnerException.ToString() + Environment.NewLine);
                MessageBox.Show(errormsg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Пример #13
0
 private void DisConnectSSASServer()
 {
     if (ssasServer != null)
     {
         if (ssasServer.GetConnectionState(true) == System.Data.ConnectionState.Open)
         {
             ssasServer.Disconnect(true);
         }
         ssasServer.Dispose();
     }
     ssasServer = null;
 }
Пример #14
0
 public DataTable ListFunctions()
 {
     Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
     svr.Connect(Context.CurrentServerID);
     try
     {
         return(getFunctionList(svr.Assemblies));
     }
     finally
     {
         svr.Disconnect();
     }
 }
Пример #15
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();
            }
        }
        /// <summary>
        /// Performs the action of this task.
        /// </summary>
        protected override void InternalExecute()
        {
            this.server = new AMO.Server();
            StringBuilder con = new StringBuilder(string.Format(CultureInfo.CurrentCulture, "Data Source={0};", this.MachineName));

            if (string.IsNullOrEmpty(this.UserName))
            {
                this.LogTaskMessage(MessageImportance.Low, "Using a Trusted Connection");
                con.Append("Integrated Security=SSPI;");
            }
            else
            {
                con.AppendFormat("UserName={0}", this.UserName);
            }

            if (!string.IsNullOrEmpty(this.UserPassword))
            {
                con.AppendFormat("Password={0}", this.UserPassword);
            }

            this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Connect to server: {0}", this.MachineName));
            this.server.Connect(con.ToString());
            switch (this.TaskAction)
            {
            case ScriptCreateTaskAction:
                this.Script((scripter, objects, xmlWriter, dependantObjects) => scripter.ScriptCreate(objects, xmlWriter, dependantObjects));
                break;

            case ScriptAlterTaskAction:
                this.Script((scripter, objects, xmlWriter, dependantObjects) => scripter.ScriptAlter(objects, xmlWriter, dependantObjects));
                break;

            case ScriptDeleteTaskAction:
                this.Script((scripter, objects, xmlWriter, dependantObjects) => scripter.ScriptDelete(objects, xmlWriter, dependantObjects));
                break;

            case ProcessTaskAction:
                this.Process();
                break;

            case ExecuteTaskAction:
                this.ExecuteScript();
                break;

            default:
                this.Log.LogError(string.Format(CultureInfo.CurrentCulture, "Invalid TaskAction passed: {0}", this.TaskAction));
                return;
            }

            this.server.Disconnect();
        }
Пример #17
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();
                }
            }
        }
Пример #18
0
        public bool ConnectToAnalysisServer()
        {
            _CbServer         = new Server();
            _CbDatabase       = new Database();
            _CbDataSource     = new RelationalDataSource();
            _CbDataSourceView = new DataSourceView();
            _CbDataSet        = new DataSet();

            //Connecting to the Analysis Services.
            _CbServer = (Server)ConnectAnalysisServices(CbServerName, CbProviderName);
            if (_CbServer != null)
            {
                LoadFromDsDwMap();
            }
            return(_CbServer != null);
        }
Пример #19
0
        /*---------------------------------
         * Description: Update data for Analysis DB and reprocess it
         * ----------------------------------- */
        public static void UpdateTrainDB(Microsoft.AnalysisServices.Server svr, string sStockCode, bool bAll, DateTime dtFrom, DateTime dtTo, bool bMulti)
        {
            Database db = svr.Databases.GetByName("Stock");

            CreateDataAccessObjects(db, sStockCode, bAll, dtFrom, dtTo, bMulti, false);
            Microsoft.AnalysisServices.MiningModel mm = db.MiningStructures.GetByName(sStockCode).MiningModels.GetByName(sStockCode);
            // Max, Min Time Series
            mm.AlgorithmParameters.Remove("MAXIMUM_SERIES_VALUE");
            mm.AlgorithmParameters.Remove("MINIMUM_SERIES_VALUE");
            mm.AlgorithmParameters.Add("MAXIMUM_SERIES_VALUE", MAXIMUM_SERIES_VALUE);
            mm.AlgorithmParameters.Add("MINIMUM_SERIES_VALUE", MINIMUM_SERIES_VALUE);
            mm.Update();
            //db.MiningStructures.GetByName(sStockCode).Process(ProcessType.ProcessFull);
            //mm.Process(ProcessType.ProcessFull);
            db.Process(ProcessType.ProcessFull);
        }
        /// <summary>
        /// Performs the action of this task.
        /// </summary>
        protected override void InternalExecute()
        {
            this.server = new AMO.Server();
            StringBuilder con = new StringBuilder(string.Format(CultureInfo.CurrentCulture, "Data Source={0};", this.MachineName));
            if (string.IsNullOrEmpty(this.UserName))
            {
                this.LogTaskMessage(MessageImportance.Low, "Using a Trusted Connection");
                con.Append("Integrated Security=SSPI;");
            }
            else
            {
                con.AppendFormat("UserName={0}", this.UserName);
            }

            if (!string.IsNullOrEmpty(this.UserPassword))
            {
                con.AppendFormat("Password={0}", this.UserPassword);
            }

            this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "Connect to server: {0}", this.MachineName));
            this.server.Connect(con.ToString());
            switch (this.TaskAction)
            {
                case ScriptCreateTaskAction:
                    this.Script((scripter, objects, xmlWriter, dependantObjects) => scripter.ScriptCreate(objects, xmlWriter, dependantObjects));
                    break;
                case ScriptAlterTaskAction:
                    this.Script((scripter, objects, xmlWriter, dependantObjects) => scripter.ScriptAlter(objects, xmlWriter, dependantObjects));
                    break;
                case ScriptDeleteTaskAction:
                    this.Script((scripter, objects, xmlWriter, dependantObjects) => scripter.ScriptDelete(objects, xmlWriter, dependantObjects));
                    break;
                case ProcessTaskAction:
                    this.Process();
                    break;
                case ExecuteTaskAction:
                    this.ExecuteScript();
                    break;
                default:
                    this.Log.LogError(string.Format(CultureInfo.CurrentCulture, "Invalid TaskAction passed: {0}", this.TaskAction));
                    return;
            }

            this.server.Disconnect();
        }
Пример #21
0
        public static DateTime GetPartitionLastProcessedDate(string measureGroupName, string partitionName)
        {
            DateTime dtTemp = DateTime.MinValue;
            Exception exDelegate = null;

            if (string.IsNullOrEmpty(measureGroupName) || string.IsNullOrEmpty(partitionName))
                return dtTemp;

            string sServerName = Context.CurrentServerID;
            string sDatabaseName = Context.CurrentDatabaseName;
            string sCubeName = GetCurrentCubeName();
            string sMeasureGroupName = measureGroupName;
            string sPartitionName = partitionName;

            System.Threading.Thread td = new System.Threading.Thread(delegate()
            {
                try
                {
                    Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
                    oServer.Connect("Data Source=" + sServerName);
                    Database db = oServer.Databases.GetByName(sDatabaseName);
                    Cube cube = db.Cubes.FindByName(sCubeName);
                    MeasureGroup measuregroup = cube.MeasureGroups.FindByName(sMeasureGroupName);
                    Partition partition = measuregroup.Partitions.FindByName(sPartitionName);

                    dtTemp = partition.LastProcessed;
                }
                catch (Exception ex)
                {
                    exDelegate = ex;
                }
            }
            );
            td.Start();
            while (!td.Join(1000))
            {
                Context.CheckCancelled();
            }

            if (exDelegate != null) throw exDelegate;

            return dtTemp;
        }
Пример #22
0
        private static void OpenAMOServerConnection(object o)
        {
            ConnectAMOServerInfo info = null;

            try
            {
                info = (ConnectAMOServerInfo)o;
                Microsoft.AnalysisServices.Server s = new Microsoft.AnalysisServices.Server();
                s.Connect(info.ConnectionString);
                info.Server = s;
            }
            catch (Exception ex)
            {
                info.ex = ex;
            }
            finally
            {
                info.autoEvent.Set();
            }
        }
        // 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();
                }
            }
        }
Пример #24
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();
         }
     }
 }
Пример #25
0
        private bool ConnectSSASServer(string connectionString)
        {
            try
            {
                if (ssasServer == null)
                {
                    ssasServer = new AMO.Server();
                }

                if (ssasServer.GetConnectionState(true) != System.Data.ConnectionState.Open)
                {
                    ssasServer.Connect(connectionString);
                }

                return(true);
            }
            catch
            {
                return(false);
            }
        }
Пример #26
0
        public static void Main1()
        {
            serverAnalysis = new Microsoft.AnalysisServices.Server();

            try
            {
                serverAnalysis.Connect("Data Source = " + strServerName);

                olap = new Olap();

                analysis.Database db = serverAnalysis.Databases.FindByName(strDataBaseName);

                if (db != null)
                {
                    db.Drop();
                }
                else
                {

                    db = serverAnalysis.Databases.Add(strDataBaseName);

                    db.Update();

                    olap.CreateDataSource(db, strName, strConnectionString);

                    olap.CreateDataSourceView(db, strName, strName);

                    olap.CreateGeographyDimension(db, strName);

                    olap.CreateCustomerDimension(db, strName);

                    olap.CreateCube(db, strName);
                }
            }

            catch (analysis.AmoException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        // 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();
                }
            }
        }
Пример #28
0
        public static DateTime GetCubeLastProcessedDate()
        {
            string sServerName   = Context.CurrentServerID;
            string sDatabaseName = Context.CurrentDatabaseName;
            string sCubeName     = AMOHelpers.GetCurrentCubeName();

            DateTime  dtTemp     = DateTime.MinValue;
            Exception exDelegate = null;

            System.Threading.Thread td = new System.Threading.Thread(delegate()
            {
                try
                {
                    Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
                    oServer.Connect("Data Source=" + sServerName);
                    Database db = oServer.Databases.GetByName(sDatabaseName);
                    Cube cube   = db.Cubes.FindByName(sCubeName);

                    dtTemp = cube.LastProcessed;
                }
                catch (Exception ex)
                {
                    exDelegate = ex;
                }
            }
                                                                     );
            td.Start();                   //run the delegate code
            while (!td.Join(1000))        //wait for up to a second for the delegate to finish
            {
                Context.CheckCancelled(); //if the delegate isn't done, check whether the parent query has been cancelled. If the parent query has been cancelled (or the ForceCommitTimeout expires) then this will immediately exit
            }

            if (exDelegate != null)
            {
                throw exDelegate;
            }

            return(dtTemp);
            //return Context.CurrentCube.LastProcessed; //this doesn't work because of a bug: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606
        }
Пример #29
0
        public static void Main1()
        {
            serverAnalysis = new Microsoft.AnalysisServices.Server();

            try
            {
                serverAnalysis.Connect("Data Source = " + strServerName);

                olap = new Olap();

                analysis.Database db = serverAnalysis.Databases.FindByName(strDataBaseName);

                if (db != null)
                {
                    db.Drop();
                }
                else
                {
                    db = serverAnalysis.Databases.Add(strDataBaseName);

                    db.Update();

                    olap.CreateDataSource(db, strName, strConnectionString);

                    olap.CreateDataSourceView(db, strName, strName);

                    olap.CreateGeographyDimension(db, strName);

                    olap.CreateCustomerDimension(db, strName);

                    olap.CreateCube(db, strName);
                }
            }

            catch (analysis.AmoException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        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();
            }
        }
        public static DateTime GetCubeLastProcessedDate()
        {
            string sServerName = Context.CurrentServerID;
            string sDatabaseName = Context.CurrentDatabaseName;
            string sCubeName = AMOHelpers.GetCurrentCubeName();

            DateTime dtTemp = DateTime.MinValue;
            Exception exDelegate = null;

            System.Threading.Thread td = new System.Threading.Thread(delegate()
            {
                try
                {
                    Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
                    oServer.Connect("Data Source=" + sServerName);
                    Database db = oServer.Databases.GetByName(sDatabaseName);
                    Cube cube =  db.Cubes.FindByName(sCubeName);

                    dtTemp = cube.LastProcessed;
                }
                catch (Exception ex)
                {
                    exDelegate = ex;
                }
            }
            );
            td.Start(); //run the delegate code
            while (!td.Join(1000)) //wait for up to a second for the delegate to finish
            {
                Context.CheckCancelled(); //if the delegate isn't done, check whether the parent query has been cancelled. If the parent query has been cancelled (or the ForceCommitTimeout expires) then this will immediately exit
            }

            if (exDelegate != null) throw exDelegate;

            return dtTemp;
            //return Context.CurrentCube.LastProcessed; //this doesn't work because of a bug: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606
        }
Пример #32
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();
            }
        }
Пример #33
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();
                }
            }
        }
        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);
                            }
                            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);
            }
        }
        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 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;
        }
        private void ExecInternal(ProjectItem projItem, DataModelingSandbox sandbox)
        {
            //sandbox.
            // extract deployment information
                DeploymentSettings deploySet = new DeploymentSettings(projItem);

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

                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));
                }
                StringBuilder sb = new StringBuilder();
                XmlWriterSettings xws = new XmlWriterSettings();
                xws.OmitXmlDeclaration = true;
                xws.ConformanceLevel = ConformanceLevel.Fragment;
                XmlWriter xwrtr = XmlWriter.Create(sb, xws);
                scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] {sandbox.Database}, xwrtr, true);

                // 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 Tabular Database");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "BIDSHelper - Deploy Tabular Database - Exception");
            }
        }
Пример #39
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);
            }
        }
Пример #40
0
        public static DateTime GetLastProcessedDateOverPartitions(string CubeName, string MeasureGroupName, string PartitionName, bool IncludeMeasureGroupLastProcessed)
        {
            string sServerName   = Context.CurrentServerID;
            string sDatabaseName = Context.CurrentDatabaseName;

            string sCubeName;

            if (string.IsNullOrEmpty(CubeName))
            {
                sCubeName = AMOHelpers.GetCurrentCubeName();
            }
            else
            {
                sCubeName = CubeName;
            }

            if (!string.IsNullOrEmpty(PartitionName) && string.IsNullOrEmpty(MeasureGroupName))
            {
                throw new Exception("Measure group may not be empty if partition is explicitly specified");
            }

            DateTime  dtTemp     = DateTime.MinValue;
            Exception exDelegate = null;

            System.Threading.Thread td = new System.Threading.Thread(delegate()
            {
                try
                {
                    Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
                    oServer.Connect("Data Source=" + sServerName);
                    Database db = oServer.Databases.GetByName(sDatabaseName);
                    Cube cube   = db.Cubes.FindByName(sCubeName);

                    //If measure group is specified - get it. Otherwise iterate over all measure groups
                    if (!string.IsNullOrEmpty(MeasureGroupName))
                    {
                        dtTemp = FindMaxLastProcessedDateInMeasureGroup(cube.MeasureGroups.GetByName(MeasureGroupName), PartitionName, IncludeMeasureGroupLastProcessed);
                    }
                    else
                    {
                        foreach (MeasureGroup curMeasureGroup in cube.MeasureGroups)
                        {
                            DateTime curLastProcessedDate = FindMaxLastProcessedDateInMeasureGroup(curMeasureGroup, PartitionName, IncludeMeasureGroupLastProcessed);

                            if (dtTemp < curLastProcessedDate)
                            {
                                dtTemp = curLastProcessedDate;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    exDelegate = ex;
                }
            }
                                                                     );
            td.Start();                   //run the delegate code
            while (!td.Join(1000))        //wait for up to a second for the delegate to finish
            {
                Context.CheckCancelled(); //if the delegate isn't done, check whether the parent query has been cancelled. If the parent query has been cancelled (or the ForceCommitTimeout expires) then this will immediately exit
            }

            if (exDelegate != null)
            {
                throw exDelegate;
            }

            return(dtTemp);
        }
        public DataTable ForEachMeasureGroupInternal(string command, bool forEachPartition)
        {
            DataTable result = new DataTable();

            Microsoft.AnalysisServices.Server server = new Microsoft.AnalysisServices.Server();
            server.Connect("*");
            Database db = server.Databases.GetByName(Context.CurrentDatabaseName);

            foreach (Microsoft.AnalysisServices.Cube c in db.Cubes)
            {
                Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + server.Name + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Cube=" + c.Name);

                foreach (Microsoft.AnalysisServices.MeasureGroup mg in c.MeasureGroups)
                {
                    if (forEachPartition)
                    {
                        foreach (Microsoft.AnalysisServices.Partition p in mg.Partitions)
                        {
                            //parameters don't appear to work with some DMV queries, so use string substitution
                            string sNewCommand = command;
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_NAME", db.Name);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_ID", db.ID);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_NAME", c.Name);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_ID", c.ID);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_NAME", mg.Name);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_ID", mg.ID);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "PARTITION_NAME", p.Name);
                            sNewCommand = ReplaceParameterWithString(sNewCommand, "PARTITION_ID", p.ID);
                            Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(sNewCommand, conn);
                            cmd.CommandTimeout = 0;
                            Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter adp = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(cmd);
                            TimeoutUtility.FillAdomdDataAdapter(adp, result);
                        }
                    }
                    else
                    {
                        //parameters don't appear to work with some DMV queries, so use string substitution
                        string sNewCommand = command;
                        sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_NAME", db.Name);
                        sNewCommand = ReplaceParameterWithString(sNewCommand, "DATABASE_ID", db.ID);
                        sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_NAME", c.Name);
                        sNewCommand = ReplaceParameterWithString(sNewCommand, "CUBE_ID", c.ID);
                        sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_NAME", mg.Name);
                        sNewCommand = ReplaceParameterWithString(sNewCommand, "MEASUREGROUP_ID", mg.ID);
                        Microsoft.AnalysisServices.AdomdClient.AdomdCommand cmd = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(sNewCommand, conn);
                        cmd.CommandTimeout = 0;
                        Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter adp = new Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter(cmd);
                        TimeoutUtility.FillAdomdDataAdapter(adp, result);
                    }
                }

                conn.Close();
            }
            return result;
        }
        public static DateTime GetLastProcessedDateOverPartitions(string CubeName, string MeasureGroupName, string PartitionName, bool IncludeMeasureGroupLastProcessed)
        {
            string sServerName = Context.CurrentServerID;
            string sDatabaseName = Context.CurrentDatabaseName;

            string sCubeName;

            if (string.IsNullOrEmpty(CubeName))
            {
                sCubeName = AMOHelpers.GetCurrentCubeName();
            }
            else
            {
                sCubeName = CubeName;
            }

            if (!string.IsNullOrEmpty(PartitionName) && string.IsNullOrEmpty(MeasureGroupName))
            {
                throw new Exception("Measure group may not be empty if partition is explicitly specified");
            }

            DateTime dtTemp = DateTime.MinValue;
            Exception exDelegate = null;

            System.Threading.Thread td = new System.Threading.Thread(delegate()
            {
                try
                {
                    Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server();
                    oServer.Connect("Data Source=" + sServerName);
                    Database db = oServer.Databases.GetByName(sDatabaseName);
                    Cube cube = db.Cubes.FindByName(sCubeName);

                    //If measure group is specified - get it. Otherwise iterate over all measure groups
                    if (!string.IsNullOrEmpty(MeasureGroupName))
                    {
                        dtTemp = FindMaxLastProcessedDateInMeasureGroup(cube.MeasureGroups.GetByName(MeasureGroupName), PartitionName, IncludeMeasureGroupLastProcessed);
                    }
                    else
                    {
                        foreach (MeasureGroup curMeasureGroup in cube.MeasureGroups)
                        {
                            DateTime curLastProcessedDate = FindMaxLastProcessedDateInMeasureGroup(curMeasureGroup, PartitionName, IncludeMeasureGroupLastProcessed);

                            if (dtTemp < curLastProcessedDate)
                            {
                                dtTemp = curLastProcessedDate;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    exDelegate = ex;
                }
            }
            );
            td.Start(); //run the delegate code
            while (!td.Join(1000)) //wait for up to a second for the delegate to finish
            {
                Context.CheckCancelled(); //if the delegate isn't done, check whether the parent query has been cancelled. If the parent query has been cancelled (or the ForceCommitTimeout expires) then this will immediately exit
            }

            if (exDelegate != null) throw exDelegate;

            return dtTemp;
        }
        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);
            }
        }
Пример #44
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 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);
            }
        }