PowerBI Desktop (and Custom EAS OAuth2 Connector)

[closch]

Thought I'd ask around -

I've a PowerBI Dashboard, currently linking key parts of the MetaModel around to aid with more dynamic creation of reports.

Couple of questions

1. Is anyone else using Power BI and wants to compare notes on what they're using it for - as well as any example reports? Current DataModel inside PowerBI looks like

2020-06-08_17-08-16.png

and an example report PoC

2020-06-08_17-09-57.png

2. Has anyone written a custom connector to handle EAS OAuth2 protocol - before I begin creating my own?

[closch]

Update

So I've written a Custom Connector which works with the OAuth2 (bearer token etc.) - whilst functional, it may not be the most elegant way but as a PoC its fine. Those interested can reach out and i'll share.

Chris

[rwoof]

Hi Chris

We are also very interested in using PowerBI to create reports from the EAS metamodel, but we have only just started looking into it. Your metamodel is helpful and we'd be happy to have a discussion and share ideas. We are also looking at the same areas as you - focused in the Composite Application Provider area, but reporting on things like costs, contracts, stakeholders, business domain/capability and process - to let our global process owners understand what they have in what markets, what it does , how much it costs etc.

Would be good to share ideas, also on connectivity.

Cheers
Rob

[closch]

So i've created a Community source code Repo here https://github.com/essentialproject/ess ... tributions

You can find the Custom Connector Source Code here : https://github.com/essentialproject/ess ... s/PowerBI

I'll post the PowerBI Template up there too a bit later ... (will be in a different folder)

edit : new location

[closch]

The above Repo now includes PowerBI template https://github.com/essentialproject/ess ... rs/PowerBI

edit: new location

[closch]

I've updated Revision of the PowerBI template to

• make it TenantID agnostic (you'll be prompted for tenantID)

• and also make it "user friendly" by hiding and renaming fields/classes to something more understandable by those less familiar with the metamodel.

Feedback welcomed - also, let me know if you download it - it'd be interesting to see how many people are using this

[closch]

This work has a new home https://github.com/essentialproject/ess ... tributions

The old repo will be retired.

Thanks @NeilWalsh for setting this up

[JohnWhite]

Hi Chris,

We're a new user of EA cloud. This will no doubt be extremely useful. My plan is eventually to allow our analytics team to own the design and build of the dashboards so the pain doesn't sit with IT to build new EA viewer views, however, I suspect it will be a mix.

On that note, do you have any principles on when to use PowerBI over custom EA views?

I haven't studied your repo yet, what would be the remaining hurdles to make this more production grade (as much as reporting on architecture meta model can be classed as production or operational - no one will die if it's not highly available etc.)

@EA Staff - any plans for a formal connector?

Thanks for your efforts.
John

[closch]

Hi Chris,

We're a new user of EA cloud. This will no doubt be extremely useful. My plan is eventually to allow our analytics team to own the design and build of the dashboards so the pain doesn't sit with IT to build new EA viewer views, however, I suspect it will be a mix.

On that note, do you have any principles on when to use PowerBI over custom EA views?

I haven't studied your repo yet, what would be the remaining hurdles to make this more production grade (as much as reporting on architecture meta model can be classed as production or operational - no one will die if it's not highly available etc.)

@EA Staff - any plans for a formal connector?

Thanks for your efforts.
John

Hi John,

