Mike Allen Systems

Doing Smarter Things With IT

Analyzing The Edge (Look before you leap)

All computer systems have edges, edges in space and time. Computer Systems have attractive cores, so that they survive, but out at the edges they have less attractive features. This is part of the organic nature of systems they have a start and an end and they occupy space in a business, they also interweave and overlap the business process that they enable. In any system the edge is a key point of failure, the place where assumptions and assertions tend to fail.

Working the edges requires some good problem, cost and risk analysis skills. Think of this as stress, money and time factors. Ideally these problems are addressed at the leading edge, but it is not possible to address all these issues before they occur. We must address edge issues continuously, in an agile fashion; the edge is a place to look for potential issues.

The leading edge changes it’s profile over time along with the system lifecycle. Early edge events are important as they strongly influence system viability, that’s success or failure, but also core future areas like usability. Usability issues typically present as edge issues, such as interfaces to other systems, but in poorly designed systems will present as a core issue. Design is a leading edge issue, too little design leads to usability problems, over design will slow progress and often lead to rework. There is a sweet spot for leading edge issues, you may not know exactly where it is, but you can head towards it by making informed decisions.

The most common method of creating an edge issue is ignorance, mainly of the ‘it’s not important’ variety, making misguided assumptions because it is easier. This leads to assumptions that a problem is simple, therefore it requires no design thinking. Cost benefit and risk analysis are an important part of design and always should be considered.

A high  level design that can be justified by a few simple metrics, not only focuses thought, it aids collaboration. Analyzing the edge quantifies the area of interest with questions like how much, how many and how often? This leads to what does it cost and why do we do this? What are our usability profiles.

Interfaces with other systems are key edges. How is data synchronized, is there a master? Interfaces can fall into disrepair, then they are propped up my human intervention and this becomes a new workflow. People become accustomed to working around edge issues and may sometimes feel that that is how the system works. These issues can be hard to detect and address, but yield large benefits when they are fixed.

This is about getting more value from your software, data and technology, edge cases are a great place to start.

On Upgrading to a New Website

I have a business web site at Mike Allen Systems, I had already done some design and content and was ready to add some more business content and improve the graphics.

In the design process I had gone back to basics and used HTML and CSS, using W3-CSS to give me a simple responsive layout. That was all good: basic, simple and responsive, so I’d ticked all those boxes. One area of technology I’d fudged slightly was the layout, I’d used HTML server side includes for my headers, footers and menu. All this with minimal JavaScript. The problem I now had was how make the web site easily extendable in order provide more business functions.

I have to admit that the moment I started writing HTML and steering away from JavaScript, I had set my direction. I’d just set up a WordPress site for a customer, and while I respect WordPress, I wasn’t going to go there for my own needs. I have some programing skills in C#, so I looked at Razor Pages . This wasn’t a simple choice, I tried various aspects of creating just Razor Pages with a Layout replacing the includes, that was the simple bit. Then I got into creating an ASP .NET Core 2 application, which I discovered gave me what I needed, particularly for form pages.

Converting my pages to a Razor project was simple and tested nicely on the desktop. I used SendGrid on my contact form, that is nice and clean to use. What I hadn’t really considered was how to deploy this fairly new technology. My current web host didn’t support ASP .NET Core 2 on my server. Bummer! I regrouped and deployed to Azure and that was OK. I looked at Custom URL and SSL on Azure and found that put me in a higher price range. I looked for a more suitable host.

Finding a good host has always been one of the trials for web sites. Sometimes and excellent host will be taken over and service will decline, and the opposite can happen and a host can dramatically improve services. I’ve been using Crazy Domains for years for domain registration. Years ago their support for the .NET I needed was limited, but I gave them a call to see if they supported Core 2, and they did. I deployed and was up and running straight away.

This was a fairly simple technology upgrade for a web site, but it still consumed time and effort. My take away is to always consider deployment up front as part of the planning process, in order to avoid obstacles in the path of successful completion. It’s also important to be flexible, so I did write a little JavaScript to power my quote engine.

In summary, don’t forget to plan, but don’t expect activities to progress as planned, there are a lot of components involved in a web site upgrade, so allow time for discovery of issues and remediation. I also found the upgrade led me to consider other issues, such as how I manage images, keywords and content, that is for future projects.

