Debugging access to REST data sources in Power BI

Recently I was involved in building a Power BI report connecting to a REST API as the data source. In this blog post I show you what tools made my life easier working with REST services.

The Error

In order to access the REST service we needed to create a token first and pass this to the service in the HTTP header. We followed the advice here to achieve this in Power BI directly. But it didn’t work and we always got the following error at some point: “Specified value has invalid HTTP Header characters”

PBIErrorREST

As far as I observed it, Power BI is pretty intransparent what happens behind the scene. So first of all I wanted to know whether the REST service works as expected.

Testing the REST service outside of Power BI

There are various options make a REST call, I’ll mention two of them.

PowerShell

A low installation footprint approach is using PowerShell. With the following command you can send a REST request and retrieve the result:

Invoke-RestMethod -Uri <URL> -Body (‘{“systemId”: “it-logix”, “apiToken”: “abcd”}’) -Method POST

SoapUI

At least for me a more convenient way is using a non-command-line tool, for example SoapUI.

Using SoapUI I could proof the REST service works as expected. In the second screenshot on the right side I can see the actual data returned from the REST service.

SOAPUI

SOAPUI_Response

But why would Power BI throw an error anyway?

Bringing light into what happens behind the scene of Power BI with Fiddler

Looking at the error message, my first guess was that something is wrong with how we set the request header in Power Query. To bring more light into this I used Fiddler. Fiddler links itself into all HTTP traffic on your computer and makes it visible for you to analyze it.

The Power BI traffic can be found in the microsoft.mashup… process.

Fiddler1

To my surprise I found that Power BI called the REST service successfully including getting the correct data back similar to what I got in SoapUI. But why would Power BI throw an error anyway?

Compare with a generic REST service and get the riddle solved

As always in a debugging process it is helpful to have a second example to check whether an error is generic or specific to one case. For that I used a service called JSON Placeholder. Here the data import to Power BI worked like a charm. Once again using Fiddler my interest was about the response header. I compared the header of the generic service with the one in my project. Can you see it?

JSONResponse1

The HTTP response header of the actual REST service

JSONResponse2

The HTTP response header of the JSON placeholder service

Finally we asked the REST service developer to remove the header line starting with an appostrophe – and suddenly it worked in Power BI 🙂

Summer News 2019

It’s been quiet here. No new blog post since end of 2017. Let me share with you what happend so far and what’s coming next.

One of the reasons I had no time for blogging were my various customer projects. Whereas SAP BusinessObjects was my world till 2017, in 2018 I definitely moved into the Microsoft BI world more and more. I had the pleasure to work with much of the cool stuff around Power BI, SSAS Tabular, SSRS and various Azure services. Besides that I further pushed WhereScape data warehouse automation in our customer projects.

2018 was an intensive year in my company IT-Logix too. I had to jump in as an ad interim COO last summer and run operations for eight months – besides my role as the Chief Knowledge Officer and my regular project work. In April 2019 I finally could handover all my management activites and stepped back from the executive committee after more than ten years. On the other hand I was elected into the board of directors. This new role goes along much better with my continued interest in exciting projects, developing new fields of businesses and sharing knowledge with the community.

Community is key – my concern in 2019 is to invest in the next generation of BI and analytics folks. Therefore I’m investing myself into building up the Swiss branch of TDWI Young Guns. The Young Guns community serves as a networking platform between students, young professionals and more experienced people – all sharing a common interest in data and what you can do with it. After a successful first barcamp back in May there are some promising upcoming events you shoudn’t miss if you’re around Switzerland in September and October. Subscribe to the Young Guns mailing list or join us on LinkedIn to learn more about the details.

I also continued my conceptual work with an updated version of my requirments framework IBIREF including practical guidance of how to write and slice BI specific user stories. In addition, I worked on a new model around different (or better: growing) tshirt sizes for your BI architecture. The basic concept will be published in TDWI’s BI-SPEKTRUM by end of August – you’ll find a copy here too. So stay tuned.

