PowerShells Day 3

This blog post covers what we created and the improvements we made on day 3 of ACDC 2022.

The problem

Electrical bills have gone through the roof this winter, and we are sick and tired of scanning the market for cheaper electricity.

The solution

We created a solution to this problem! The PowerSwapCorp!

When you sign up as a customer you give us power of attorney to swap electricity contracts for your home. We scan the market and swap to whatever electricity provider has the cheapest rates. All we ask for in return is 10% of your total savings each month.

Before becoming a customer

Before you might become a customer you would like to get an overview of how we classify all the electricity providers. In the Power Swap Dashboard, you would see today’s cheapest provider as well as a 30-day overview of the market. The reason behind these dashboards is to give the customer an idea of how well we know our data and the electricity market.

Excellent User Experience:

These dashboards give a unique overview of how the market is today and tell a story to the customer on how we manage the data. The user experience of this dashboard is easy to manage and has an intuitive layout, even for the less educated customer.

Most Extreme Business Value:

The Dashboard let the customer themselves take the decision if our services can save them money or not. The intuitive layout and real-world scenarios (Variable price vs Fixed price) will convince the customer without interaction from our sales team.

Rock Solid Geeknes:

Showcasing where the electricity providers generate the electricity from is a great tool of data and technology for the data geeks and even for the climate activist geeks. Who would really like to buy electricity without knowing where it comes from? 😉

Killer App:

It is not that common for the electricity providers to share their data to the extent of what we have done here. This is clearly the best platform for providing all the data the customers and potential customers deserve. #ShareWhatTheCustomersDeserve

Our website

Visit https://powerswap.powerappsportals.com/

or scan this QR-code:

View our offering and an overview of what you can save by signing up for our service.

Even before you sign up to become a customer you get insights into embedded Power BI reports that show details on how the electricity market is doing.

Excellent User Experience: By having a consistent visual identity across apps and the website as a hub for user interaction we create a strong brand. The language and visuals should annotate a young, tech startup that wants to disrupt its industry.
Most Extreme Business Value: The website will provide business and win customers over by providing information on how much money they could save by using our services. The customer can log in, register support tickets, and communicate with the back office team.
Rock Solid Geeknes: The embedded Powerbi reports combine data from different sources and provide intelligent insight into the weather conditions impact on different types of electricity production.
Killer App: The portal is accessible and responsive, works on mobile devices, and will (when the preview feature allows) be available as a native app on your phone.

Become a customer

Becoming a new customer is easy. You can click the “Join Us” button on the front page or simply go to “Sign in” in the main menu. You can register by choosing the authentication provider you prefer: Microsoft, Google, or Azure AD for internal users.

Excellent User Experience: You can choose an authentication provider and connect website login to a familiar service. This provides a smooth user experience as you don’t have to remember a new set of usernames and password combinations.
Most Extreme Business Value: By letting new customers register through self-service we create more business than if it was only salespeople doing the signing of contracts.
Rock Solid Geeknes: Automatic self-registration and multiple identity providers are kinda geeky.
Killer App: Its killer cool that we use other authentication providers – we could have just left it with username and password..

Terms and Conditions

When you sign up the first time you’ll need to sign our terms and condition.

You need to checkbox before the button is enabled and you can submit the form. This is our way of getting your consent to switch contracts for you. When the terms and conditions are signed a flow automatically creates a document and uploads it to Azure Blob Storage.

This is a screenshot of our flow that stores power of attorney documents into blob.

Excellent User Experience: The button for signing the Terms and conditions will only be enabled when the checkbox is checked.
Most Extreme Business Value: When customers sign terms and conditions they give us legal rights to do business on their behalf – that’s trust – and trust is money… (or was that time is money..?)
Rock Solid Geeknes: Automatic creation and upload of documents using Flow are geeky.
Killer App: We are creating a solid app ready to go with all these security and legal steps taken care of.

Customer Application

When you are logged in you will be sent to the customer application form. This is where you enter the details we need to create a customer contract. The applications are sent for approval to the back office.

In the application form, you are asked to enter your contact information and information about your current electricity provider.