Improved Xero Invoice extraction using Linx

Having successfully extracted Xero invoices using Linx, I decided to drill deeper an improve my understanding and methods.

My aim is still to extract the invoices and load them into Power BI, but now I have the additional target of loading the invoices into CosmosDB and then loading the CosmosDB collection into PowerBI, and eventual Azure Analysis Services.

I improved my understanding of Invoice Type, realizing that the data was both Accounts Receivable and Payable.

Here’s a sample of the data:

         "InvoiceType": "ACCPAY",
         "ContactName":"Melrose Parking",
         "Partition": "201709"

Key takeaways:

    1. The ‘id’ is critical and must be named that to map to the id in the Cosmosdb
    2. That Url, while null in the example, is the url of the Invoice PDF
    3. I’ve added a ‘Partition’ field for CosmosDB and Analysis services use.
    4. I’ve trimmed the time off InvoiceDate and DueDate
    5. All the JSON formatting is done in Linx

Here’s a pic of the Linx project:


That InvoiceItem is a type I’ve defined with the format for the JSON

My thinking is that as the invoice status changes I can update CosmosDB using the id, the CosmosDB API has an ‘Upsert’ functionality which will do this neatly. The intention being to extract Xero Invoices that have changed in the last day on a daily basis using Linx and then upsert them to the CosmosDB and then refresh the Power BI/ Analysis Services Model.

I’m using a very simple C# program to do the upsert, this is all dynamic, with very little code.

foreach(dynamic invoice in invoices)
                await this.client.UpsertDocumentAsync(UriFactory.CreateDocumentCollectionUri(databaseName, collectionName), invoice,options);

This code doesn’t care what the invoice looks like, the CosmosDB has no schema, but I insert into a collection called ‘Invoices’ with a consistent layout.

Also it is still possible to load the JSON directly to PowerBI, the query is very simple:

     Source = Json.Document(File.Contents("C:\Users\Mike\Documents\LinxOutput\invoice.json")),
     #"InvoiceTable" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
     #"Expanded Column1" = Table.ExpandRecordColumn(InvoiceTable, "Column1", {"InvoiceDate", "InvoiceNumber", "Total", "Status", "id", "SubTotal", "TotalTax", "TotalDiscount", "DueDate", "ContactId", "ContactName", "UpdatedDate", "Reference", "AmountDue", "AmountPaid", "InvoiceType", "Url"}, {"InvoiceDate", "InvoiceNumber", "Total", "Status", "id", "SubTotal", "TotalTax", "TotalDiscount", "DueDate", "ContactId", "ContactName", "UpdatedDate", "Reference", "AmountDue", "AmountPaid", "InvoiceType", "Url"}),
     #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"InvoiceDate", type date}, {"DueDate", type datetime}})
     #"Changed Type"

If you’ve got this far, thank you for your interest, there’ll be more on Xero, Linx, CosmosDB and PowerBI in the near future.

Application Authenticate using public/private key pairs

Recently I’ve been using no-code and low-code solution to build small business applications. I’ve had to learn more about authentication using public/private key pairs, which I’ve normally had other people look after for me, but now it’s time for me to do it myself. After a read of the Wikipedia article https://en.wikipedia.org/wiki/X.509

First up the business scenarios. My web site uses https://letsencrypt.org/ certificates, a fantastic free service, it’s built into my web portal so I just nee to know it’s there, simple. Then there was the e-commerce site where I had to take a commercial certificate and install it on an Azure web site, a little bit of manual prep, bit largely automated by the portal.

Then something more complex, I wanted to use an Azure function to talk to SharePoint online. I checked out this post https://blogs.msdn.microsoft.com/richard_dizeregas_blog/2015/05/03/performing-app-only-operations-on-sharepoint-online-through-azure-ad/ , which told me almost everything that I needed to know. One important lesson was that the AAD app acts as a portal to SharePoint online, so once I had one app working, all my azure functions then used the same AAD app for access to SharePoint online. This is still a work in progress for me, I was motivated by a need to insert images from a Power App, then I realized that this opened the door to other business needs, such a creating a PDF from a SharePoint item.