19.07.01. Event MAKE BI ITX (390)

A glimpse at my presentation about T-Shirt Sizes for your BI architecture

Covering the same topic, I had the pleasure to give a keynote together with Ronald Damhof during the MAKE BI confernence beginning of July. I’ll give a similar presentation during BOAK and TDWI Schweiz confernce this autumn, unfortunately without Ronald. In addition, I’ll share some of my (disciplined) agile project experience during DADay 2019, a virtual conference organised by the Disciplined Agile Consortium.

Finally I have many ideas for further blog posts. Bear with me that I’ll find some time to write them down.

Agile BI Building Blocks 2.0

Quite a while ago, I published a blog post about my Agile BI Maturity Model. In this post I’d like to show you the current state of the model.

First of all I renamed the model to “Agile BI Building Blocks”. I don’t like the “maturity” term anymore as it somehow values the way you are doing things. Building blocks are more neutral. I simply want to show what aspects you need to take into consideration to introduce Agile BI in a sustainable way. The following picture shows the current model:

What changed compared to version 1? Let me go through the individual building blocks:

  1. Agile Basics & Mindset. No change – still very important: You need to start with agile basics and the agile mindset. A good starting point is still the Agile Manifesto or the newer Modern Agile.
  2. Envision Cycle & Inception Phase. No change – this about the importance of the Inception Phase especially for BI project. Simply don’t jump straight into development but do some minimal upfront work like setup the infrastructure or create a highlevel release scope and secure funding.
  3. BI specific User Stories. Changed the term from simply User Stories to “BI specific User Stories”. Unfortunately I didn’t manage to write a blog post about this yet, but in my recent workshop materials you’ll find some ideas around it.
  4. No / Relative Estimating. Changed from Relative Estimating (which is mainly about Story Points) to include also No Estimating which is basically about the #NoEstimates movement. I held a recent presentation at TDWI Schweiz 2017 about this topic (in German only for now)
  5. (Self Organizing) Teams. Changed and put the term “Self Organizing” in brackets as this building block is about teams and team roles in general.
  6. Workspace & Co-Location. Added “Workspace” as this building block is not only about co-location (though this is an important aspect of the agile workspace in general)
  7. Agile Contracting. No change, in my recent presentation at TDWI Schweiz 2017 I talked about Agile Contracting including giving an overview of the idea of the “Agiler Festpreis”, more details you can find in the (German) book here.
  8. New: Data Modeling & Metadata Mgt. Not only for AgileBI data modeling tool support and the question around how to deal with metadata is crucial. In combination with Data Warehouse Automation these elements become even more important in the context of AgileBI.
  9. New: Data Warehouse Automation. The more I work with Data Warehouse Automation tools like WhereScape, the more I wonder how we could work previously without it. These kind of tools are an important building block on your journey of becoming a more agile BI environment. You can get a glimpse at these tools in my recent TDWI / BI-Spektrum article (again, in German only unfortunately)
  10. Version Control. No change here – still a pity that version control and integration into common tools like Git are not standard in the BI world.
  11. Test Automation. No change here, a very important aspect. Glad to see finally some DWH specific tools emerging like BiGeval.
  12. Lean & Fast processes. No change here – this block refers to introducing an iterative-incremental process. There are various kinds of process frameworks available. I personally favor Disciplined Agile providing you with a goal-centric approach and a choice of different delivery lifecycles.
  13. Identify & Apply Design Patterns. No change except that I removed “Development Standards” as a separate building block as these are often tool or technology specific formings of a given design pattern. Common design patterns in the BI world range from requirements modeling patterns (e.g. the BEAM method by Lawrence Corr as well as the IBIREF framework) to data modeling patterns like Data Vault or Dimensional Modeling and design patterns for data visualization like the IBCS standards.
  14. New: Basic Refactoring. Refactoring is a crucial skill to become more agile and continously improve already existing artefacts. Basic refactoring means that you are able to do a refactoring within the same technology or tool type, e.g. within the database using database refactoring patterns.
  15. New: Additive Iterative Data Modeling. At a certain step in your journey to AgileBI you can’t draw the full data model upfront but want to design the model more iteratively. A first step into that direction is the additive way, that means you typically enhance your data model iteration by iteration, but you model in a way that the existing model isn’t changed much. A good resource around agile / iterative modeling can be found here.
  16. Test Driven Development / Design (TDD). No change here. On the data warehouse layer tools like BiGeval simplify the application of this approach tremendously. There are also materials availble online to learn more about TDD in the database context.
  17. Sandbox Development Infrastructure. No change, but also not much progress since version 1.0. Most BI systems I know still work with a three or four system landscape. No way that every developer has its own full stack.
  18. Datalab Sandboxes. No change. The idea here is that (power) business users can get their own, temporary data warehouse copy to run their own analysis and add and integrate their own data. I see this currently only in the data science context, where a data scientist uses such a playground to experiment with data of various kinds.
  19. Scriptable BI/DWH toolset. No change. Still a very important aspect. If your journey to AgileBI takes you to this third stage of “Agile Infrastructure & Patterns” which includes topics like individual developer environments and subsequently Continuous Integration, a scriptable BI/DWH toolset is an important precondition. Otherwise automation will become pretty difficult.
  20. Continuous Integration. No change. Still a vision for me – will definitely need some more time to invest into this in the BI context.
  21. Push Button Deployments. No change. Data Warehouse Automation tools (cf. building block 9) can help with this a lot already. Still need a lot of manual automation coding to have link with test automation or a coordinated deployment for multiple technology and tool layers.
  22. New: Multilayer Refactoring. In contrast to Basic Refactoring (cf. building block 14) this is the vision that you can refactor your artefacts across multiple technologies and tools. Clearly a vison (and not yet reality) for me…
  23. New:  Heavy Iterative Data Modeling. In contrast to Additive Iterative Data Modeling (cf. building block 15) this is about the vision that you can constantly evolve your data model incl. existing aspects of it. Having the multilayer refactoring capabilities is an important precondition to achieve this.