Excellent User Experience: We combine the form for entering your contact information with the application form to make sure we are limiting the number of forms and steps to become a customer.
Most Extreme Business Value: Users can sign up to the portal without sending customer applications. This will allow us to follow up on contacts that are interested in our product.
Rock Solid Geeknes: This application step allows us to have automatic flows that follow up contacts without applications, as well as those that are already customers.
Killer App: This application process is tying nicely with the rest of the app.

If you are unsure about your current square meters, you can use our mobile app that allows you to measure the space around you and it will calculate the amount of power it consumes.

Power Consumption Calculator

We created an app for customers that are unsure about the size of their homes. Now they could open our app, use the camera to pin a couple points to the floor in augmented reality, and get square meters calculated. You also get yearly consumption based on square meters.

Excellent User Experience: A super easy app that does exactly what it made for
Most Extreme Business Value: For the user, knowing the consumption is key in order to predict how costly the energy bill we are. For us, additional apps and tools are a marketing tools to brand ourselves.
Rock Solid Geeknes: Augmented reality! Just playing in the app measuring all and everything is just fun!
Killer App: Combining excellent user experience, with business value and geeky AR/MR is the definition of a killer app

Help Desk

Once you are a customer you will have access to knowledge articles, forums, and the help desk. This is where you can raise support tickets and get help from our back-office support team. From the main menu open “Support” and choose “Help Desk”.

This takes you to an overview of your support tickets where you can see your active and closed cases. You can also create new cases by clicking “Create a new case” at the top of the list, or using the “Create case” button that you find in the “Help” section on most pages in the website.

When you create a case you’ll automatically get suggestions to possible answers in the knowledge base articles and other information pages on our site – based on the words you type in the title field. Further, the name of your house and your name is automatically related to the case. You simply select the type and subject and add your description and send the case to us.

Excellent User Experience: The experience of creating a case and being presented with possible solutions before the case is created provides a good user experience – and takes some load off the support team.
Most Extreme Business Value: By solving some of the problems users are having before they create support tickets the support team can focus more on the big problems.
Rock Solid Geeknes: It’s pretty geeky to predict the answer to a problem in a support ticket before it’s even submitted!
Killer App: This has the taste of AI and has predictive qualities that make for a great killer app..

Sales Person App

Our salespeople are on the go and need business apps on the phone. They have a Canvas App that allows them to sign new customers on the fly and approve or reject applications that come in through the web. The website has open registration, and that means that there will be some unserious applications. Because we are signing legal contracts with our customers that allow us to make deals on their behalf, we need to make sure that the applicants are real people – and we like to do that manually.

When creating interaction designs it’s good to look for established design patterns that exist and that users are already used to. For example Tinder’s Swipe Gesture. We wanted to use swiping as a way of approving or denying new applicants.

You can see the loading screen that engages the user right away while data loads into the application. At the start screen, you can open the “Approve or deny” screen, or a form for creating new customers.

The “Approve or deny” first shows the list of new agreements to give you a sense of the workload. Then when you open an agreement you can swipe right to approve – get to a success screen – or swipe left to deny – and get to a “successfully denied” page.

You can also create new contacts from the list page. When a seller is on the go it’s important that they easily can sign new contracts.

Excellent User Experience: The usability of this swiping motion is great, natural, and fun. It’s easy on the hand and feels good to the end-user.
Most Extreme Business Value: By allowing salespeople to approve applications on the go we shorten the time from contact submitting an application until it’s approved.
Rock Solid Geeknes: We simply love sliding motions. That’s why we spend all that time on Tinder, you know…
Killer App: Simply having your work tool in your pocket is great for a salesperson. They get their work done on the go and can spend more time closing sales.

Seller Teams

After the registration the customer is created in DataVerse as a contact, using a Cloud Flow we create an approval based on the new contact record that is created. Since this is a power of attorney product we have to approve these manually as of now. This is something we will continue working on with our lawyers.
When the approval is created a message is posted in a team’s channel for all associates in PowerSwap Corp. When the approval is completed and approved, it posts a new message that the contact is now approved.
Once contact is approved, we will automatically assign them the best current deal and their subscription start running.