Then I got back to my core business driver, Business Intelligence, and my current area of interest, Xero. I was looking for drag and drop access to Xero data, with a simple connector. At this point I looked at the new https://linx.software/ Xero connector, and was a little put off that I needed a Consumer Key, and a secret to use the Xero API. All too hard I thought! Then I looked close and realized if all used a certificate, I read this https://developer.xero.com/documentation/api-guides/create-publicprivate-key and then created a new cert. The I created a private Xero App with my certificate and it all fell into place.

I’m not an expert on certificates  by any measure, but now I know enough to integrate my Xero with Power BI using Linx, that will be my next adventure.

Get Invoices from Xero to Power BI using Linx

I have a side project exploring low-cost and low-code ways of interfacing with Xero. I’ve extracted data using Linx from https://linx.software/ , a useful low-cost software solution.

You need to create a private Xero App at https://developer.xero.com/documentation/auth-and-limits/private-applications , this is actually fairly simple. Generating the certificate is the most difficult bit, see my blog https://mikeallen.com.au/post/Application-Authenticate-using-publicprivate-key-pairs.

Once that is done set up a Linx job with two steps:

1/ GetInvoices


It’s all pretty self-explanatory and it will generate a nice json file for you.

Import the json into Power BI using Power Query, the Date formats are a bit tricky because there’s a time zone in there. I’ve fixed the ‘Date’ column to make it just a Date.

Here’s the query:

     Source = Json.Document(File.Contents("C:\Users\Mike\Documents\LinxOutput\LinxInvoices.json")),
     #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
     #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Type", "Contact", "Date", "DueDate", "Status", "LineAmountTypes", "LineItems", "SubTotal", "TotalTax", "Total", "TotalDiscount", "UpdatedDateUTC", "CurrencyCode", "CurrencyRate", "InvoiceID", "InvoiceNumber", "Reference", "BrandingThemeID", "Url", "SentToContact", "ExpectedPaymentDate", "PlannedPaymentDate", "HasAttachments", "Payments", "CreditNotes", "Prepayments", "Overpayments", "AmountDue", "AmountPaid", "FullyPaidOnDate", "AmountCredited"}, {"Type", "Contact", "Date", "DueDate", "Status", "LineAmountTypes", "LineItems", "SubTotal", "TotalTax", "Total", "TotalDiscount", "UpdatedDateUTC", "CurrencyCode", "CurrencyRate", "InvoiceID", "InvoiceNumber", "Reference", "BrandingThemeID", "Url", "SentToContact", "ExpectedPaymentDate", "PlannedPaymentDate", "HasAttachments", "Payments", "CreditNotes", "Prepayments", "Overpayments", "AmountDue", "AmountPaid", "FullyPaidOnDate", "AmountCredited"}),
     #"Expanded Contact" = Table.ExpandRecordColumn(#"Expanded Column1", "Contact", {"ContactID", "ContactNumber", "AccountNumber", "ContactStatus", "Name", "FirstName", "LastName", "EmailAddress", "SkypeUserName", "ContactPersons", "BankAccountDetails", "TaxNumber", "AccountsReceivableTaxType", "AccountsPayableTaxType", "Addresses", "Phones", "IsSupplier", "IsCustomer", "DefaultCurrency", "UpdatedDateUTC", "XeroNetworkKey", "SalesDefaultAccountCode", "PurchasesDefaultAccountCode", "SalesTrackingCategories", "PurchasesTrackingCategories", "PaymentTerms", "ContactGroups", "Website", "BrandingTheme", "BatchPayments", "Discount", "Balances", "HasAttachments"}, {"ContactID", "ContactNumber", "AccountNumber", "ContactStatus", "Name", "FirstName", "LastName", "EmailAddress", "SkypeUserName", "ContactPersons", "BankAccountDetails", "TaxNumber", "AccountsReceivableTaxType", "AccountsPayableTaxType", "Addresses", "Phones", "IsSupplier", "IsCustomer", "DefaultCurrency", "UpdatedDateUTC.1", "XeroNetworkKey", "SalesDefaultAccountCode", "PurchasesDefaultAccountCode", "SalesTrackingCategories", "PurchasesTrackingCategories", "PaymentTerms", "ContactGroups", "Website", "BrandingTheme", "BatchPayments", "Discount", "Balances", "HasAttachments.1"}),
     #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Contact",{{"Date", type datetimezone}}),
     #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"ContactID", "ContactNumber", "AccountNumber", "ContactStatus", "FirstName", "LastName", "EmailAddress", "SkypeUserName", "ContactPersons", "BankAccountDetails", "TaxNumber", "AccountsReceivableTaxType", "AccountsPayableTaxType", "Addresses", "Phones", "IsSupplier", "IsCustomer", "DefaultCurrency", "UpdatedDateUTC.1", "XeroNetworkKey", "SalesDefaultAccountCode", "PurchasesDefaultAccountCode", "SalesTrackingCategories", "PurchasesTrackingCategories", "PaymentTerms", "ContactGroups", "Website", "BrandingTheme", "BatchPayments", "Discount", "Balances", "HasAttachments.1", "Status", "LineAmountTypes", "LineItems", "TotalDiscount", "CurrencyCode", "CurrencyRate", "InvoiceID", "BrandingThemeID", "Url", "SentToContact", "ExpectedPaymentDate", "PlannedPaymentDate", "HasAttachments", "Payments", "CreditNotes", "Prepayments", "Overpayments"}),
     #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"DueDate", type datetimezone}, {"UpdatedDateUTC", type datetimezone}, {"FullyPaidOnDate", type datetimezone}}),
     #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Type"},INV_CR,{"Type"},"INV_CR",JoinKind.LeftOuter),
     #"Expanded INV_CR" = Table.ExpandTableColumn(#"Merged Queries", "INV_CR", {"INV_CR"}, {"INV_CR.1"}),
     #"Removed Columns1" = Table.RemoveColumns(#"Expanded INV_CR",{"Type"}),
     #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"INV_CR.1", "INV_CR"}})
     #"Renamed Columns"