Looking at my own journey towards more agility in BI and data warehouse environments, I’m in the midst of the second phase about basic infrastructure and basic patterns & standards. Looking forward to an exciting year 2018. Maybe the jump over the chasm will work 😉

What about your journey? Where are you now? Do you have experience with the building blocks in the third phase about agile infrastructure & patterns? Let me know in the comments section!

Click & Ready: Setup a virtual training classroom

For our upcoming event “BusinessObjects Arbeitskreis” or simply BOAK (www.boak.ch) we are providing hands-on sessions to participants. We’ve used Cloudshare as a platform for multiple years now to provide every participant with its own virtual machine environment. This year we have the chance to use the Training module of the Cloudshare platform. This modules simplifies the setup of user environments massively and gives us – the instructors – better capabilities to assist our participants. In this article I quickly walk you through how you can setup a virtual training classroom in Cloudshare:

Create a new class

Open the Training module in the web interface of Cloudshare and choose “Create a New Class”. Give it  a name and choose which blueprint and snapshot of your VM environment you want to use for the class.

 

In addition, choose an environment policy. The policy controls how long an environment can be used and after which idle time an environment is suspended. Here you see an example of an environment policy:

Next, you have to choose a time for your class to start. Important: This starting time is used by Cloudshare to prepare all the necessary VM environments in the background so that your students don’t have to wait once they log in:

Finally specify a passphrase and a maximum number of students. You can also allow non-registered users to join – an option we are grateful for during BOAK.

Manage students & run the class

After having created the class, you can register students and eventually send out an invitation email to them.

What students need to join a class is only the Student Login Link as well as the passphrase:

If you allow unregistered users to join the class, you can force them to provide some details like name and company:

