private static void DrillThroughThreadSafe(QueryClient queryClient)
{
Excel.Range rngCell = queryClient.RangeCell;
// create sheet
Excel.Sheets sheets = xlApp.Sheets;
Excel.Worksheet sheet = (Excel.Worksheet)sheets.Add();
// show message to user we are retrieving records
Excel.Range rngHead = sheet.Range["A1"];
int maxDrillThroughRecords = ExcelHelper.GetMaxDrillthroughRecords(rngCell);
rngHead.Value2 = string.Format("Retrieving TOP {0} records",
maxDrillThroughRecords);
// set up connection
var connString = ExcelHelper.GetConnectionString(rngCell);
var commandText = queryClient.GetDAXQuery(connString);
var daxClient = new DaxClient();
var cnnStringBuilder = new TabularConnectionStringBuilder(connString);
var cnn = new ADOMD.AdomdConnection(cnnStringBuilder.StrippedConnectionString);
// retrieve result
var dtResult = daxClient.ExecuteTable(commandText, cnn);
// output result to sheet
Excel.Range rngOut = sheet.Range["A3"];
ExcelHelper.FillRange(dtResult, rngOut);
ExcelHelper.FormatRange(dtResult, rngOut);
rngHead.Value2 = string.Format("Retrieved TOP {0} records", maxDrillThroughRecords);
}
public void ProcessCurrent(XPObjectSpace objSpace)
{
var prevSshot = CashFlowHelper.GetPreviousSnapshot(objSpace);
if (prevSshot == null)
{
ProcessCurrentWithoutPrev();
_LastReturnMessage = "Processed without previous snapshot is defined in XPO.";
return;
}
var dax = new DaxClient();
string connectionString = AppConfig.SsasConnectionString;
var cnn = new ADOMD.AdomdConnection(connectionString);
cnn.Open();
var reader = dax.ExecuteReader(@"EVALUATE
ROW
(
""Result"",
CALCULATE (
COUNTROWS( CashFlow ),
CashFlow[Snapshot OID] = ""{OID}""
)
)".Replace("{OID}", "{" + prevSshot.Oid + "}"), cnn);
reader.Read();
var prevRowCount = reader.GetValue(0);
if (prevRowCount == null)
{
ProcessCurrentWithPrev();
_LastReturnMessage = "Processed previous snapshot as it currently does not exist in SSAS.";
}
else
{
ProcessCurrentWithoutPrev();
_LastReturnMessage = "Did not process previous snapshot because previous snapshot already exists in SSAS.";
}
}
public void GetTable_SSAS2014()
{
var commandText = "EVALUATE TOPN( 100, CashFlow )";
string serverName = "FINSERV01";
string connectionString = string.Format(
"Integrated Security=SSPI;Persist Security Info=True;Initial Catalog={1};Data Source={0};", serverName, "CashFlow");
var cnn = new ADOMD.AdomdConnection(connectionString);
var daxClient = new DaxClient();
var dtResult = daxClient.ExecuteTable(commandText, cnn);
var columnNames = "";
foreach (System.Data.DataColumn column in dtResult.Columns)
{
if (string.IsNullOrEmpty(columnNames))
{
columnNames += ",";
}
columnNames += column.ColumnName;
}
Console.WriteLine(columnNames);
foreach (System.Data.DataRow row in dtResult.Rows)
{
var rowText = "";
foreach (object item in row.ItemArray)
{
if (!string.IsNullOrEmpty(rowText))
{
rowText += ",";
}
rowText += Convert.ToString(item);
}
Console.WriteLine(rowText);
}
}
