public static List <DatabaseInfo> GetDatabasesFromServer(TOM.Server server)
{
var result = new List <DatabaseInfo>();
using (var reader = server.ExecuteReader("<Statement>SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS</Statement>", out AS.XmlaResultCollection results))
{
if (results != null && results.ContainsErrors)
{
throw new Exception("Unable to query databases from server: " + results.GetMessageString());
}
while (reader.Read())
{
// If multi-dimensional and tabular servers are installed side-by-side, we may have
// records where the COMPATIBILITY_LEVEL column is null - filter these away:
if (reader.IsDBNull(reader.GetOrdinal("COMPATIBILITY_LEVEL")))
{
continue;
}
result.Add(new DatabaseInfo
{
ID = reader.GetString(reader.GetOrdinal("DATABASE_ID")),
Name = reader.GetString(reader.GetOrdinal("CATALOG_NAME")),
CompatibilityLevel = reader.GetInt32(reader.GetOrdinal("COMPATIBILITY_LEVEL")),
LastUpdate = reader.GetDateTime(reader.GetOrdinal("DATE_MODIFIED")),
Description = reader.GetString(reader.GetOrdinal("DESCRIPTION"))
});
}
}
return(result);
}
private static XmlaResultCollection DynamicXMLAParser(
string serverAddress, string database, string XMLAQuery, bool outputToCsv)
{
string ConnectionString = @"Provider=MSOLAP;Data Source=" + serverAddress +
@";Initial Catalog=" + database + ";Integrated Security=SSPI;ImpersonationLevel = Impersonate; persist security info = True; ";
var XMLA = XMLAQuery;
var objServer = new Microsoft.AnalysisServices.Tabular.Server();
objServer.Connect(ConnectionString);
var reader = objServer.ExecuteReader(XMLA, out XmlaResultCollection
resultsOut, null, true);
// Add all roles to Cached List
//while (reader.Read())
//{
//}
reader.Close();
reader.Dispose();
objServer.Dispose();
return(resultsOut);
}
public static List <DatabaseInfo> GetDatabasesFromServer(TOM.Server server)
{
var result = new List <DatabaseInfo>();
using (var reader = server.ExecuteReader("<Statement>SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS</Statement>", out AS.XmlaResultCollection results))
{
if (results != null && results.ContainsErrors)
{
throw new Exception("Unable to query databases from server: " + results.GetMessageString());
}
while (reader.Read())
{
result.Add(new DatabaseInfo
{
ID = reader.GetString(reader.GetOrdinal("DATABASE_ID")),
Name = reader.GetString(reader.GetOrdinal("CATALOG_NAME")),
CompatibilityLevel = reader.GetInt32(reader.GetOrdinal("COMPATIBILITY_LEVEL")),
LastUpdate = reader.GetDateTime(reader.GetOrdinal("DATE_MODIFIED")),
Description = reader.GetString(reader.GetOrdinal("DESCRIPTION"))
});
}
}
return(result);
}
private static void DynamicRoleCreation(string roleName,
string serverAddress, string databaseId, string filterExpression)
{
string ConnectionString = @"Provider=MSOLAP;Data Source=" + serverAddress +
@";Initial Catalog=" + databaseId + ";Integrated Security=SSPI;ImpersonationLevel = Impersonate; persist security info = True; ";
var RoleIDNamePair = new Dictionary <int, string>();
// Get Roles Where Rolename == 'NewlycreatedRoleName=@Param'
var GetRolesIDXMLA = @"<Batch xmlns
=""http://schemas.microsoft.com/analysisservices/2003/engine""
Transaction=""true""><Discover xmlns = ""urn:schemas-microsoft-com:xmlanalysis""><RequestType>TMSCHEMA_ROLES</RequestType><Restrictions/><Properties/></Discover
></Batch>";
// Take Param: @RoleName for use in XMLA to create role
var xmlaCreateRole = @"<Batch Transaction=""false""
xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><Create
xmlns=""http://schemas.microsoft.com/analysisservices/2014/engine""><DatabaseID>" + databaseId + @"</DatabaseID><Roles><xs:schema xmlns:xs=""http://www.w3.org/2001/XMLSchema""
xmlns:sql=""urn:schemas-microsoft-com:xmlsql""><xs:element><xs:complexType><xs:sequence><xs:element
type=""row""/></xs:sequence></xs:complexType></xs:element><xs:complexType
name=""row""><xs:sequence><xs:element name=""Name"" type=""xs:string"" sql:field=""Name""
minOccurs=""0""/><xs:element name=""Description"" type=""xs:string""
sql:field=""Description"" minOccurs=""0""/><xs:element name=""ModelPermission""
type=""xs:long"" sql:field=""ModelPermission""
minOccurs=""0""/></xs:sequence></xs:complexType></xs:schema><row xmlns=""urn:schemasmicrosoft-com:xmlanalysis:rowset""><Name>" + roleName + @"</Name><ModelPermission>2</ModelPermission></row></Ro
les></Create></Batch>";
// Get Role ID Where Rolename == @PRoleName: XMLA??
var objServer = new Microsoft.AnalysisServices.Tabular.Server();
objServer.Connect(ConnectionString);
objServer.Execute(xmlaCreateRole);
var reader = objServer.ExecuteReader(GetRolesIDXMLA, out XmlaResultCollection
resultsOut, null, true);
// Add all roles to Cached List
while (reader.Read())
{
RoleIDNamePair.Add(int.Parse(reader[0].ToString()), reader[2].ToString());
}
reader.Close();
reader.Dispose();
var RoleIDByRoleName = "";//= RoleIDNamePair.Single(s => s.Value == roleName).Key.ToString();
foreach (var pair in RoleIDNamePair)
{
if (pair.Value == roleName)
{
RoleIDByRoleName = pair.Key.ToString();
break;
}
}
var AddFilterTorole = @"<Batch Transaction=""false""
xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><Create
xmlns=""http://schemas.microsoft.com/analysisservices/2014/engine""><DatabaseID>" + databaseId + @"</DatabaseID><TablePermissions><xs:schema
xmlns:xs=""http://www.w3.org/2001/XMLSchema"" xmlns:sql=""urn:schemas-microsoft-com:xmlsql""><xs:element><xs:complexType><xs:sequence><xs:element
type=""row""/></xs:sequence></xs:complexType></xs:element><xs:complexType
name=""row""><xs:sequence><xs:element name=""RoleID"" type=""xs:unsignedLong""
sql:field=""RoleID"" minOccurs=""0""/><xs:element name=""RoleID.Role"" type=""xs:string""
sql:field=""RoleID.Role"" minOccurs=""0""/><xs:element name=""TableID""
type=""xs:unsignedLong"" sql:field=""TableID"" minOccurs=""0""/><xs:element
name=""TableID.Table"" type=""xs:string"" sql:field=""TableID.Table""
minOccurs=""0""/><xs:element name=""FilterExpression"" type=""xs:string""
sql:field=""FilterExpression"" minOccurs=""0""/><xs:element name=""MetadataPermission""
type=""xs:long"" sql:field=""MetadataPermission""
minOccurs=""0""/></xs:sequence></xs:complexType></xs:schema><row xmlns=""urn:schemasmicrosoft-com:xml-analysis:rowset""><RoleID>" + RoleIDByRoleName +
@"</RoleID><TableID>364</TableID><FilterExpression>" + filterExpression + @"</FilterExpression
></row></TablePermissions></Create><SequencePoint
xmlns=""http://schemas.microsoft.com/analysisservices/2014/engine""><DatabaseID>" +
databaseId + @"</DatabaseID></SequencePoint></Batch>";
var applyRoleQuery = AddFilterTorole;
objServer.Execute(applyRoleQuery);
}