Excellent User Experience
By using approval flows integrated with Microsoft Teams, we continue using the same interface as the associates are used to.
Most Extreme Business Value
Using a Cloud Flow to create the approval and then post it to teams for associates makes it easy to handle for employees. When our manually approve process could be replaced with an automatic solution, we could just extend this Cloud flow with auto-approve.
Rock Solid Geeknes
Using Power Automate Cloud Flow to run the entire process from when a contact is created, going through an approval flow which then is approved using Microsoft Teams is in our opinion use of the best tools and technologies.
Killer App
By using Cloud Flow with approval integrated with teams we believe this will make the work tools of our employees more consistent and reduce the number of tools they have to involve in order to achieve their tasks.

A 360-degree view of the customer

In the Power Swap Business Application, we have a complete 360-degree overview of the customers with all the customer information, preferences, emails, phone calls, notes, current contract, contract swaps, and so on.

This is an application on top of dataverse where all the current electricity providers and offers are stored. And all the information needed to calculate the cheapest provider and offer for the customers depending on their preferences.

Business value

With a 360-degree overview of the customer we can offer:

  • Better customer satisfaction with excellent customer service.
  • Personalized marketing activities to decrease churn rate.
  • Sales have all the information they to potentially sell other services to the existing customer

Excellent user experience

The user experience is familiar and follows the best practices for form design by MS. Combining the structure and usability of the model-driven app with the tailored experience of the canvas application. Both apps are responsive and can be used on the desired device.

Killer App

This is an application that connects everything, calculating the best possible offer that fits the customer’s preferences and swapping the old agreement with the new one on an hourly basis.

Rock Solid Geekiness

  • We used fiddler to decrypt https traffic, and revers engineered an existing service. 🥷
  • We took that information and created our own custom connector. 🔌
  • To not spend time on creating entities and attributes, we used dataflows to auto create our data model from the payload of the service 😍

Individual customer dashboard

Every customer needs to have an overview of their current situation. In this dashboard “Power Swap individual” the customer has access to their current provers, spot price, electricity consumption, etc.

Excellent User Experience:

This is another appealing dashboard that provides just the right amount of information to the customers. This is a dynamic dashboard that will update the customers with the latest information.

Most Extreme Business Value:

This has a great business value as the customer has one intuitive dashboard to look up its personal data. We believe we have managed to showcase the data in a way that it is easy for the customers to both use it as a personal dashboard, and also share how cool their electricity data is to other friends. They even have data on when “Power Swap” is predicted to switch providers based on discount days left.

Rock Solid Geeknes and Killer App:

To be able to look at when Power Swap is predicted to “swap” provider is a cool tech every geek would appreciate. To get all this data in one dashboard is unique and cool.

More data, please! More data please! Historic Clima and Oil/Energy price

Well.. what can we say? We just love data and correlations.
Below we have three pages from our “Power Swap Analysis” dashboard. In these pages, we have collected historic data both from the climate sector and oil/energy to find correlations between the different sectors and the electricity Spot Price.

Excellent User Experience:

These three pages offer a minimalistic design with a sexy look to it, what really is sexier than well-presented data?
Throughout the dashboard we have little to no manual interactions, the graphs are already optimized and well explained with text. We have focused on highlighting the data which is most relevant and outstanding, we believe all this data will educate the people who wonder why/what is the cause of the recent spike in the electricity price.

Most Extreme Business Value:

All though we are not directly an electricity provider we have all the data available because we stand in the middle of all the transactions. By providing this dashboard to our customers we give them yet another reason to become customers and stay customers for a long period of time. This dashboard will educate our customers, and well-educated customers will be long-term customers because of their understanding of how the market implication works.

This dashboard is something we would like to sell to our electricity providers. By selling them this information we can influence them to switch the source they buy the electricity from because in the dashboards we showcase where the correlations are positive and negative.
This will be a win-win situation because if the providers manage to buy electricity from the spot market cheaper, we will also be able to give our customers more providers with cheap and hopefully renewable electricity.

Rock Solid Geeknes:
By collection historical data and making cool graphs will please every geek’s heart. And by collecting this historical data we have also managed to provide even more value to our customers, and at the same time, we have created another income source from the electricity providers. What is better than using geekiness to earn some money?