Indeed you've hit upon one of the big issues presently with Essential , in that the View creation is not "low-code" in fact it's worse than "high-code" (if that's such a thing) as you also need an intimate understanding of the internal ontology/metamodel, and not just XSLT/HTML and any Javascript Libraries you may want to use. And whilst that's improving, the pace at which views in response to variations of a stakeholder question drove me to look to use PowerBI to allow rapid iteration of viewpoints.

On that note, do you have any principles on when to use PowerBI over custom EA views?

Not yet, but like you it's something i've thought about.
Present thinking -
Use PowerBI when
* you want to prototype views, or mash-up multiple "Views" onto a single page. (want TechFit Measures alongside a view of Bus Unit to App Matrix on same page, and costs and contract end dates - 2 mins and it's done) (assuming you've the elements in your PowerBI data-model)
* you have a standard view (e.g. App Capability Map) and want to rapidly switch between indicators of varying dimensions (e.g. RAG status of cost, risk, maturity, in/out of plans )
* someone says, can you change that bar chart to pie chart (1 click and done)

Use EAS native views when.....
- well i'm trying to put my finger on what that is exactly
--- there's definitely something around data access control

It'd be good to get the community thoughts on this however


I'm currently nearing v2.0 of the release, and that'll probably be my "final draft" release to the community in terms of the PoC model. It'll have addressed many of the necessary patterns to adapt the PoC into anyone's use-case. (whilst it may not have the whole meta-model in it - see point 2 below below - it'll have the conceptual/patterns that can be used across the model to be able to copy/paste with minimal effort to enable the right parts of the model.

Current Issues for 'Enterprise Ready':
1. Technically - subject to your own internal policies - there's no real 'fragility issues' with the PowerBI side of things that I've hit. On the API side, one thing to watch for is the 5 minute token refresh on Oauth, this can lead to warnings in the 'Transform Data' window which can simply be worked around by hitting refresh.
My Custom connector is only a PoC and embeds API user credential in the connector - if you've controlled access to data, you'd need separate API users (with appropriate permissions) and therefore separate connectors for each. There are more 'enterprise' appropriate ways of connecting to OAuth2 APIs that don't require credentials to be stored in a PowerBI connector (risk reduction), but i've not looked down that route far.
As I understand it the PowerBI Cloud pricing model does require optimising your data set to avoid unintended costs. (see point 2 below)

2. I now realise that, at least in the PowerBI Desktop version, I'm hitting performance and useability constraints due to number of queries, size of the data-model and qty of relationships (I was a bit overly ambitious). This is not a problem however... as typically you wouldn't want a PowerBI dashboard to be all things to all people, but be specific to stakeholder/use-case, as such you can maintain different dashboard sets (seperate files) for each stakeholder/use-case with the appropriate parts of the model in, and maintain responsive refreshes and small memory footprint. This latter approach is probably more suitable to controlling access to data, with an associated APIUser/custom-connector as appropriate.

3. I've not yet tested my custom connector with the PowerBI Gateway service, so whilst i know i can publish and view data to the Cloud based PowerBI, i've not tested refresh from that point.

[closch]

v2.0 of the PowerBI Dashboard is now released,

Until such time it's merged with Master it can be found under the pull request https://github.com/essentialproject/ess ... ll/3/files

You should find all the necessary PowerBI concepts and ideas to produce what you need
(there's examples of merge query, building relationships to resolve ambiguity, creating custom columns for sorting, hiding and renaming elements to make it more end-user friendly, etc. etc.)

If anyone wants to refine this version, or a parallel version further using more elegant data techniques, i'd certainly be appreciative, as i'm self-taught PowerBI and happy to learn what optimisations can be made.

It'd also be cool if anyone was to upload example dashboards they've made (ideally using the out-of-the-box standard Essential Classes and relationships).

[JohnWhite]

Most appreciated Chris! I won't get to experiment with this just yet, but when I do I'll get a friend in our PowerBI team to take a look and see if there are any suggestions. Thanks for your efforts and sharing with the community.

[jmk]

Hi Chris,

for those of us unable to use powerbi, would it be possible for you to post some screenshots demonstrating the kind of reports you defined through the connector ? Either here or on github ?

Thanks,

Jean-Marie.

[closch]

Hi Chris,

for those of us unable to use powerbi, would it be possible for you to post some screenshots demonstrating the kind of reports you defined through the connector ? Either here or on github ?

Thanks,

Jean-Marie.

Sure, I'll probably get around to posting in GitHub in due course!

Firstly, we should be clear, that it's not the reports I've created that are the real value of this approach (especially since they're specific to questions my stakeholders were asking - and my limited ability to make them look "nice"), it's the speed at which you can create dashboards/reports for perspectives that are now being asked for - use the Q&A function to do it on the fly, or drag, drop a little bit of this and a bit of that, and bang! you have a report! Want a filter by some dimension (time, org unit, capability etc.), drop it in filter box and away you go.

Apologies I've had to redact a fair bit - but should give you an idea of the possibility.

Q&A examples:

EA_1.png

EA_1.1.png

EA_1.2.png

[closch]

Basic GIS View

EA_7.png

Calendar visuals

EA_6.png

Basic Dashboards

EA_5.png

[closch]

Trying out other visuals to show Technical Debt

EA_8.png

Another Overview Dashboard

EA_4.png

A view of the latest Data Model (bits of Metamodel from EAS that i've brought into PowerBI)

EAS_Screenshot_4.png

[closch]

How you bring all these bits and pieces onto a single page/dashboard will depend on the "message" or "story" that page is designed to tell...

Which is where my hope lies, by sharing this proof of concept, we all can build on it and share "this worked for me with my <insert use-case here> discussions"

Most appreciated Chris! I won't get to experiment with this just yet, but when I do I'll get a friend in our PowerBI team to take a look and see if there are any suggestions. Thanks for your efforts and sharing with the community.

No problems, look forward to see any suggestions for improvements!

[closch]

Also, (as I forgot this earlier)

You can bring in Visio visuals, but they are a little more limiting/complicated (depends on sharepoint/visio web services)

In this simple example, visio block elements use data in PowerBI from EAS to colour-code and place text about lifecycle status

EA_9.png

You can also bring D3.js visuals in as well (though i prefer the native PowerBI visual plugins).

[closch]

Hi Chris,

for those of us unable to use powerbi, would it be possible for you to post some screenshots demonstrating the kind of reports you defined through the connector ? Either here or on github ?

Thanks,

Jean-Marie.

I've also included a gif showing the ease with which these can be pulled together in the updated GitHub Readme, and Pull requested - for now you can see it here https://github.com/C-Losch/essential-co ... 0Templates until it's merged with Master.

[jmk]

Chris,

thanks for the pictures.

J.-M.

[closch]

FYI - Latest Power BI update looks like some incredibly useful features that can be taken advantage of

Full update Info here : https://powerbi.microsoft.com/en-us/blo ... e-summary/

Q&A
Field synonyms for Q&A - good when multiple terms are used to mean the same thing.


Story telling

• Using data points

and

• General Annotation

Azure Mapping
with access to various selectable underlying maps - satellite, road, traffic etc.

[tkinte]

Also, (as I forgot this earlier)

You can bring in Visio visuals, but they are a little more limiting/complicated (depends on sharepoint/visio web services)

In this simple example, visio block elements use data in PowerBI from EAS to colour-code and place text about lifecycle status
EA_9.png


You can also bring D3.js visuals in as well (though i prefer the native PowerBI visual plugins).

Hi closh.

Thank you for Sharing ... Really insightfull.

Best regards,

Tshitshi

[mikestainer]

Hi, I'm a new Essential Cloud user and haven't been a developer for many years, so struggling to work through why I can't get this to work. I'm getting a credentials error when running the connector query. I'm assuming the username is the full email address to log-in to the platform, or have I missed something key. Hoping for some assistance as I am very keen to leverage PowerBI for some insights that the base product doesn't do so well.

2020-10-06_14-18-13.png

[closch]

Looks like v6.11 release may have broken the connector (at least it's stopped working for me) - I'll get it working again my end, and then we can troubleshoot if still an issue.

[closch]

Hi, I'm a new Essential Cloud user and haven't been a developer for many years, so struggling to work through why I can't get this to work. I'm getting a credentials error when running the connector query. I'm assuming the username is the full email address to log-in to the platform, or have I missed something key. Hoping for some assistance as I am very keen to leverage PowerBI for some insights that the base product doesn't do so well.

2020-10-06_14-18-13.png

Hi Mike, so this is now working for me again.

1st thing - v6.11 introduced some changes which required API users (at least) to have to change password - which meant logging in with the api users credentials to be able to set a new password - after which i was able to get the usual tokens and query the API. So, now i know it's all still working as it should...

I've reviewed and updated the readme.md on git to hopefully fill in some steps it didn't cover, and asked for it to be published ("pulled") - but here's the raw version in the meantime https://github.com/essentialproject/ess ... 9a2aaf2bbc.

If you could read- through that, and double check against that new set of instructions and see if helps.

If your still stuck, it'd be good if you could list what you've done and how far you've got successfully, and we can troubleshoot from there.

[mikestainer]

Hi, still no joy I'm afraid, I've followed the steps a number of times and within PowerBI first get asked hot to connect, i then go to edit credentials which shows the connector with anonymous, then it tries to connect and I get an error saying it cant connect with the credentials provided. I've tested using the API with my credentials from postman successfully so can be authenticated.

Clearly I'm missing some vital step!

Mike

[closch]

Hi, still no joy I'm afraid, I've followed the steps a number of times and within PowerBI first get asked hot to connect, i then go to edit credentials which shows the connector with anonymous, then it tries to connect and I get an error saying it cant connect with the credentials provided. I've tested using the API with my credentials from postman successfully so can be authenticated.

Clearly I'm missing some vital step!

Mike

Ok, just to make sure I didn't miss anything i'll go through all the steps from scratch here, (i may have missed something in the readme.md but don't think so)

1) Make the necessary edits in the connectors .pq file, before compiling into a .mez in Visual Studio (i.e. replace any xxx with appropriate values for your tenantID/user/pass/api_key)
it's this section in the source-code

Code: Select all

//following variables contain sensitive information and should not be shared
//TODO - look to have a better way of handling credentials - consider Extension.CurrentCredential() function,
api_key = "xxx";   //API Key
vusername = "xxx"; //EAS Cloud username of local account (non-SAML) with appropriate priveleges to repo/data 
vpassword = "xxx"; //Associated password to the above User account
tenantID = "xxx";  //EAS Tenant Name 

1.1 ) Copy the .mez file to the appropriate path

Code: Select all

C:\Users\%username%\Documents\Microsoft Power BI Desktop\Custom Connectors

2) Open Power BI - Click the 'x' in the Top Right corner of the welcome pop-up.
3) Click Transform Data which opens the Power Query Editor window
4) in Power Query Editor - click Manage Parameters and add two new Parameters
* tenantID - with your tenantID
* Production-Repo-ID - with the Repo ID
Easiest place to get these values is from the URL in the 'Capture' page of Essential Cloud
(https://<tenantID>.essentialintelligence.com/app/#/Repo/<Production-Repo-ID>/EditInstance/)
5) Click New Source > Blank Query - then Right click on the Blank Query and click Advanced Editor

Paste in the following example code overwriting anything that's already present.

Code: Select all

let
    class = "Composite_Application_Provider",
    Source = EAS_Connector_v0.1.Contents("https://" & #"tenantID" & ".essentialintelligence.com/api/essential-utility/v2/repositories/" & #"Production-Repo-ID" & "/classes/" & class &  "/instances"),
    instances = Source[instances],
    #"Converted to Table" = Table.FromList(instances, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "externalIds", "className", "name", "description", "slots"}, {"Column1.id", "Column1.externalIds", "Column1.className", "Column1.name", "Column1.description", "Column1.slots"})
in
    #"Expanded Column1"

Click done

If you get an 'Expression.Error: Access to resource is forbidden' appear, click "refresh preview"
6) click Close & Apply
you should have access to Composite_applicaiton_provider class info in the report composer view
7) Any prompts received for new classes such as 'Please Specify how to connect." click the Edit Credentials and then connect (there's nothing to change, just PowerBI seeing a new path and going through the motions)