I added a INV_CR table to convert Type 0 to CR and 1 to INV.

This is a work in progress, my aim will be to load data to a CosmosDB, and get it all into the cloud. Feel free to get n touch if you have questions about this.

That Power Query should work nicely in Excel as well, so there are a few possibilities here

Analysis and the Design of Computer Systems

Good computer systems don’t just spring up of themselves, they require a lot of work from end users, consumers, managers, analysts, designers, documenters,  developers and testers. These are all roles in the development process, it is not meant to be definitive, a single person may have multiple roles, a single role will often have multiple persons. For small systems a few people may occupy most of the roles, there may be a whole class of roles, such as stakeholders, who intersect other roles. In order to disentangle these roles and relationships and create a good computer system we use analysis.

Analysis is sometimes seen as an optional extra, on small projects where there is a temptation to start first and repent at leisure, on large projects it can get lost in a maze of priorities, lists and milestones, then repentance is less leisurely. Analysis starts with some introspection, and a review of where a project is at and where it is going. A good project is linked to a business initiative, but there can be cases where the computer system itself is the trigger for a remediation of replacement project. A useful ingredient is cost benefit analysis and risk assessment. These activities need to be conducted rigorously to ensure outcomes satisfy user expectations and that risks are acknowledged and ameliorated.

Having attended to the basic prerequisites basic prerequisites of cost and risk, the analyst’s attention must turn to the business domain. The analyst, or the team of analysts, will attempt to decompose the business problem into it’s component parts and interact with subject matter experts and stakeholders. One of the interesting things about subject matters experts is that they exist at all levels of an organization and may rarely get together to share their views, so discovery will be multifaceted at all of, and between organizational levels.

At this point many stakeholders will, no doubt,  keen to see lists of requirements, process models and specifications. The Analysts will be trying to internalize a large quantity of information in order to create a broad brush design of a workable solution to the business problem. These goals and aspirations are not necessarily incompatible with each other, but they may not be naturally compatible.

The initial analysis and design process can be thought of as antithesis, synthesis and thesis. The decomposition of the problem domain is the antithesis, the design process is the synthesis and the design is the thesis. Ensuing iterations of the development process repeat this cycle, which can also be viewed as a continuous process, as synthesis continues until the goals are achieved.

Power BI, the new desktop multi-tool