Killer app:

It is not that common for the electricity providers to share historical market data to the extent of what we have done here. This is clearly the best platform for providing all the data to the customers and electricity providers.

Current data analysis:

Quality assurance of deals

Since we are getting deals from a 3. party provider, we need to ensure that the deals are the best and not least updated. That’s why we have created a crawler that runs scheduled every day to search through search engines with our contract names.

So based on our contract names, we are doing a search on bing each day, to see for possible better deals. We search based on the data we are capturing on www.elskling.no and then use this data to search on bing daily for updates on these contracts.

Rock Solid Geeknes:
Using Web Search API in Azure we could call this API to run search engines with our query as much as we want. We combined this API with a cloud flow. Using data we have already captured earlier (contracts from www.elskling.no) and stored in dataverse, we could run queries through bing with each of these deals.

Full solution from PowerShredders of Axdata

We came into this Hackaton with the idea that we could make the Onboarding process a lot less manual, a lot more secure and reduce the risk of human errors in the process significantly. And we’ll be honest, we are extreamly happy with the solution we now are traveling home with! The best thing you ask? It works like a sharm, every bit of it! We have tested it several times, and everything flows exactly as described!
You can see the full solution under here, but we’ll start with a summary of why we think we should get a lot of points in the four different main categories:

Excellent User Experience:

Lets start with the help this will give to the employees already working in the company:
– HR now will have a veary streamlined process for Onboarding, where all tasks that can (and should be) automated are just that. They don’ need to spend time sending out agreements, follow up with signature, enter a lot of information regarding the new employee in the system, follow up on other employees that forgets the tasks they have in an Onboarding. They don’t need to notify IT about a new employee coming it and wait for creation of user and access to systems. All of this happens automatically. Nothing is forgotten, no sensitive information is sent in emails or seen by someone who shouldn’t see it.
– IT never needs to think about a new employee coming in anymore. Everything is automated and just happens. Isn’t that the best user experience? When you actually don’t have to do even one click, and the process still works?
– Other employees in the company having tasks regarding a new employee coming in will be reminded of their tasks and make sure nothing is forgotten. Automated and nice. And, if they complete their task, no notifcation will be sent, it really is as easy as it sounds

And then, to the candidate starting. If the company implements this solution, everything will be ready for the new employee when he has his firsgt day of work. He can even get information about the company, his team, his manager and more in the Onboarding Portal before he starts, so we can keep up the good energy people feel when they are about to start working at a new company. The new employee will also feel that this company really takes care of their employees, and that they really are up to date in the digital world we’re living in.

Most Extreme Business Value:

The value for companies here are so high, that it’s almost difficult to know where to start. But, first of all, this saves a lot of time for the HR department. And, we really mean A LOT of time. Not just that everything is automated so they don’t need to do as much as they have to now to register a new employee, but they don’t have to push and follow up everyone else that hasn’t done their part and they don’t have to correct human errors that’s been done during the process. They can spend their time on something that is much more valuable to the company, and that is to make sure that all the employees already working here gets the best environment possible to do their job as best as they can! Lets face it, this is what we want the HR department to do, we don’t want them to spend time on entering data into a system.
The IT department will also save a lot of time witht this solution, that they can spend on other things as well. Not that I work in our IT department, but I assume that creating users and assigning licenses and access aren’t the most fun task they do at work. So I would think this will actually make their workday more fun!
Let’s just do an approxemently calculation of time saved. Create a contract for the new employee, upload it for e-signature, send it for signing, recieve it, ask the employee for more information so that it can manually be put into the system. Let’s say this totally takes 5 hours at best. Then someone needs to tell IT to create user and access and make sure that it have been done, a total of 1 hours. Then someone needs to follow up other employees, to make sure everything is done and ready for the new employee. This is maybe the most time consuxming, and I don’t think it’s wrong to say that this takes at best 8 hours in total during the time between someone signs and their first day of work. This means that time saved for just this one onboarding process is at least 14 hours. One of our customers typically hires between 10 and 20 employees every month, and this means that this solution will save them between 140 and 280 hours. Each month! To have an other example, an other customer of ours plans to hire 1.500 employees by the end of 2023… You do the math here!

