public TabularItems.Measure GetMeasure(string measureName)
{
if (!server.Connected)
{
throw new InvalidOperationException("You must be connect to the server");
}
Database database = server.Databases.FindByName(databaseName);
if (database == null)
{
throw new InvalidOperationException(string.Format(
"Database '{0}' does not exist on server '{1}'",
databaseName, server.Name));
}
Cube cube = database.Cubes.FindByName(cubeName);
if (cube == null)
{
throw new InvalidOperationException(string.Format(
"Cube '{0}' does not exist in database '{1}'",
cubeName, database));
}
TabularItems.Measure measure = new TabularItems.Measure(cube, measureName);
if (measure == null)
{
throw new InvalidOperationException("Measure " + measureName + " was not found in database " + this.databaseName);
}
return(measure);
}
public void GetMeasure_SSAS2016()
{
var tabular = new DaxDrill.Tabular.TabularHelper("FINSERV01", "HRR_Snap");
tabular.Connect();
TabularItems.Measure measure = tabular.GetMeasure("HRR_ExGST_Sum");
Console.WriteLine(measure.Name);
}
public void GetMeasure()
{
string measureName = "Gross Billed Sum";
TabularItems.Measure measure = null;
using (var tabular = new DaxDrill.Tabular.TabularHelper(serverName, "Roaming"))
{
tabular.Connect();
measure = tabular.GetMeasure(measureName);
tabular.Disconnect();
}
Console.WriteLine("Measure = {0}, Table = {1}", measure.Name, measure.TableName);
}
private TabularItems.Measure GetMeasure(Excel.Range rngCell)
{
var cnnBuilder = new TabularConnectionStringBuilder(this.connectionString);
string measureName = GetMeasureName(rngCell);
TabularItems.Measure measure = null;
using (var tabular = new DaxDrill.Tabular.TabularHelper(cnnBuilder.DataSource, cnnBuilder.InitialCatalog))
{
tabular.Connect();
measure = tabular.GetMeasure(measureName);
tabular.Disconnect();
}
return(measure);
}
// get DAX query from XML Data based on active rngCell
public string GetCustomTableQuery(Excel.Range rngCell)
{
Excel.Worksheet sheet = (Excel.Worksheet)rngCell.Parent;
Excel.Workbook workbook = (Excel.Workbook)sheet.Parent;
TabularItems.Measure measure = GetMeasure(rngCell);
#region measure
// get referenced measure
Office.CustomXMLNode node = ExcelHelper.GetCustomXmlNode(workbook, Constants.DaxDrillXmlSchemaSpace,
string.Format("{0}[@id='{1}']", Constants.MeasureXpath, measure.Name));
string measureName = measure.Name;
if (node != null)
{
foreach (Office.CustomXMLNode attr in node.Attributes)
{
if (attr.BaseName == "ref")
{
// get DAX query by measure id
node = ExcelHelper.GetCustomXmlNode(workbook, Constants.DaxDrillXmlSchemaSpace,
string.Format("{0}[@id='{1}']/x:query", Constants.MeasureXpath, attr.Text));
break;
}
}
}
#endregion
#region table
// get DAX query by table id (if measure not found in XML metadata)
if (node == null)
{
node = ExcelHelper.GetCustomXmlNode(workbook, Constants.DaxDrillXmlSchemaSpace,
string.Format("{0}[@id='{1}']/x:query", Constants.TableXpath, measure.TableName));
}
#endregion
if (node != null)
{
return(node.Text);
}
return(string.Empty);
}
public TabularItems.Measure GetMeasureFromDMV(string measureName)
{
if (!server.Connected)
{
throw new InvalidOperationException("You must be connected to the server");
}
var dmv = string.Format(
@"SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [TABLE],[MEASURE_CAPTION] AS [MEASURE],
[MEASURE_IS_VISIBLE]
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE CUBE_NAME ='Model'
AND MEASURE_CAPTION = '{0}'" ,
measureName);
var daxClient = new DaxHelpers.DaxClient();
System.Data.DataTable dtResult = null;
using (var cnn = new ADOMD.AdomdConnection(connectionString))
{
cnn.Open();
dtResult = daxClient.ExecuteTable(dmv, cnn);
cnn.Close();
}
TabularItems.Measure measure = null;
foreach (System.Data.DataRow drow in dtResult.Rows)
{
if (Convert.ToString(drow["MEASURE"]) == measureName)
{
measure = new TabularItems.Measure(Convert.ToString(drow["TABLE"]), measureName);
break;
}
}
if (measure == null)
{
throw new InvalidOperationException("Measure " + measureName + " was not found in database " + this.databaseName);
}
return(measure);
}
public IEnumerable <DetailColumn> GetCustomDetailColumns(Excel.Range rngCell)
{
Excel.WorkbookConnection wbcnn = null;
Excel.Workbook workbook = null;
Excel.Worksheet sheet = null;
wbcnn = ExcelHelper.GetWorkbookConnection(rngCell);
sheet = (Excel.Worksheet)rngCell.Parent;
workbook = (Excel.Workbook)sheet.Parent;
TabularItems.Measure measure = GetMeasure(rngCell);
string xmlString = ExcelHelper.ReadCustomXmlNode(
workbook, Constants.DaxDrillXmlSchemaSpace,
string.Format("{0}[@id='{1}']", Constants.TableXpath, measure.TableName));
List <DetailColumn> columns = DaxDrillConfig.GetColumnsFromTableXml(Constants.DaxDrillXmlSchemaSpace, xmlString, wbcnn.Name, measure.TableName);
return(columns);
}