Once you are logged in, the student’s personal environment is already up and running – just clik “Start Using This Environment”:

Click on “View VM”

Now the HTML5 based remote desktop session is opened directly within the browser – please note: No addons are needed for this! This helps us to keep costs for additional configurations on our training laptops to an absolute minimum.

As a student I can ask for help, chat with the instructor or with the whole class.

As an instructor I can switch to the Instructor Console where I see a thumbnail of every student’s screen:

If a student asks for help, I can quickly “zoom in” and see the student’s screen live:

If needed, the instructor can take over control of a student’s screen:

Conclusion

In this blog post I’ve showed how you can use Cloudshare’s Training Module to setup and run a virtual training lab class. I have to admit that I’m pretty amazed how easy it was to use this feature. So far everybody is happy with it:

  • Our IT administrators because it reduces laptop-side configurations.
  • Me as an instructor: I can easily invite students and run a class without technical knowledge around virtualization, VM management etc.
  • Our students: So far they were very happy with the solution. It is easy to use and the performance is as if you work with your local PC.

   

BI-specific analysis of BI requirements

Problems of requirement analysis

Practically every BI project is about requirements, because requirements communicate “what the client wants”. There are essentially two problems with this communication: the first is that clients often do not end up with what they really need. This is illustrated in the famous drawing in Figure 1: What the customer really needs.

 

Figure 1: What the customer really needs (Source: unknown, with additional material by Raphael Branger)

The second problem is that requirements can change over time. Thus, it can be that, especially in the case of long implementation cycles, the client and the contractor share a close consensus about what is wanted at the time of the requirement analysis. By the time the solution goes into operation, however, essential requirements may have changed.

Figure 2: Requirements can change over time

Of course, there is no simple remedy for these challenges in practice. Various influencing factors need to be optimized. In particular, the demand for speed calls for an agile approach, especially in BI projects. I have already written various articles, including Steps towards more agility in BI projects In that article, among other things, I describe the importance of standardization. This also applies to requirement analysis. Unfortunately, the classic literature on requirement management is not very helpful; it is either too general or too strongly focused on software development. At IT-Logix, we have developed a framework over the last ten years that helps us and our customers in BI projects to standardize requirements and generate BI-specific results. Every child needs a name, and our framework is called IBIREF (the IT-Logix Business Intelligence Requirements Engineering Framework)

Overview of IBIREF

IBIREF is divided into three areas:

Figure 3: Areas of IBIREF

  • The area of requirement topics addresses the question of what subjects should be considered at all as requirements in a BI project. I’ll go into a little more detail about this later in this article.
  • In the requirements analysis process, the framework defines possible procedures for collecting requirements. Our preferred form is an iterative-incremental (i.e. agile) process; I have dealt here with the subject of an agile development process through some user stories. It is, of course, equally possible to raise the requirements upfront in a classic waterfall process.
  • We have also created a range of tools to simplify and speed up the requirement collection process, depending on the process variant. This includes various checklists, forms and slides.

Overview of requirement topics

Now I would like to take a first look at the structuring of possible requirement topics.

Figure 4: Overview of possible requirement topics