I’ve been creating short videos for Power BI and Excel users for around a year now, publishing one a month to my You Tube channel. I’m starting to get a feel for what people are looking for in Power BI, and I am surprised. My most recent video Power BI - scraping the web is a real hit! This says to me that not only is Power BI a superb business intelligence tool, it’s also handy for data extraction and manipulation. The other real lesson is that observation of how people approach a new app can indicate areas of potential growth.

Offering more cloud solutions

I’ve been ramping up my BI offering, centered around PowerBI. In order to offer my clients a more complete solution I have become a cloud solution provider (CSP), partnered with rhipe.com.au. This enables me to offer a range of Microsoft products including Office 365, Power Bi Pro and Azure services. I’m still adjusting my offering, but, if it’s Microsoft, I can supply it to you at a reasonable price and offer some excellent support.

Focusing on Business Intelligence and Power BI

Much of my work over the last few years has focused on Data,Reporting and ETL, all components of Business Intelligence, BI. Now that Power BI is establishing itself as a BI market leader I find that I can re-focus my business more easily on BI and I intend to promote and market Power BI, and my abilities and experience in Power BI. I have been pursuing this in two areas, I am creating BI how-to videos on YouTube , and I have a new Mike Allen Systems web site to again focus purely on BI. Check out my videos and new web site, but most importantly check out Power BI , it’s a free download!

Microsoft Professional Program for Data Science

I recently completed the new Microsoft Professional Program Certificate in Data Science. I've a background in software and data migration, and I knew how to use the tools. The course was intended to formalise my skills. Well, was I in for a surprise.

I'd already completed the Power BI Course, and first up was Querying Data with SQL, what could possibly go wrong? The courses were through edX and were mainly USD $50, they have all gone up to $100 since then.

The first speed bump was choosing a statistical language, I flipped a coin between R and Python a few times, and the gods of statistics seemed to favour R. There was also a new plug-in for R in Visual Studio, which made life easier. I kind of fell in love with R, anyone who likes to manipulate a whole dataset at a time would be at home with this language. My favourite bit of code just extracts the year from a date column and adds it as a new column on the end, cool! The density graphs are an absolute delight, as are all the graphical functions. There are R packages for everything and a vibrant user community, this is all good. I also found R useful for generating test data.

Next up was learning some real Statistical Thinking with ColumbiaX. I had done my stats training years ago while studying Applied Science. This course cleaned out all the cobwebs and got me enthused, there are more stats courses from ColumbiaX which I may get into in the future. Great stuff to get one Statistically Thinking. They've taken this off the Data Science track now, I can see why, but it needs a suitable replacement.

Then things got a little more technical with the Principles of Machine Learning. I'll never look at a regression graph in the same way. the introduction to Azure Machine Learning was a real epiphany of drag and drop Data Science. This is amazing stuff and right at the leading edge of cloud technology. I've has some exposure to Azure but this was really something else. Although calling an Azure Web Service from Excel did give me a touch of cognitive dissonance. Another great feature I found in Azure was Jupyter Notebook, which is a lovely open source intelligent notebook that can execute code. Wow! Executable notes!

I think every design team could benefit from this, what a way to prototype data transformations.

My decision to do the Developing Intelligent Application was a little off track, but I bought a book on C# to help me. This is part of the Applied Data Science section and there are some more data oriented courses available. Developing a bot was fun, and there was lot in this course. Analysing Twitter Feeds was great and bought in the Azure IoT hub and other Azure elements. This is obviously very popular and you can do all this with Microsoft Flow now :-(. Lot's more technical stuff in this one, I've even made friends with JSON. I did discover that SQL is useful for data analytics, the old is made new again!

The final Capstone project was a return to data analysis and Machine Learning. The hardest bit was writing the Data Analysis report and reviewing other students’ efforts.

This Certification took me over six months to complete and I learned a lot along the way. I paid for all the courses to get certification, however casual auditing of the course is available. The Professional Program offers a good conceptual learning alternative for those not seeking Microsoft technical certification. I suppose the down side is that you are expected to do a lot of technical things, like C#, and get no real certification for it.

I can't speak more highly of edX. Their courses are so well organised, the platform is easy to use and the content is excellent. Microsoft have made a great choice in partnering with them.

Was it all worth it? Absolutely! I'll be back to edX as soon as I've finished my book on Bayesian Analysis :-).