Rock Solid Geeknes:

There are so many partws in this solution that works perfectly together, we almost can’t believe that we actually made this work as good as it does now. We seriously din’t think that we would be able to automate that many parts of the process as we have done now.

Killer App:

We have created this as a total solution, so that everything can be uploaded and used by all companies using Dynamics FO og Dynamics HR. With only a little personalization to make the wording, logo etc. fit the different companies, this can be set up quite fast. And, of course, each part can be implemented by it’s self too, if someone don’t want the whole solution. But, we know that this will be an easy solution to sell to our customers, as we know that a lot of them already really wants this!

The solution:

And now, finally, let’s go to the solution. You have been through a recruitment process in your company, and you have picked a really awesome candidate you want to send an offer to. This is what you do:

1: Create the candidate in Dynamics, and fill name, startdate, email address and phone number, and connect him to the position:

Like magic, the candidate resieves an email to view, update and sign the agreement. The Agreement is automatically filled with data from Dynamics before it’s sent out for signing, done through an API we created with OneFlow right here at this Hackaton:

The candidagte fills in the National ID number, and signs the Agreement using Bank-ID:

3: Agreement is signed by the company as well:

4: You Hire the candidate in Dynamics with two clicks, and assign the onboarding checklist that should be used for this canidate, and ticks the checkbox to create a user in Azure AD for the new employee:

This triggers a PowerAutomate that first create a work email for the new emplyee, sets this new email as the primary email address on the Employee workspace in Dynamics and creates the Azure AD user for the new employee and assigns group and licenses to it, and ends with sending the login detail to the employee with SMS, together with the link to the Onboardig Portal:

And, of course we have created a PowerAutomate that will remind the employees in your company about the tasks they are assigned regarding the onboarding of the new employee if they don’t follow it up by themself:

Now, the employee can log in to the Onboarding Portal (that works on all devices), and enter in more information about himself. When submitted, the data is automatically updated directly in Dataverse, and then becomes visiable in Dynamics within seconds:

And, last, but absolutely not least, the night before the new employee has his firs day of work, a PowerAutomate runs and imports the user to Dynamics, connects the user to the correct employee in Dynamics, and assigns the security role Employee to the user so that the new employee has accsess to the Employee Self-Service workspace in Dynamics:

And, to end it all for now, we have automated the process for email signature, so that is is automatically put on every one of the emails you send out. Now they finally will lokk the same for the whole company:

The signature itself is created with HTML-code, after a rule is created in Exchange

Serverless SQL and Synapse love

After taken all the data from Dynamics into Synapse Middle-Aged Mutable Ninja Tuples wanted to be able to create views and use all data gathered in Synapse.

As a first step doing this we setup a serverless SQL.

This serverless SQL has no storage and only contain of the possibilities to g.ex create Stored Procedures and Views. This will able us to cross data from many sources and get a more rich way to doing analytics without duplicating data.

First thing first: Creating a External Data source for Serverless.

Then we need to create a fileformat

(This is not the final file format we ended up with – remember to update when working)

Next create all the external table(s) we need

!Note we only get the data we need from the CSV file, there are so many fields not needed in the source so let’s get those out of the way..

This would be the “code” way. But taking the time into consideration.. We used the GUI way:

After creating all the external tables we can now create a view in serverless SQL using normal T-SQL

(Please don’t mind the “dirty case”. We tried to fix this in several ways. But could not find a solution)

Now we can use this View in Power BI to get Clean data from Synapse. We can even cross data from several sources without duplicating data.

Less work for those using Power BI, and more control of the data back-end. A win-win situation.

If this turtle had not been sick, he would dived further on, but as of to day, he has to go back to bed again..

Plans (if not sick)

  • Getting data from outside Dynamics
    • Weather info
    • Traffic info
  • Taken those data into Power BI.
    • Analyze of SOS alert VS weather and time used to solve SOS alert
    • Analyze of time used in traffic – optimalization of resource scheduling
    • ++