Here are a few points about each topic:

  1. The broad requirements that arise from the project environment need to be considered to integrate a BI project properly. Which business processes should be supported by the BI solution to be developed? What are the basic professional, organizational or technical conditions? What are the project aims and the project scope?
  2. If the BI solution to be created includes a data warehouse (DWH), the requirements for this system component must be collected. We split the data requirements into two groups: The target perspective provides information about the key figures, dimensions and related requirements, such as historiography or the need for hierarchies. This is all well and good, but the source perspective should not be forgotten either. Many requirements for the DWH arise from the nature of the source data. In addition, requirements for metadata and security in the DWH have to be clarified.
  3. The BI application area includes all front-end requirements. This starts with the definition of the information products required (reports, dashboards, etc.), their target publication, purpose and data contents. One can then consider how the users navigate to and within the information products and what logic the selection options follow. One central consideration is the visualization of the data, whether in the form of tables or of diagrams. In this area, advanced standards such as the IBCS provide substantial support for the requirement analysis process (read an overview of my blog contributions to IBCS and Information Design here). The functionalities sub-item concerns requirements such as exporting and commenting. When it comes to distribution, it is interesting to know the channels through which the information products are made available to the users. And it is important to ask what security is required in the area of BI application too.
  4. The issue of requirement metadata is often neglected; however, it is useful to clarify this as early as possible in the project. This concerns the type of additional information to be collected about a requirement: Does one know who is responsible for a requirement? When was it raised, and when was it changed again? Are acceptance criteria also being collected as part of the requirement analysis?
  5. Lastly, requirements need to be collected for the documentation and training required for the use and administration of the BI system.

Summary

In this article, I have indicated that requirement analysis presents a challenge, both in general and especially in BI projects. Our IBIREF framework enables us to apply a standardized approach with the help of BI-specific tools. This allows both our customers and us to capture requirements more precisely, more completely and more quickly, thus enhancing the quality of the BI solution to be created.

Upcoming event: Please visit my team and me at our workshop at the TDWI Europe Conference in Munich in late June 2017. The theme is “Practice Makes Perfect: Practical Analysis of Requirements for a Dashboard” (though the workshop will be held in German). We will use the IBIREF framework, focusing on the BI application part, in roleplays and learn how to apply them. Register now—the number of seats for this workshop is limited!

(This article was first published by me in German on http://blog.it-logix.ch/bi-anforderungen-bi-spezifisch-erheben/)

My life as a BI consultant: Update Spring 2017

Spring 2017 in Provence (France)


Obviously I hadn’t time to write much on my blog during the last nine months. Let me share with you what topics kept me busy:

For the upcoming months I’ll be visiting and speaking at various events:

  • IBCS Annual Conference Barcelona: June 2nd, Discussion of the next version of the International Business Communication Standards.
  • TDWI 2017 Munich: June 26-28, Half-day workshop about practical gathering of requirements for a dashboard.
  • MAKEBI 2017 Zurich: July 3rd, I’ll be presenting a new keynote around alternatives to traditional estimation practices
  • BOAK 2017 Zurich: September 12th, same as with MAKEBI, I’ll be presenting a new keynote around alternatives to traditional estimation practices
  • WhereScape Test Drive / AgileBI introduction Zurich: September 13th: During this practical half-day workshop you learn hands-on how to use a DWH automation tool and you’ll get an introduction to the basics of Agile BI.
  • My personal highlight of today, I’ll be speaking during Agile Testing Days 2017: I’ll do a 2.5 hours workshop regarding the introduction of Agile BI in a sustainable way.

It would be a pleasure to meet you during one of these events – in case you’ll join, send me a little heads-up!

Last but not least, let me mention the Scrum Breakfast Club which I’m visting on a regular basis. We gather once a month using the OpenSpace format to discuss practical issue all around the application of agile methods in all kind of projects incl. Business Intelligence and Datawarehousing. The Club has chapters in Zurich, Bern as well as in Milan and Lisbon.

AgileBI workshop London

Just a quick note for my UK based readers who are interested in AgileBI: I’m proud to have been selected as a speaker for the upcoming Enterprise Data & Business Intelligence (EDBI) conference in London, taking place on November 7 – 10. I’ll lead a half day workshop on Monday afternoon November 7 around my Agile BI Maturiy Model and of course would be happy to welcome you there too!

Just have a look now: Introducing Agile Business Intelligence Sustainably: Implement the Right Building Blocks in the Right Order

If you are interested in participating in this event, drop me note either by leaving a comment or contact me on LinkedIn and I can send you a voucher to save 200£ on the registration fee.

In addition, follow #IRMEDBI on Twitter!