[mikestainer]

Thanks very much for the help, I can report success with the basic connection!

Loading the PowerBI template generates a very large number of errors which I'm assuming is down to the limited data population of my environment, however, the one specific thing I would like to check on though is that a number of errors appear to relate to my having multiple values against a particular item - for example contained business capabilities against a higher level business capability.

[closch]

Thanks very much for the help, I can report success with the basic connection!

Loading the PowerBI template generates a very large number of errors which I'm assuming is down to the limited data population of my environment, however, the one specific thing I would like to check on though is that a number of errors appear to relate to my having multiple values against a particular item - for example contained business capabilities against a higher level business capability.

Ah yes... this lead to a fair bit of head scratching for me also.

The problem is one of the challenges of is dealing with two styles of data storage (EAS's underlying protege 'graph-like' structure) and trying to transform it into the other Data structure (PowerBi's 'SQL-like' structure).

In short, PowerBI doesn't like Mulitiple values in a field and as you've found EAS doesn't care about this limitation. However an error is not always a critical error (in that PowerBI will continue to work around it) - but you may end up with 'missing' information on the reports.

Code: Select all

However

, the fix that i've found is for the situations I've had to deal with is to create ''relationship' tables (see the many examples in the template)

So as an example between business_capability and business_process

The way this works is
a) create your business_capability table
a) create your business_process table
b) create a relationship table (code below) which keeps (at a minimum) columns for Business_Capabilty.id (EAS's internal ID for the instance) and a column for the values of the slot you're interested in (you can filter different slots in and out, but for simplicity of understanding i'd recommend filtering just on the slot of interest (e.g. realised_by_business_processes).

Code: Select all

let
    class = "Business_Capability",
    Source = EAS_Connector_v0.1.Contents("https://" & #"tenantID" & ".essentialintelligence.com/api/essential-utility/v2/repositories/" & #"Production-Repo-ID" & "/classes/" & class &  "/instances"),
    instances1 = Source[instances],
    #"Converted to Table" = Table.FromList(instances1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "className", "name", "description", "slots"}, {"Business_Capability.id", "Business_Capability.className", "Business_Capability.name", "Business_Capability.description", "Business_Capability.slots"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Column1",{"Business_Capability.name", "Business_Capability.description"}),
    #"Expanded Business_Capability.slots" = Table.ExpandListColumn(#"Removed Columns2", "Business_Capability.slots"),
    #"Expanded Business_Capability.slots1" = Table.ExpandRecordColumn(#"Expanded Business_Capability.slots", "Business_Capability.slots", {"slotName", "slotValue"}, {"Business_Capability.slots.slotName", "Business_Capability.slots.slotValue"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Business_Capability.slots1", each ([Business_Capability.slots.slotName] = "realised_by_business_processes")),
    #"Expanded Business_Capability.slots.slotValue" = Table.ExpandListColumn(#"Filtered Rows", "Business_Capability.slots.slotValue"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Business_Capability.slots.slotValue",{{"Business_Capability.slots.slotValue", "Business_Process.id"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Business_Capability.className"})
in
    #"Removed Columns"
    

c) you "close and apply" to get back to the Report viewer
d) the click Model icon (bottom icon on the left)
e) Create/validate the relationships and create/adjust them as required (I tend to turn on "both" cross-filtering)