Concerns: Synapse VS Data Export Service and it's deprecation After looking at the synapse and comparing it with the previous Data Export Service. (Deprecated) I look forward to the future. There are an incredible number of new possibilities for synapse versus Data export Service. However, there is one thing that worries me a little. How to translate Option set values to Display names in Synapse. This is a known issue from the past g.ex power BI. The data export service had a separate function that created an optionssetmetadata table where you could join together and get the different display names for each language on option sets values. As it is today, from the synapse you only get access to the option values ​​and no longer have this support table to lean on. Ref: https://powerapps.microsoft.com/en-us/blog/do-more-with-data-from-data-export-service-to-azure-synapse-link-for-dataverse/

Download Brreg Data

To get more data for testing we did setup a new soruce in Azure Data Factory to download “Account Name” and “Orgnummer” from Brreg.

The result downloading first 1000 from Brreg..

We made it simple.. using this URL

https://data.brreg.no/enhetsregisteret/api/enheter?size=1000&page=1

https://data.brreg.no/enhetsregisteret/api/enheter?size=1000&page=4

With an param to ADF to say how many pages we should download.. Simply changing the number of pages to download.. Meaning batches of 1000 accounts.. So, g.ex: We set

ADF will loop until 5 pages in URL have been set in API URL and downloaded to source

g:ex:

run 1: https://data.brreg.no/enhetsregisteret/api/enheter?size=1000&page=1

run 2: https://data.brreg.no/enhetsregisteret/api/enheter?size=1000&page=2

run 3: https://data.brreg.no/enhetsregisteret/api/enheter?size=1000&page=x

For each loop we download these data and dump them into TEST SQL DB

Example now:

MOIST MUNCH

Ved hvert maleri er det satt opp IoT enheter for måling av fuktighet og temperatur. Dette er for å overvåke at målingene ikke går over terskelverdier som kan skade de uvurderlige maleriene. Etter at dataen har blitt hentet fra IoT-enhetene så må de visualiseres slik at ansatte kan bruke dataen for og enkelt ha oversikt over hvordan forholdene til maleriene er.

Når de åpner Power BI dashbordet så møter de denne skjermen her:

Fargene for dashbordet er valgt etter museets fargeprofil: rød, hvit og svart, og følger dermed også fargeprofilen som er satt for resten av den totale løsningen med besøkende-delen.

Helt på toppen får de gjennomsnittsmålingene fra alle sensorene gjennom hele museet ved de tidspunktene målingene ble gjort, med et minutts oppløsning.

På bunnen får vi opp temperatur- og fuktighetsmålinger for de to rommene som det er satt opp sensorer for: “Solrommet” og “Månerommet”. Der har man mulighet til å klikke inn på rommene for å få mer detaljerte data om målingene for hvert maleri. Trykker man for eksempel inn på solrommet får man opp:

Der man kan se på de målingene som er gjort for sensorene de siste timene. Pilen på logoen tar deg til forsiden og gir deg også mulighet til å klikke deg inn på data for det andre rommet:

Som viser en lignende side som Solrommet, men med sensordata fra Månerommet.

Integration with team can really be simple

So we used the tool App Studio to implement the “report incidents” React app with teams!
Now you can install the app in Teams and report incidents straight from the teams app. Now the audience can easily report incidents without having to navigate to the website (or install it as a pwa, by all means). Would be awesome to actually have the possibility to officially release it, but given that MS uses about a month to approve the app, this was the best option to move forward!

API

We are using many different endpoints to get data from different sources. Some of them by using the sdks and some of them clean http requests:

-Azure Signal R (Hub):

-Azure Web Application:

https://app-acdc2022.azurewebsites.net/api/telemetry/GetTelemetriesWithTemperature

-Azure Maps Geolocation:

https://atlas.microsoft.com/mapData/{location}/status?api-version=1.0&subscription-key={subscription-key}

-IoT Central:

Internal service from IoT Central to LogicApp and Flow.

-Metamask:

The extension injects the Ethereum web3 API into every website’s javascript context, so that dapps can read from the blockchain. We use it when loggin in onhttps://smartzooapp.webflow.io/ :