PowerBI Desktop (and Custom EAS OAuth2 Connector)
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
and an example report PoC
2. Has anyone written a custom connector to handle EAS OAuth2 protocol - before I begin creating my own?
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
and an example report PoC
2. Has anyone written a custom connector to handle EAS OAuth2 protocol - before I begin creating my own?
You do not have the required permissions to view the files attached to this post.
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
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
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
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
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
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
Last edited by closch on 22 Jun 2020, 09:28, edited 1 time in total.
The above Repo now includes PowerBI template https://github.com/essentialproject/ess ... rs/PowerBI
edit: new location
edit: new location
Last edited by closch on 22 Jun 2020, 09:29, edited 1 time in total.
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.
This work has a new home https://github.com/essentialproject/ess ... tributions
The old repo will be retired.
Thanks @NeilWalsh for setting this up
The old repo will be retired.
Thanks @NeilWalsh for setting this up
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
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,JohnWhite wrote: ↑16 Jul 2020, 05:53 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
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.
Not yet, but like you it's something i've thought about.On that note, do you have any principles on when to use PowerBI over custom EA views?
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.
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).
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).
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:
You do not have the required permissions to view the files attached to this post.
Basic GIS View
Calendar visuals
Basic Dashboards
You do not have the required permissions to view the files attached to this post.
Trying out other visuals to show Technical Debt
Another Overview Dashboard
A view of the latest Data Model (bits of Metamodel from EAS that i've brought into PowerBI)
Another Overview Dashboard
A view of the latest Data Model (bits of Metamodel from EAS that i've brought into PowerBI)
You do not have the required permissions to view the files attached to this post.
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"
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"
No problems, look forward to see any suggestions for improvements!
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
You can also bring D3.js visuals in as well (though i prefer the native PowerBI visual plugins).
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
You can also bring D3.js visuals in as well (though i prefer the native PowerBI visual plugins).
You do not have the required permissions to view the files attached to this post.
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.
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
Azure Mapping
with access to various selectable underlying maps - satellite, road, traffic etc.
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
- General Annotation
Azure Mapping
with access to various selectable underlying maps - satellite, road, traffic etc.
- tkinte
- Posts: 146
- Joined: 18 Nov 2014, 08:24
- Location: https://www.linkedin.com/in/tshitshi-kia-ntoni
- Contact:
Hi closh.closch wrote: ↑27 Jul 2020, 17:37 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).
Thank you for Sharing ... Really insightfull.
Best regards,
Tshitshi
-
- Posts: 5
- Joined: 29 Sep 2020, 03:55
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.
You do not have the required permissions to view the files attached to this post.
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.
Hi Mike, so this is now working for me again.mikestainer wrote: ↑06 Oct 2020, 03:23 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
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.
-
- Posts: 5
- Joined: 29 Sep 2020, 03:55
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
Clearly I'm missing some vital step!
Mike
You do not have the required permissions to view the files attached to this post.
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)mikestainer wrote: ↑15 Oct 2020, 23:30 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
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
Code: Select all
C:\Users\%username%\Documents\Microsoft Power BI Desktop\Custom Connectors
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"
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)
-
- Posts: 5
- Joined: 29 Sep 2020, 03:55
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.
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.mikestainer wrote: ↑20 Oct 2020, 05:28 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.
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
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"
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)
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.
You do not have the required permissions to view the files attached to this post.
-
- Posts: 3
- Joined: 02 Sep 2022, 11:19
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
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