Custom .Net Calculations SSAS

Question

I am seeking ideas on how to solve the following problem:

I want to give users the ability to use PowerBI and Excel to aggregate/filter data by "Investment Class" and return a measure which is the XIRR over the related transactions. To make it interesting I need to use my own custom XIRR algorithm (currently implemented in .Net). I would ideally like to be able to use PowerBI or SSAS Tabular for my semantic model to allow easy usage from Excel, PowerBI, Tableau etc.

Traditionally I have solved this problem using SSAS Multidimensional and a custom 'Stored Procedure' in the cube implemented in .Net to handle this calculation. I am hoping to accomplish this in a more modern cloud PaaS architecture that PowerBI or SSAS Tabular would provide.

Given that I can implement custom aggregations and TVFs, etc in SQL Server written in .Net I have explored using DirectQuery techniques, but this usually falls short when looking at multi-select dimension value scenarios.

Other solutions I am considering also include using non Microsoft MDX engines like Apache Kylin or Kyligence, which would provide the MDX interface with a cloud PaaS service, but would love to keep this in the MS realm if possible.

Hopefully this conveys my challenge. Ideally being able to use custom code in SSAS Tabular/PowerBI for calculated measures like we can in MDX would be a solve.

Create Table dim_Investment (
 Investment_Id int not null Primary Key,
 Investment_Class nvarchar(100) not null
)

Create Table fct_Transaction (
  Investment_Id int not null,
  Transaction_Date Date not null,
  Transaction_Amount money not null
)

-- Example measure calc in SQL
Select
 Custom_XIRR(f.Transaction_Date, f.Transaction_Amount, 0.1) As IRR
From
 fct_Transaction As f
 Inner Join dim_Investment As d On
  (f.Investment_Id = d.Investment_Id )
Where
 (d.Investment_Class In ('Pre IPO', 'Distressed'))

Answer 1

Are you completely sure you can't implement your custom code in DAX? Using custom code for calculations like this in Power BI/Tabular is not going to happen any time soon, unfortunately.

Chris