cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Integrating Power BI with COGNOS with added logic

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

3 REPLIES 3
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Kudoed Authors