PowerBI_model.png

Optional steps

f) - but strongly recommended - delete the columns in tables a) and b) from above that refer to the relationships to avoid confusion.
g) use Merge queries to bring bits of useful data into a single table for useability

I've not tried to create relationships myself where it refers back to the same table (and PowerBI doesn't like that sort of 'loop') so it'll depend on what you're trying to achieve as to a solution that works for you.

Hope this gets you going.

[Arunkumar.Nadar]

Hi closch,

There is way to certify your connector with Microsoft. Do you plan to do it? That would help us to download this connector from Microsoft website as certified connector and use it.

https://learn.microsoft.com/en-us/power ... tification

Power BI (Cloud) version would need a certified connector. Power Bi (Desktop) works with the version you have provided below.

Hi Neil,
Will this be part of the Essential Roadmap in near future?

Thanks
Arunkumar Nadar

[JohnM]

Hi,

It's on our list, but not yet prioritised. I'll raise at our next features meeting but likely to be H123 at the earliest.

Regards

John

[Robbie64]

Hello, just wondering if there have been any developments over the last year on this PowerBI connector topic? Either from EAS or community. Thanks very much.

[JohnM]

Hi Robbie,

Apologies for the delay. We're working to get the integration engine out first, then have this on the stack to look into in detail. We're hoping to get the integration engine out shortly.

John