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)); }
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); } }
private static DataTable GetClusterCharacteristics(string strModel, string strClusterUniqueID, double dThreshold) { //if we don't know the path to the system data mining sprocs assembly, get it if (_cachedSystemDataMiningSprocsPath.Length == 0) { Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server(); svr.Connect(Context.CurrentServerID); ClrAssembly ass = (ClrAssembly)svr.Assemblies.GetByName("System"); if (ass == null) { throw new Exception("System (data mining sprocs) assembly not found"); } foreach (ClrAssemblyFile file in ass.Files) { if (file.Type == ClrAssemblyFileType.Main) { lock (_cachedSystemDataMiningSprocsPath) _cachedSystemDataMiningSprocsPath = file.Name; break; } } svr.Disconnect(); } //get the DataMining sprocs assembly and call the GetClusterCharacteristics function System.Reflection.Assembly asAss = System.Reflection.Assembly.LoadFile(_cachedSystemDataMiningSprocsPath); Type t = asAss.GetType("Microsoft.AnalysisServices.System.DataMining.Clustering"); object oClustering = t.GetConstructor(new Type[] { }).Invoke(new object[] { }); return((DataTable)t.InvokeMember("GetClusterCharacteristics", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.InvokeMethod, null, oClustering, new object[] { strModel, strClusterUniqueID, dThreshold })); }
public static 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; }
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); }
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); } }
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); }
/// <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); }
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); } }
public DataTable ListFunctions() { Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server(); svr.Connect(Context.CurrentServerID); try { return(getFunctionList(svr.Assemblies)); } finally { svr.Disconnect(); } }
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(); } }
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(); } } }
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; }
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(); } } }
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); } }
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(); } } }
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 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 }
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(); } }
private bool VerifyServer() { try { if (Options == null) { return(false); } if (!srv.Connected || srv.ConnectionInfo.Server != txtServer.Text.Trim()) { if (srv.Connected) { srv.Disconnect(); } srv.Connect("Integrated Security=SSPI;Persist Security Info=False;Data Source=" + txtServer.Text.Trim()); } return(true); } catch (Exception exc) { throw exc; } }
public static 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 }
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 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(); } }
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 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 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 }); }
static void CreateAllPartitions() { //https://msdn.microsoft.com/en-us/library/ms345091.aspx string srv_name = @"SCRBMSBDK000660\PREPRODQUERYSRV1"; //@"SCRBSQLDEFRM637"; //this one is not enough for Intermodal, always fails during the processing string db_name = "Intermodal"; //"Intermodal_TEST"; string cube_name = "Intermodal Cube"; //TextWriter tw = new StreamWriter("date.txt", true); Microsoft.AnalysisServices.Server srv; Microsoft.AnalysisServices.Database db; Microsoft.AnalysisServices.Cube cube; Microsoft.AnalysisServices.MeasureGroup mg; Microsoft.AnalysisServices.Partition part; srv = new Microsoft.AnalysisServices.Server(); try { srv.Connect(srv_name); logMessageFmt("Connected to {0}", srv_name); db = srv.Databases.FindByName(db_name); cube = db.Cubes.FindByName(cube_name); string query, slice; //-------- USD - Equipment Level mg = cube.MeasureGroups.FindByName("USD - Equipment Level"); /* * select top 100 percent * from * ( * ) x * order by Shipment_CODE_SSL, Equipment_Key * */ query = @" select * from DTST12_SIT_DEV.IM_PnLFACTUSDAggrShpCnt where AccountMonth_CODE_SSL in ( select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth where MonthYYYYMM_No = {0} ) " ; slice = "[Time Accounting period].[Unique Month].&[{0}]"; logMessageFmt("Database: [{0}], cube: [{1}], measure group: [{2}].", db_name, cube_name, mg.Name); DropMeasureGroupPartitions(mg); CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2016, 1, 12, 120000000); CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2017, 1, 2, 120000000); //-------- FFE /* * select top 100 percent * from * ( * ) x * order by Shipment_CODE_SSL, Equipment_CODE_SSL * */ mg = cube.MeasureGroups.FindByName("FFE"); query = @" select * from DTST12_SIT_DEV.IM_PnL_FACT_FFEDisc_D where AccountMonth_CODE_SSL in ( select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth where MonthYYYYMM_No = {0} ) union all select * from DTST12_SIT_DEV.IM_PnL_FACT_FFEDisc_L where AccountMonth_CODE_SSL in ( select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth where MonthYYYYMM_No = {0} ) " ; slice = "[Time Accounting period].[Unique Month].&[{0}]"; logMessageFmt("Database: [{0}], cube: [{1}], measure group: [{2}].", db_name, cube_name, mg.Name); DropMeasureGroupPartitions(mg); CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2016, 1, 12, 2000000); CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2017, 1, 2, 2000000); //-------- Equipment Moves /* * select top 100 percent * from * ( * ) x * order by Shipment_Key * */ mg = cube.MeasureGroups.FindByName("Equipment Moves"); query = @" select * from DTST12_SIT_DEV.IM_tF_PnLContMovAggr where AccountingMonth_Key in ( select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth where MonthYYYYMM_No = {0} ) " ; slice = "[Time Accounting period].[Unique Month].&[{0}]"; logMessageFmt("Database: [{0}], cube: [{1}], measure group: [{2}].", db_name, cube_name, mg.Name); DropMeasureGroupPartitions(mg); CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2016, 1, 12, 10000000); CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2017, 1, 2, 10000000); //-------- USD - Shipment Level /* * select top 100 percent* from * ( * ) x * order by Shipment_CODE_SSL * */ mg = cube.MeasureGroups.FindByName("USD - Shipment Level"); query = @" select * from DTST12_SIT_DEV.IM_tF_PnLFACTUSDAggr_Limited where AccountMonth_CODE_SSL in ( select Month_Key from DTST13_APPL_MSBIPNL.PnL_DimPeriodAccountingMonth where MonthYYYYMM_No = {0} ) " ; slice = "[Time Accounting period].[Unique Month].&[{0}]"; logMessageFmt("Database: [{0}], cube: [{1}], measure group: [{2}].", db_name, cube_name, mg.Name); DropMeasureGroupPartitions(mg); CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2016, 1, 12, 250000); CreateMeasureGroupPartitions(mg, query, slice, db.DataSources[0], 2017, 1, 2, 250000); cube.Update(); } finally { if (srv.Connected == true) { srv.Disconnect(); } } }
public DataTable DiscoverPartitionSlices(string cubeName, string measureGroupName) { DataTable dt; DataTable dtTemp; string props; XmlaDiscover xd = new XmlaDiscover(); int dimCount; int i; DataRow[] sameDimRows; string overlapText; bool notFirstDim = false; Microsoft.AnalysisServices.Server server = new Microsoft.AnalysisServices.Server(); server.Connect("*"); Database db = server.Databases.GetByName(Context.CurrentDatabaseName); Cube cube = db.Cubes.GetByName(cubeName); MeasureGroup mg = cube.MeasureGroups.GetByName(measureGroupName); props = "<DATABASE_NAME>" + Context.CurrentDatabaseName + "</DATABASE_NAME>"; props += "<CUBE_NAME>" + cubeName + "</CUBE_NAME><MEASURE_GROUP_NAME>" + measureGroupName + "</MEASURE_GROUP_NAME>"; //get info for the first partition in the measure group dt = xd.Discover("DISCOVER_PARTITION_DIMENSION_STAT", props + "<PARTITION_NAME>" + mg.Partitions[0].Name + "</PARTITION_NAME>"); dt.Columns.Add("Overlap", System.Type.GetType("System.String")); dt.AcceptChanges(); dimCount = dt.Rows.Count; //get info for other partitions, if they exist if (mg.Partitions.Count > 1) { for (i = 1; i < mg.Partitions.Count; i++) { Context.CheckCancelled(); dtTemp = xd.Discover("DISCOVER_PARTITION_DIMENSION_STAT", props + "<PARTITION_NAME>" + mg.Partitions[i].Name + "</PARTITION_NAME>"); dtTemp.Columns.Add("Overlap", System.Type.GetType("System.String")); dtTemp.AcceptChanges(); dt.Merge(dtTemp); } //work out if partitions overlap foreach(DataRow currentRow in dt.Rows) { Context.CheckCancelled(); if (currentRow["ATTRIBUTE_INDEXED"].ToString() == "true") { overlapText = ""; sameDimRows = dt.Select("DIMENSION_NAME='" + currentRow["DIMENSION_NAME"] + "' AND ATTRIBUTE_NAME='" + currentRow["ATTRIBUTE_NAME"] + "' AND PARTITION_NAME<>'" + currentRow["PARTITION_NAME"] + "' AND ATTRIBUTE_INDEXED='true'"); notFirstDim = false; foreach (DataRow dr in sameDimRows) { Context.CheckCancelled(); if ( ( (Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString())) && (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString())) ) || ( (Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString())) && (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString())) ) || ( (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MIN"].ToString()) <= Int32.Parse(dr["ATTRIBUTE_COUNT_MIN"].ToString())) && (Int32.Parse(currentRow["ATTRIBUTE_COUNT_MAX"].ToString()) >= Int32.Parse(dr["ATTRIBUTE_COUNT_MAX"].ToString())) ) ) { if (notFirstDim) overlapText+=", "; overlapText+=dr["PARTITION_NAME"]; notFirstDim = true; } } currentRow["Overlap"] = overlapText; } dt.AcceptChanges(); } } server.Disconnect(); return dt; }
public static void DeployScript(ProjectItem projItem, DTE2 ApplicationObject) { Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object; try { //validate the script because deploying an invalid script makes cube unusable Microsoft.AnalysisServices.Design.Scripts script = new Microsoft.AnalysisServices.Design.Scripts(oCube); } catch (Microsoft.AnalysisServices.Design.ScriptParsingFailed ex) { string throwaway = ex.Message; MessageBox.Show("MDX Script in " + oCube.Name + " is not valid.", "Problem Deploying MDX Script"); return; } if (oCube.MdxScripts.Count == 0) { MessageBox.Show("There is no MDX script defined in this cube yet."); return; } try { ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy); ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 1, 5); // Check if the file is read-only (and probably checked in to a source control system) // before attempting to save. (issue: 10327 ) FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1)); if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly) { //TODO - can I check and maybe prompt before saving? //Save the cube projItem.Save(""); } ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 2, 5); // extract deployment information DeploymentSettings deploySet = new DeploymentSettings(projItem); // use xlst to create xmla alter command XslCompiledTransform xslt = new XslCompiledTransform(); XmlReader xsltRdr; XmlReader xrdr; // read xslt from embedded resource xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployMdxScript)); using ((xsltRdr)) { // read content from .cube file xrdr = XmlReader.Create(projItem.get_FileNames(1)); using (xrdr) { ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 3, 5); // Connect to Analysis Services Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server(); svr.Connect(deploySet.TargetServer); ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 4, 5); // execute the xmla try { Microsoft.AnalysisServices.Scripter scr = new Microsoft.AnalysisServices.Scripter(); // Build up the Alter MdxScript command using XSLT against the .cube file XslCompiledTransform xslta = new XslCompiledTransform(); StringBuilder sb = new StringBuilder(); XmlWriterSettings xws = new XmlWriterSettings(); xws.OmitXmlDeclaration = true; xws.ConformanceLevel = ConformanceLevel.Fragment; XmlWriter xwrtr = XmlWriter.Create(sb, xws); xslta.Load(xsltRdr); XsltArgumentList xslarg = new XsltArgumentList(); Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase); if (targetDB == null) { throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer)); } string targetDatabaseID = targetDB.ID; xslarg.AddParam("TargetDatabase", "", targetDatabaseID); xslta.Transform(xrdr, xslarg, xwrtr); // Extract the current script from the server and keep a temporary backup copy of it StringBuilder sbBackup = new StringBuilder(); XmlWriterSettings xwSet = new XmlWriterSettings(); xwSet.ConformanceLevel = ConformanceLevel.Fragment; xwSet.OmitXmlDeclaration = true; xwSet.Indent = true; XmlWriter xwScript = XmlWriter.Create(sbBackup, xwSet); Cube oServerCube = targetDB.Cubes.Find(oCube.ID); if (oServerCube == null) { throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer)); } else if (oServerCube.State == AnalysisState.Unprocessed) { throw new System.Exception(string.Format("The {0} cube is not processed the {1} server.", oCube.Name, deploySet.TargetServer)); } if (oServerCube.MdxScripts.Count == 0) { scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { oServerCube }, xwScript, true); } else { MdxScript mdxScr = oServerCube.MdxScripts[0]; scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { mdxScr }, xwScript, true); } xwScript.Close(); // update the MDX Script XmlaResultCollection xmlaRC = svr.Execute(sb.ToString()); if (xmlaRC.Count == 1 && xmlaRC[0].Messages.Count == 0) { // all OK - 1 result - no messages } else { StringBuilder sbErr = new StringBuilder(); for (int iRC = 0; iRC < xmlaRC.Count; iRC++) { for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++) { sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description); } } MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy MDX Script"); } // Test the MDX Script AdomdConnection cn = new AdomdConnection("Data Source=" + deploySet.TargetServer + ";Initial Catalog=" + deploySet.TargetDatabase); cn.Open(); AdomdCommand cmd = cn.CreateCommand(); string qry = "SELECT {} ON 0 FROM [" + oCube.Name + "];"; cmd.CommandText = qry; try { // test that we can query the cube without errors cmd.Execute(); // Building the project means that the .asdatabase file gets re-built so that // we do not break the Deployment Wizard. // -- // This line is included in this try block so that it is only executed if we can // successfully query the cube without errors. projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false); } catch (System.Exception ex) { // undo the deployment if we caught an exception during the deployment svr.Execute(sbBackup.ToString()); MessageBox.Show(ex.Message); } finally { cmd.Dispose(); cn.Close(); cn.Dispose(); } } catch (System.Exception ex) { if (MessageBox.Show("The following error occured while trying to deploy the MDX Script\r\n" + ex.Message + "\r\n\r\nDo you want to see a stack trace?" , "BIDSHelper - Deploy MDX Script" , MessageBoxButtons.YesNo , MessageBoxIcon.Error , MessageBoxDefaultButton.Button2) == DialogResult.Yes) { MessageBox.Show(ex.StackTrace); } } finally { ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 5, 5); // report any results back (status bar?) svr.Disconnect(); svr.Dispose(); } } } } finally { ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy); ApplicationObject.StatusBar.Progress(false, "Deploying MdxScript", 5, 5); } }
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 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); } }
static void PartitionInfo() { string srv_name = @"SCRBMSBDK000660"; string db_name = "FBR_FYPnL_DPRD"; string cube_name = "FYPnL Cube"; string mg_name = "USD"; //TextWriter tw = new StreamWriter("date.txt", true); Microsoft.AnalysisServices.Server srv; Microsoft.AnalysisServices.Database db; srv = new Microsoft.AnalysisServices.Server(); try { srv.Connect(srv_name); logMessageFmt("Databases on [{0}]: {1}", srv_name, srv.Databases.Count); db = srv.Databases.FindByName(db_name); Cube cube = db.Cubes.FindByName(cube_name); CubeDimension cubedim = cube.Dimensions[0]; Dimension dbdim = cubedim.Dimension; DimensionAttribute dbattr = dbdim.Attributes[0]; //var Source = dbattr.Source; System.Data.DataSet ds = dbdim.DataSourceView.Schema; string dsid = db.DataSourceViews[0].DataSourceID; DataTable dt = db.DataSourceViews[0].Schema.Tables[0]; //db.DataSources[0]. //DataTable dt = ds.Tables["SHARED_DimBrand"]; //ep = dt.ExtendedProperties. MeasureGroup mg = cube.MeasureGroups.FindByName(mg_name); MeasureGroupDimension mgd = mg.Dimensions[0]; List <MeasureGroupAttribute> alist = new List <MeasureGroupAttribute>(); if (mgd is RegularMeasureGroupDimension) { RegularMeasureGroupDimension rmgd = (RegularMeasureGroupDimension)mgd; foreach (MeasureGroupAttribute mgattr in rmgd.Attributes) { if (mgattr.Type == MeasureGroupAttributeType.Granularity) { alist.Add(mgattr); } } //MeasureGroupAttribute mgattr = rmgd.Attributes.f["Key"]; } Type t = alist[0].KeyColumns[0].Source.GetType(); Measure msr = mg.Measures[0]; foreach (Partition part in mg.Partitions) { string src; TabularBinding tb = part.Source; if (tb is QueryBinding) { src = String.Format("QUERY: {0}", ((QueryBinding)tb).QueryDefinition); } else if (tb is TableBinding) { src = String.Format("TABLE: {0}.{1}", ((TableBinding)tb).DbSchemaName, ((TableBinding)tb).DbTableName); } else if (tb is DsvTableBinding) { src = String.Format("DSV: {0}.{1}", ((DsvTableBinding)tb).DataSourceViewID, ((DsvTableBinding)tb).TableID); } else { src = String.Empty; } logMessageFmt("Partition [{0}]: {1}", part.Name, src /*part.EstimatedRows*/); //part.Process() } //Partition part = mg.Partitions[0]; //.FindByName(part_name); logMessage("Done."); Console.ReadKey(); } finally { if (srv.Connected == true) { srv.Disconnect(); } } }
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 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); } }
// Clears the cache for the current database // This method uses Server.Execute() as I need to establish an AMO // connection anyway in order to find the CubeID from the CubeName // Other methods in this class use the XmlaClient objects to execute // the Xmla requests. public void ClearCache(string cubeName) { // only way to get a DatabaseID from a Database name appears to be to use AMO string dbId = ""; Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server(); using (svr) { svr.Connect(Context.CurrentServerID); try { Database db = svr.Databases.FindByName(Context.CurrentDatabaseName); dbId = db.ID; if (cubeName.Length != 0) { Cube c = db.Cubes.FindByName(cubeName); if (c != null) { cubeName = "<CubeID>" + c.ID + "</CubeID>"; } else { throw new Exception("The cube '" + cubeName + "' does not exist in the " + Context.CurrentDatabaseName + " database."); } } // execute clear cache based on the ID string clearCmd = string.Format(CLEARCACHE_TEMPLATE, dbId, cubeName); svr.Execute(clearCmd); } catch { // re-throw any exception throw; } finally { // clean up svr.Disconnect(); } } }
public ActionResponse Execute() { var ids = new SSASIdentifiers(_input, _output); using (_server) { Database database; _server.Connect($"Data Source={_output.Connection.Server};Catalog=;"); if (_server.Databases.Contains(ids.DatabaseId)) { database = _server.Databases.Find(ids.DatabaseId); _output.Info($"Updating existing OLAP database: {ids.DatabaseId}"); } else { database = new Database() { Name = ids.DatabaseId, ID = ids.DatabaseId, DataSourceImpersonationInfo = new ImpersonationInfo { ImpersonationMode = ImpersonationMode.Default } }; _server.Databases.Add(database); _output.Info($"Creating new OLAP database: {ids.DatabaseId}"); if (!SSAS.Save(_server, _output, database)) { return(new ActionResponse(500, $"Could not save OLAP database: {ids.DatabaseId}")); } } DataSource dataSource; if (database.DataSources.Contains(ids.DataSourceId)) { dataSource = database.DataSources.Find(ids.DataSourceId); _output.Info($"Updating existing data source: {ids.DataSourceId}"); } else { dataSource = database.DataSources.AddNew(ids.DataSourceId); _output.Info($"Creating new data source: {ids.DataSourceId}"); } dataSource.ConnectionString = $"Provider=SQLOLEDB.1;Data Source={_input.Connection.Server};Integrated Security=SSPI;Initial Catalog={_input.Connection.Database}"; dataSource.ImpersonationInfo = new ImpersonationInfo { ImpersonationMode = ImpersonationMode.ImpersonateServiceAccount }; dataSource.Timeout = System.TimeSpan.FromSeconds(_input.Connection.RequestTimeout); if (!SSAS.Save(_server, _output, dataSource)) { return(new ActionResponse(500, $"Could not save data source: {ids.DataSourceId}")); } // view var entity = _output.Entity; var fields = SSAS.GetFields(_output); DataSourceView dataSourceView; if (database.DataSourceViews.Contains(ids.DataSourceViewId)) { dataSourceView = database.DataSourceViews.Find(ids.DataSourceViewId); if (dataSourceView.Schema.Tables.Contains(entity.Alias)) { dataSourceView.Schema.Tables.Remove(entity.Alias); } dataSourceView.Schema.Tables.Add(CreateDataTable(_output, fields)); _output.Info($"Updating existing data source view: {ids.DataSourceViewId}"); } else { _output.Info($"Creating new data source view: {ids.DataSourceViewId}"); dataSourceView = database.DataSourceViews.AddNew(ids.DataSourceViewId); dataSourceView.DataSourceID = ids.DataSourceId; dataSourceView.Schema = new DataSet("Schema"); dataSourceView.Schema.Tables.Add(CreateDataTable(_output, fields)); if (!SSAS.Save(_server, _output, dataSourceView)) { return(new ActionResponse(500, $"Could not save data source view: {ids.DataSourceViewId}")); } } Dimension dimension; if (database.Dimensions.Contains(entity.Alias)) { dimension = database.Dimensions.Find(entity.Alias); _output.Info($"Updating existing dimension: {entity.Alias}"); } else { dimension = database.Dimensions.AddNew(entity.Alias, entity.Alias); dimension.WriteEnabled = false; dimension.StorageMode = DimensionStorageMode.Molap; _output.Info($"Creating new dimension: {entity.Alias}"); } dimension.Source = new DataSourceViewBinding(ids.DataSourceViewId); foreach (var field in fields) { DimensionAttribute dimensionAttribute; if (dimension.Attributes.Contains(field.Alias)) { dimensionAttribute = dimension.Attributes.Find(field.Alias); } else { dimensionAttribute = dimension.Attributes.Add(field.Label, field.Alias); dimensionAttribute.OrderBy = OrderBy.Key; // for now } var optimize = field.PrimaryKey || field.Dimension == "true" || field.Dimension == "default" && !field.IsNumeric(); dimensionAttribute.AttributeHierarchyEnabled = optimize; dimensionAttribute.IsAggregatable = optimize; dimensionAttribute.Usage = field.PrimaryKey ? AttributeUsage.Key : AttributeUsage.Regular; dimensionAttribute.KeyColumns.Clear(); DataItem keyColumn; if (field.Type == "string") { var length = field.Length == "max" ? int.MaxValue : Convert.ToInt32(field.Length); keyColumn = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field), length) { Trimming = Trimming.None }; } else { keyColumn = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field)); } dimensionAttribute.KeyColumns.Add(keyColumn); } if (!SSAS.Save(_server, _output, dimension)) { return(new ActionResponse(500, $"Could not save dimension: {entity.Alias}")); } Cube cube; if (database.Cubes.Contains(ids.CubeId)) { cube = database.Cubes.Find(ids.CubeId); _output.Info($"Updating existing cube: {ids.CubeId}"); } else { cube = database.Cubes.AddNew(ids.CubeId, ids.CubeId); _output.Info($"Creating new cube: {ids.CubeId}"); } cube.Source = new DataSourceViewBinding(ids.DataSourceViewId); CubeDimension cubeDimension; if (cube.Dimensions.Contains(entity.Alias)) { cubeDimension = cube.Dimensions.Find(entity.Alias); } else { cubeDimension = cube.Dimensions.Add(entity.Alias); } cubeDimension.Attributes.Clear(); foreach (DimensionAttribute attribute in database.Dimensions.Find(entity.Alias).Attributes) { cubeDimension.Attributes.Add(attribute.ID); } var normalMeasureGroup = GetMeasureGroup(cube, entity, ids.NormalMeasureGroupId, ids.DataSourceViewId); // standard count measure Measure countMeasure; if (normalMeasureGroup.Measures.Contains("Count")) { countMeasure = normalMeasureGroup.Measures.Find("Count"); } else { countMeasure = normalMeasureGroup.Measures.Add("Count", "Count"); } countMeasure.AggregateFunction = AggregationFunction.Count; countMeasure.Source = new DataItem(new RowBinding(entity.Alias), OleDbType.Integer); // version measures var versionField = entity.GetVersionField(); if (versionField != null) { Measure versionMeasure; if (normalMeasureGroup.Measures.Contains(ids.VersionId)) { versionMeasure = normalMeasureGroup.Measures.Find(ids.VersionId); } else { versionMeasure = normalMeasureGroup.Measures.Add(ids.VersionId); } versionMeasure.AggregateFunction = AggregationFunction.Max; versionMeasure.Source = new DataItem(entity.Alias, versionField.Alias, SSAS.GetOleDbType(versionField)); versionMeasure.Visible = false; } // defined measures var measureFields = entity.GetAllOutputFields().Where(f => f.Measure && f.Type != "byte[]"); // normal measures foreach (var field in measureFields.Where(f => f.AggregateFunction != "distinctcount")) { Measure measure; var function = (AggregationFunction)Enum.Parse(typeof(AggregationFunction), field.AggregateFunction, true); if (normalMeasureGroup.Measures.Contains(field.Alias)) { measure = normalMeasureGroup.Measures.Find(field.Alias); } else { measure = normalMeasureGroup.Measures.Add(field.Label, field.Alias); } measure.AggregateFunction = function; measure.FormatString = field.Format; measure.Source = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field)); } // distinct measures foreach (var field in measureFields.Where(f => f.AggregateFunction == "distinctcount")) { var type = SSAS.GetOleDbType(field); if (SSAS.CanDistinctCount(type)) { Measure measure; var distinctMeasureGroup = GetMeasureGroup(cube, entity, ids.DistinctMeasureGroupId, ids.DataSourceViewId); if (distinctMeasureGroup.Measures.Contains(field.Alias)) { measure = distinctMeasureGroup.Measures.Find(field.Alias); } else { measure = distinctMeasureGroup.Measures.Add(field.Label, field.Alias); } measure.AggregateFunction = AggregationFunction.DistinctCount; measure.FormatString = field.Format; measure.Source = new DataItem(entity.Alias, field.Alias, SSAS.GetOleDbType(field)); } else { _output.Warn($"Can not distinct count {field.Label} using {field.Type} type."); } } if (!SSAS.Save(_server, _output, cube)) { return(new ActionResponse(500, $"Could not save cube: {ids.CubeId}")); } _server.Disconnect(); } return(new ActionResponse()); }
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); } }
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); } }
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"); } }
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; }
/// <summary> /// This method ensures the tabular model is online and populates the CompatibilityLevel property. /// </summary> /// <param name="closedBimFile">A Boolean specifying if the user cancelled the comparison. For the case where running in Visual Studio, the user has the option of cancelling if the project BIM file is open.</param> public void InitializeCompatibilityLevel(bool closedBimFile = false) { if (UseBimFile) { TOM.Database tomDatabase = null; bool exceptionLoadingFile = false; try { tomDatabase = OpenDatabaseFromFile(); } catch { exceptionLoadingFile = true; } if (exceptionLoadingFile || tomDatabase == null) { throw new ConnectionException($"Can't load file \"{_bimFile}\"."); } _compatibilityLevel = tomDatabase.CompatibilityLevel; _dataSourceVersion = tomDatabase.Model.DefaultPowerBIDataSourceVersion.ToString(); _directQuery = (tomDatabase.Model != null && tomDatabase.Model.DefaultMode == Microsoft.AnalysisServices.Tabular.ModeType.DirectQuery); return; } if (UseProject) { //Initialize _projectDirectoryInfo FileInfo projectFileInfo; if (_project == null) { //Probably running in command-line mode projectFileInfo = new FileInfo(_projectFile); } else { projectFileInfo = new FileInfo(_project.FullName); } _projectDirectoryInfo = new DirectoryInfo(projectFileInfo.Directory.FullName); //Read settings file to get workspace server/db ReadSettingsFile(); //Read project file to get deployment server/cube names, and bim file ReadProjectFile(); //Overwrite the server if a workspace server provided if (_workspaceServerProvided) { this.ServerName = _workspaceServer; } } Microsoft.AnalysisServices.Server amoServer = new Microsoft.AnalysisServices.Server(); try { amoServer.Connect(BuildConnectionString()); } catch (ConnectionException) when(UseProject) { //See if can find integrated workspace server bool foundServer = false; string tempDataDir = Path.GetTempPath() + @"Microsoft\Microsoft SQL Server\OLAP\LocalServer\Data"; if (Directory.Exists(tempDataDir)) { var subDirs = Directory.GetDirectories(tempDataDir).OrderByDescending(d => new DirectoryInfo(d).CreationTime); //Need to order by descending in case old folders hanging around when VS was killed and SSDT didn't get a chance to clean up after itself foreach (string subDir in subDirs) { string[] iniFilePath = Directory.GetFiles(subDir, "msmdsrv.ini"); if (iniFilePath.Length == 1 && File.ReadAllText(iniFilePath[0]).Contains("<DataDir>" + _projectDirectoryInfo.FullName + @"\bin\Data</DataDir>")) //Todo: proper xml lookup { //Assuming this must be the folder, so now get the port number string[] portFilePath = Directory.GetFiles(subDir, "msmdsrv.port.txt"); if (portFilePath.Length == 1) { string port = File.ReadAllText(portFilePath[0]).Replace("\0", ""); this.ServerName = $"localhost:{Convert.ToString(port)}"; amoServer.Connect(BuildConnectionString()); foundServer = true; break; } } } } if (!foundServer) { throw; } } ////non-admins can't see any ServerProperties: social.msdn.microsoft.com/Forums/sqlserver/en-US/3d0bf49c-9034-4416-9c51-77dc32bf8b73/determine-current-user-permissionscapabilities-via-amo-or-xmla //if (!(amoServer.ServerProperties.Count > 0)) //non-admins can't see any ServerProperties //{ // throw new Microsoft.AnalysisServices.ConnectionException($"Current user {WindowsIdentity.GetCurrent().Name} is not an administrator on the Analysis Server " + this.ServerName); //} if (amoServer.ServerMode != ServerMode.Tabular && amoServer.ServerMode != ServerMode.SharePoint) //SharePoint is what Power BI Desktop runs as { throw new ConnectionException($"Analysis Server {this.ServerName} is not running in Tabular mode"); } Microsoft.AnalysisServices.Database amoDatabase = null; if (this.DatabaseName == "" && this.ServerName.ToUpper().StartsWith("localhost:".ToUpper())) { //PBI Desktop doesn't have db name yet if (amoServer.Databases.Count > 0) { amoDatabase = amoServer.Databases[0]; this.DatabaseName = amoDatabase.Name; } } else { amoDatabase = amoServer.Databases.FindByName(this.DatabaseName); } if (amoDatabase == null) { if (!this.UseProject) { throw new ConnectionException("Could not connect to database " + this.DatabaseName); } else { /* Check if folder exists using SystemGetSubdirs. If so attach. If not, do nothing - when execute BIM file below will create automatically. * Using XMLA to run SystemGetSubdirs rather than ADOMD.net here don't want a reference to ADOMD.net Dll. * Also, can't use Server.Execute method because it only takes XMLA execute commands (as opposed to XMLA discover commands), so need to submit the full soap envelope */ string dataDir = amoServer.ServerProperties["DataDir"].Value; if (dataDir.EndsWith("\\")) { dataDir = dataDir.Substring(0, dataDir.Length - 1); } string commandStatement = String.Format("SystemGetSubdirs '{0}'", dataDir); bool foundFault = false; XmlNodeList rows = Core.Comparison.ExecuteXmlaCommand(amoServer, "", commandStatement, ref foundFault); string dbDir = ""; foreach (XmlNode row in rows) { XmlNode dirNode = null; XmlNode allowedNode = null; foreach (XmlNode childNode in row.ChildNodes) { if (childNode.Name == "Dir") { dirNode = childNode; } else if (childNode.Name == "Allowed") { allowedNode = childNode; } } if (dirNode != null && allowedNode != null && dirNode.InnerText.Length >= this.DatabaseName.Length && dirNode.InnerText.Substring(0, this.DatabaseName.Length) == this.DatabaseName && allowedNode.InnerText.Length > 0 && allowedNode.InnerText == "1") { dbDir = dataDir + "\\" + dirNode.InnerText; break; } } if (dbDir != "") { //attach amoServer.Attach(dbDir); amoServer.Refresh(); amoDatabase = amoServer.Databases.FindByName(this.DatabaseName); } } } if (this.UseProject) { //_bimFileFullName = GetBimFileFullName(); if (String.IsNullOrEmpty(_ssdtBimFile)) { throw new ConnectionException("Could not load BIM file for Project " + this.ProjectName); } if (!closedBimFile) //If just closed BIM file, no need to execute it { //Execute BIM file contents as script on workspace database //We don't know the compatibility level yet, so try parsing json, if fail, try xmla ... try { //Replace "SemanticModel" with db name. JObject jDocument = JObject.Parse(File.ReadAllText(_ssdtBimFile)); if (jDocument["name"] == null || jDocument["id"] == null) { throw new ConnectionException("Could not read JSON in BIM file " + _ssdtBimFile); } jDocument["name"] = DatabaseName; jDocument["id"] = DatabaseName; //Todo: see if Tabular helper classes for this once documentation available after CTP string command = $@"{{ ""createOrReplace"": {{ ""object"": {{ ""database"": ""{DatabaseName}"" }}, ""database"": {jDocument.ToString()} }} }} "; amoServer.Execute(command); } catch (JsonReaderException) { //Replace "SemanticModel" with db name. Could do a global replace, but just in case it's not called SemanticModel, use dom instead //string xmlaScript = File.ReadAllText(xmlaFileFullName); XmlDocument document = new XmlDocument(); document.Load(_ssdtBimFile); XmlNamespaceManager nsmgr = new XmlNamespaceManager(document.NameTable); nsmgr.AddNamespace("myns1", "http://schemas.microsoft.com/analysisservices/2003/engine"); XmlNode objectDatabaseIdNode = document.SelectSingleNode("//myns1:Object/myns1:DatabaseID", nsmgr); XmlNode objectDefinitionDatabaseIdNode = document.SelectSingleNode("//myns1:ObjectDefinition/myns1:Database/myns1:ID", nsmgr); XmlNode objectDefinitionDatabaseNameNode = document.SelectSingleNode("//myns1:ObjectDefinition/myns1:Database/myns1:Name", nsmgr); if (objectDatabaseIdNode == null || objectDefinitionDatabaseIdNode == null || objectDefinitionDatabaseNameNode == null) { throw new ConnectionException("Could not access XMLA in BIM file " + _ssdtBimFile); } objectDatabaseIdNode.InnerText = DatabaseName; objectDefinitionDatabaseIdNode.InnerText = DatabaseName; objectDefinitionDatabaseNameNode.InnerText = DatabaseName; //1103, 1100 projects store the xmla as Alter (equivalent to createOrReplace), so just need to execute amoServer.Execute(document.OuterXml); } } //need next lines in case just created the db using the Execute method //amoServer.Refresh(); //todo workaround for bug 9719887 on 3/10/17 need to disconnect and reconnect amoServer.Disconnect(); amoServer.Connect(BuildConnectionString()); amoDatabase = amoServer.Databases.FindByName(this.DatabaseName); } if (amoDatabase == null) { throw new ConnectionException($"Can not load/find database {this.DatabaseName}."); } _compatibilityLevel = amoDatabase.CompatibilityLevel; if (_compatibilityLevel >= 1400) { _dataSourceVersion = amoDatabase.Model.DefaultPowerBIDataSourceVersion.ToString(); } _serverMode = amoServer.ServerMode; _directQuery = ((amoDatabase.Model != null && amoDatabase.Model.DefaultMode == Microsoft.AnalysisServices.Tabular.ModeType.DirectQuery) || amoDatabase.DirectQueryMode == DirectQueryMode.DirectQuery || amoDatabase.DirectQueryMode == DirectQueryMode.InMemoryWithDirectQuery || amoDatabase.DirectQueryMode == DirectQueryMode.DirectQueryWithInMemory); }