Integrating Power BI with COGNOS with added logic

[TejaManabotula]

Hi Everyone,

I just joined the PowerBI community would love to get your advice on a high-level to see how to approach the problem I'm facing.

I'm trying to automate a process where data is being pulled from multiple systems/databases and consolidated/manipulated in Excel to create a Dashboard ( also in Excel) for monthly reporting. I am pretty sure I can bring all of the data from different systems into COGNOS, which I can then interface with PowerBI on the front-end to create dashboards for monthly reporting. 

One of the issues I'm facing is that the data is currently being manipulated in Excel using multiple VBA scripts to apply cost distributions that forecast costs for the future. I'm struggling with identifying how the VBA logic can be brought into my plan with COGNOS Connect to IBM Cognos/TM1/Planning Analytics .

Is there currently a way to integrate this VBA logic in a step between COGNOS and Power BI ( or directly in Power BI From Excel with VBA to PowerBI )? Does this need to be done before the data is brought into COGNOS?

If I can convert this logic into a powershell script is there a way to integrate that into Power BI support for powershell scripting in power BI ? Maybe I have to pull the data from COGNOS into a power query where this type of logic can be applied...

Sorry for the long list of questions...but I'm hoping someone here has experienced this in the past and can provide some advice on the best approach.

Thanks,

Teja

[v-yiruan-msft]

Hi @TejaManabotula ,

I'm sorry that I'm not clear about your requirement. Do you want to load the data from COGNOS into Power BI then create dashboard? And there are some VBA logic in these data, you want to know whether there is any method to apply these logic in Power BI directly? If my understanding is wrong, please correct me...

Convert VBA Excel Code to Power BI

Converting a VBA UDF into DAX

Best Regards

[TejaManabotula]

Thank you for your response. Yes you are correct, I want to load the data from COGNOS into powerBI. Currently this data is being pulled from different sources and manipuated in Excel with VBA before creating a dashboard in Excel. I looked through the links you mentioned, but the logic I need is to create a data distributions using gammadist functions that are available in Excel ( https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.gammadist). Is there a way to do this in DAX? I can see there is a normdist function but cant find gamma dist. 

Thanks for your help.

[v-yiruan-msft]

Hi @TejaManabotula ,

Base on my research, currently there is no the function GAMMA.DIST in DAX. You can raise one new idea ticket on Power BI Ideas site. Or here is one similar idea shared on Power BI Ideas , you can vote it up and add your comments there to improve Power BI on this feature.

In addition, please review the last post in the following thread. Hope they  can bring some inspiration.

Gamma Distribution

Gamma

Best Regards