Gartner BI & Analytics Conference – Modern Architecture

In an excellent session this afternoon, we were taken on the journey towards best practice implementation of BI/Analytics architecture. For the last 2 years Gartner have recommended that the 3 key areas are represented by the Information Portal, Analytics Workbench and Data Science Laboratory. Highlighted in the rather shaky picture below:

3 Tiers BI Arc

Each tier will offer different benefits to the business that can be summarised by the key roles and processes that a modern architecture need to accommodate. These are summarised in the images  below:

ModernArcProcesses.jpg

ModernArcProcesses

Finally by following this you could end up with the holy grail, if this is your businesses holy grail:

modernarcoverview

It was interesting in this session how they talked around vendors and again reiterated that no one single tool OR vendor can deliver this whole picture. However, they also pointed out that the magic quadrants should not be looked at in isolation as niche and smaller vendors that are in the lower left or not even quite on the board may well suit your businesses needs really well. Understanding your business needs, values and potential outcomes AGAIN seems the ultimate place to “bet your house” when it comes to delivering a successful BI/Analytics program. I would also quote Neil Chandler for businesses just venturing out into BI in that this will be a core competency of your business going forward and require “indefinite investment”. Don’t let this scare the financiers but do make sure people realise that the delivery of the information portal or a specific tool to do data discovery is not the end of the BI/Analytics journey. I much prefer Bibby’s Lead Architect, Richard Smith’s comments around this which has our internal program focussed on creating “an enduring BI capability” to be enduring you must react to change, within the business and within the marketplace.

Advertisements

Gartner BI & Analytics Summit London – Day 1

What a great day, very exciting and lots learnt from industry experts, with very little bias towards a vendor or specific technology. This post will form the first of two which is my attempt to mind dump some of the key takeaways I have picked up and found useful here at the conference.

BICC – Business Intelligence Competency Centre

Is dead… long live the ACE – Analytics Community of Excellence. In the keynote Neil Chandler suggested four things wrong with the BICC, business, intelligence, competency and centre! Although it is still the driver behind successful, versus non-successful, BI programs it is not encompassing enough of the modern world of BI and Analytics. Key benefits of this approach is an attempt to drive BI programs from business outcomes but mostly it fails and still becomes an efficiency drive, not linked to delivering actual business value. It also does not encompass a new wave of change in the business, self-service, which is near impossible to centrally manage. Finally it has not been driven around the new future of analytical applications which are focussed around algorithms and a scientific approach to running of businesses, and our lives!

Whilst there is a lot in words the evolution from BICC to ACE is not just about words it is about evolving and improving a good concept and bringing it up-to-date and inline with business needs. Analytics now seems to embrace BI and gives us a larger maturity scale for businesses in our ever changing world. Community takes away the need to centrally control and helps with the, already in-place, self-service. Finally excellence is about striving towards something we perceive as the ultimate goal, not just a list of competencies that are based around technology.

One thing that hasn’t changed but cannot be ignored is that you MUST FOCUS around BUSINESS OUTCOMES to achieve excellence in your BI or Analytics program.

Algortithms are KEY

An important key theme for the keynote centred around algorithms and their use in BI and Analytics as well as in day to day life. Guessing which classical piece of music was generated by a computer or Bach highlighted how compute power and science has progressed and there is a real feeling from Gartner that through the use of algorithms and the tools that support them we can automate, improve and gain valuable insight into our businesses. Algorithms are used all over your business today, take some time to document them and look to find tooling to support their automation and improvement. Citizen data science communities may spring up around this.

Other key notes:

  • IoT algorithms are set to generate $15 billion by 2018
  • Over half of organisations will be leveraging algorithms by 2018
  • By 2020 50% of Analytics leaders will be able to link their programs to real business value.
  • The best analytics leaders can formulate new questions as well as answer existing business ones. They also fail, learn and push the envelope; I would add they fail fast, time is gone for multi-year BI programs.
  • Data Management and Data Integration tools are converging, but not as fast as you may imagine.
  • BI and Analytics tools are also converging but it was noted NO one vendor can support all BI and Analytics needs and the buyers of each are currently different.
  • Quadrant analysts highlight IBM’s Watson Analytics as a good example of an analytical application.
  • Microsoft’s Power BI v1 (that horrible O365, SharePoint online linked tool) failed, but v2 has gained traction and is having a negative impact on Tableau’s performance.

Still a lot of learning to go, even on day 1 but I wanted to share this for those not fortunate enough to attend this amazing event!

 

Choosing the Right BI Partner

Having worked as a Senior Consultant for Thorogood Associates and Principal Consultant at Coeo, I feel very qualified to write this guide to help you when selecting a new BI partner.  I worked as a consultant for a combination of about 6 years and have worked in and around BI for over 15 years. During my consultancy time I have worked worth with over 100 companies and among the engagements around 20 green field projects. The majority of these companies selected to work with us because of a vendor recommendation or a personal connection with existing staff. The majority of these companies engaged before ever meeting the consultants that would be working with them or ever investigating the suitability of the partner. This is not to say that the engagements didn’t work, they often did, but I have often questioned customers selection criteria and wondered if there were not cheaper/better ways for them to approach their BI needs. For example would you buy a new car without a test drive first? Would you employ a BI developer or architect without an interview? If the answer is no to both then this blog may be of some use to  you. I now sit on the “other side of the fence” and I think there are many things worth considering before signing up with a BI partner, and a test drive might well be one of those!

Top 5 things to consider when selecting your BI partner:

1. Are they a certified partner to your chosen, or preferred technology vendor(s)?

There is little point talking with non-certified partners, but understanding the partner process your potential vendor uses is also important. For example anyone and everyone can become a Microsoft partner, easily. But to become a silver or gold BI partner requires a lot of important steps to be completed by the vendor.Question the website graphics and check vendor lists for up-to-date information and the steps required to achieve the advertised status.

If you havent decided on a technology or vendor then make sure your potential BI partner is certified to multiple vendors or none at all with experience of vendor selection processes. Alternatively, and ideally, select two potential partners per potential vendor and make this part of your vendor selection process.

Beware vendor recommendations as the engagement, at this point, with vendors is sales led. Are they the best people to recommend a partner to you?

2. What are their staff credentials, experience (ideally in your industry), and certifications?

This should be current, an MCDBA in SQL 2005 is not relevant. It should also be relevant to your requirements, Prince 2 certifications when you will provide your own project management matters little. Utilise LinkedIn as it is a fantastic way to validate some of the key people at your potential partner. Very early on in the process ask about the BI team and do some research, later in the process meet these people! Investigate industry experts in BI and often talking to them in open groups on Twitter or LinkedIn, can help get recommendations or confirm your thoughts on the potential partners. Look to relevant industry events and look for sponsorship and lead technical experts sharing their knowledge with everyone. Finally asking to speak to similar companies they have worked with and/or researching the case studies is also a valid and useful approach.

Ultimately treat this review as you would an interview for BI specialists. Most companies, around 90%, I have worked with all, ultimately, want to create their own internal BI competancy. To that end your choice of partner is about extending or even starting your own BI team for your company. Finding a partner that has experience doing this and have approaches for aiding this is also important. Meeting with, interviewing and validating these people is just common sense.

3. Does their engagement model fit to your requirements?

In my opinion it should be flexible, not cookie cutter BS that you could probably find on slideshare. Critically you should know your requirements and, if knowledge transfer is important, then how does the partner manage this or build it into their process. Does your business have ever changing requirements and priorities, can this partner support agile, and I dont mean they do stand ups and split work into sprints, true agile is not not just about this and validating their agile certifications is also important, if, agile is important to your business/department.

Do they force project management upon you? Do they charge for this or is it wrapped up as part of the price, day rate etc… Not that PM is bad, I think it hugely important and useful on projects. All partners should be quizzed about how they deliver the capabilities a normal PM function would bring. BUT this should be inline with your expecations and preferred way of working.

4. Do they have recent, reference-able and relevant examples of working on these kind of projects before?

This should include any key areas important to your business, i.e Master Data or data quality. Perhaps similar experience in helping transitioning you to your own BI team, in the fullness of time. Can you talk with their previous customers? If yes then I like to talk to the business analyst or BI team lead, rather than a PM. Talking to a key business user is also interesting if your choice is linked to a specific vendor.

5. How do they manage risk?

This is a broad area and difficult to gauge. But there are some key areas to think about during your selection process. Key man dependency – how does the partner make sure that if their star consultant leaves how does that affect your project and deadlines? Changing requirements – what are the processes and how do they manage the reality of changing requirements – no BI project EVER fits a static, waterfall approach. If your partners talk about being able to manage this but at extra cost, close the conversation immediately. These are best practice areas that all partners should include as part of their general way of working.

Apart from these areas to consider, what’s absolutely key is understanding what you want to deliver for your business and what the potential return or value is to that business. If at least one of these things are not clear then it is not time to engage with a third party, unless, perhaps they have good experience with these stages of work and can provide management consultancy to help build that initial business case; in my experience not many can do this from scratch and you probably need a less technical partner than the typical BI partners. But I don’t mean you have to have a completed business case or a full technical specification but you need some idea and definitely buy in from your business stakeholders.

Costs

Up front it is more than ok to ask for rate cards. If the partner doesnt work that way then ask for example costs of previous projects with similar requirements and deadlines; or ask for their average rate in their last financial year, or last two BI projects, trust me they ALL know this information. It is critical to make sure, for both you and the partner, that you are in the right ballpark right from the off. At an early stage it is completely ok for these costs to be indicative and you shouldnt start budgeting around these numbers, all good proposal stages require a more accurate assesment of need to identify more accurate costs. At an early stage it is also important to think about the cost approach, time and materials or a fixed approach. In my experience an indicative/umbrella budget with a T&M proposal gives you the best price and is the most flexible way to work with a partner. Fixed prices can work but due to the risk and nature of these engagements expect paying for a hefty upfront design stage and the partner (rightly) adding a % on top of normal costs to mitigate potential slippage/risk.

Top 3 Actions to complete whilst selecting the right partner:

1. Meet the team – and not just you, include key members of your internal tech stakeholders and potential internal delivery team members. DON’T just meet sales, or technical pre-sales.

2. See working examples of their work, relevant to yours – ideally they may want to run a mini POC with your data (perhaps with vendor supported funding)

3. Constantly review your requirements, for the project and business, against what you are learning about the partner.  Have your internal stakeholders be part of these reviews.

Time

This process should take as long as it needs, but match the length of time with size of the project and complexity of your requirements. I have found that evn a small piece of work this process can take as long as a multiple month green field project. Critically the time this process takes is up to you and should NEVER be dictated by your potential partners. Certainly question the lead time required by the potential partner to be available to start initiation or development kick off BUT dont be driven by this. Your project can wait a month for the right partner, it has probably waited a lot longer prior to this!

In Summary…

This process is a difficult one filled with the usual mix of technical review, emotional and emphatic feelings. The more people you get involved in this process, at relevant times, the better. If you are forming a BICC have your most important business stakeholders part of the process. If you or someone in your IT team knows of a good BI person (validate this) then have them talk about experiences and recommendations for BI partners. Finally I have seen matching your company size, or project size with a partner size. IMG Group (now Hitachi) always seem to work well with larger companies, more mature in BI, than say Coeo who worked much better with less BI mature companies, often of a smaller size.

I do have a spreadsheet that I used recently for vendor selection which I am more than happy to share, please comment with your details and I will email it over. I am also happy to help, where I can with my experience. Good BI people are close knit bunch in the UK and I am more than happy to share my experience and recommendations, for what it is worth to you! 🙂

And as always thank you for reading, I hope this is of some help!

Microsoft BI – 2015 Highlights

It’s been a great year for BI! Power BI coming of age,  exciting SQL Server 2016 CTP releases and a maturity in the cloud for analytics, data science and big data.

For me Power BI is the biggest news of 2015. POCs ran in H1 of 2015 found it wanting. Basic functionality missing and the confusion of wrapping it in Office 365 made it to much for businesses to consider. However with the GA release, and the numerous updates, it had finally delivered on its vision and given Microsoft an end to end, enterprise solution, for the first time in its history; including multidimensional connectivity!

Microsoft also made some great tactical manoeuvres including the purchase of Datazen and Revolution R as well as their excellent Data Culture series. Datazen is a good tool in its own right with great dashboard creation capability and impressive mobile delivery functionality on all devices/platforms. It will nicely integrate to SSRS top deliver a modern reporting experience via mobile in SQL 2016. R is the buzz of 2015, a great statistical analysis tool that will really enhance SQL Server as the platform of choice for analytics as well as RDBMS. In fact you can already leverage is capability in Power BI today!

Cloud. So Microsoft finally realised that trying to drag businesses into the cloud was not the correct strategy. A hybrid approach is what is required. Give businesses the best of both worlds. Allowing them to benefit from their existing investments but “burst” into the cloud either for scale or new capability, as yet untested. SQL 2014’s ability to store some data files, perhaps old data purely kept for compliance,  is a great example of this. ExpressRoutes ability to offer a fast way to connect on-premises with cloud is brilliant. Or go experiment with Machine Learning, made Microsoft simple by the Azure offering.

For me I was also scored to see the PDW hot the cloud with Azure SQL Data Warehouse. An MVP platform is the closest my customers have needed to be to BigData but the initial outlay of circa half a million quid was a bit steep. With the cloud offering companies get all the benefits worn a minimal investment and an infinite ability to scale. But do consider speed of making data available as it could be limited by Internet connections.

So in summary an awesome year for Microsoft BI with the future looking great! I still feel Microsoft lack SSAS in the cloud but perhaps Power BI will gain that scale in 2016. Overall I envisage seeing Microsoft as a strong leader in the next Gartner quadrant release for BI and I can’t wait for SQL 2016’s full release!

The future (2016 at least) is bright, the future is hybrid cloud…

image

MS BI Current World

Datazen and Windows 7

Are you using Windows 7? Stuck on it for the foreseeable? IT will NOT let you be part of the test group for Windows 8.1 or 10? Then consider carefully any decision to utilise Microsoft Datazen.

Datazen is a great, simple, dashboarding and visualisation tool that is available as part of your SQL Server Enterprise, Software Assurance, agreement. It is a relatively simple tool which offers brilliant mobile delivery via iOS, Android and Windows. Datazen has connectors for lots of sources including Analysis Services. Client access is FREE and there is no cloud involvement, unless you host the Datazen server in Azure, but even then you could configure it so no data is persisted in the cloud!

My first customer who is using Datazen and Windows 7 called me in last week to help troubleshoot some potential show stopping issues they are having with the tool. The issues are to do with the Windows 7 Publisher application and creating, publishing and editing dashboards with Windows Authentication to a standard SQL Server.

The Windows 7 application is in preview: http://www.datazen.com/blogs/post/datazen-publisher-for-windows-7-preview-now-available and available to download from Microsoft. The “preview” tag is an interesting one as you may think that this would have been done ages ago…

However, after digging around the history of Datazen, it is clear that this release was an afterthought following the products acquisition by Microsoft in April 2015. The original product was only released in 2013 and was the baby of a team of people that previously gave us ComponentArt. According to their background over 40k people have been using the tool since its release and both Gartner and Forrester mentioned the product. However, I was not alone in the BI community, that had never heard of it.

Being released in 2013 means they definitely didn’t think about designing a front end to work with Windows 7. In fact, by the time of Datazen’s published release date Windows 8 had been in general release for over a year. So there is no way this product was built to work with Windows 7.

But back to the issue. My customer was really excited about the ability to have a BI tool that would enable them to create rich visualisations that could be accessed by up to a 1000 users for free! If you ignore the cost of a SQL enterprise license and appropriate SA! They sensibly set up a test server (two actually but the server design can be discussed at another time) and got the Windows 7 client installed on the MI team’s laptops. Their data source is a very simple SQL Server data set storing 100s of rows of summarised data.

The MI got about the relatively easy business of creating dashboards. However, the next day they tried to edit dashboards themselves (from the server, not the local copies) or other users tried to just view the dashboards, using the Windows 7 application, and they wouldn’t open. Even local dashboards couldn’t be re-published to the server.

The led to some serious concerns that they had made the right decision to use this tool. It seems the Datazen, Windows 7, application loses the connection to the Datazen server, sporadically. This can be spotted by the failure to be able to publish or by a small icon (apologies no screenshots as I don’t have a Windows 7 VM to recreate locally) under the connection name that shows the BI hub on that Datazen server. By removing and then adding the connection again the MI team are able to publish.

We also found that this wasn’t a network or security issue as if the same users browsed (using Chrome or IE) to the Datazen server they are able to view the dashboards that simply wouldn’t open in the Windows 7 application.

So we have a temporary workaround. Keep re-creating the connection in the Windows 7 application and use the browser to actually view dashboards. Luckily there are NO end user issues as they will NOT be using the Windows 7 application to view, they will be using iOS app or direct through browsers.

Finally, we did manage to test some scenarios using a spare Microsoft Surface that was running Windows 8.1. There were no issues!

In summary you should be wary of using this product with Windows 7 and be mindful of the fact that the product wasn’t built for Windows 7 and for the best experience you do need to be on a later version of Windows. This shouldn’t detract from Datazen being a fantastic option for a BI tool. It is free and doesn’t touch the cloud, something that a lot of my customers are very excited about!

Just to note we are raising the issues with Microsoft, but at the moment it is not clear if there are plans to do a full, non-preview, release of the Windows 7 application; given that Windows 7 is still the most used OS I hope so! I will keep you updated.

APS (PDW) – Extracting Load and Query Stats

APS (PDW) – Extracting Load and Query Stats

Hi, this is a short blog post that may be useful to users of the PDW to get a full list of load statistics and query statistics. The main area to get statistics is the PDW dashboard, but in a lot of cases this is not enough. It is even worse if best practice has not been implemented and labels for queries are not used then the dashboard becomes less use than a chocolate teapot.
So in order to extract load information from the APS the following query is rather useful, note that this will only pull back information on backups, restores and loads, if you loaded data using “insert into” for example information would not show in the results.

SELECT

r.[run_id], r.[name], r.[submit_time], r.[start_time], r.[end_time], r.[total_elapsed_time], r.[operation_type],

r.[mode], r.[database_name], r.[table_name], l.[name], r.[session_id], r.[request_id], r.[status], r.[progress], case when r.[command] is null

then q.[command] else r.[command] end as [command], r.[rows_processed], r.[rows_rejected], r.[rows_inserted] from sys.pdw_loader_backup_runs r

join sys.sql_logins l on r.principal_id = l.principal_id

left outer join sys.dm_pdw_exec_requests q on r.[request_id] = q.[request_id] where r.[operation_type] = ‘LOAD’

–AND l.[name] = ‘someusername’

order by CASE UPPER(r.[status])

WHEN ‘RUNNING’ THEN 0 WHEN ‘QUEUED’ THEN 1 ELSE 2 END ASC , ISNULL(r.[submit_time], SYSDATETIME())

DESC OPTION (label = ‘Rd_DataLoads’)

Note in the preceding statement a line is commented out. This line can be used to find loads completed by a specific user. The DMV for loads sys.pdw_loader_backup_runs stores all loads over time and persists after a region restart. Again best practice should be in place where users are logging into the PDW with their own user (or windows auth if possible) NOT sa! Finally note the use of labels:

OPTION (label = ‘some comment in here’) 

Labels can then be used either in the queries you use on this page or even through the dashboard where you can see a column for labels. I would recommend your team applying some naming conventions or standards for labelling. Next query below is useful for pulling back a list of all queries that have been executed on the APS:

select q.[request_id], q.[status], q.[submit_time] as start_time, q.[end_time], q.[total_elapsed_time], q.[command], q.[error_id], q.[session_id], s.[login_name], q.[label]

from sys.dm_pdw_exec_requests q inner join sys.dm_pdw_exec_sessions s on s.[session_id] = q.[session_id]

where LEFT(s.client_id, 9) <> ‘127.0.0.1’

order by [start_time] desc OPTION (label = ‘Rd_Query_history’)

This will give you a list of all queries performed by all users of the APS, however the DMV used: sys.dm_pdw_exec_requests only stores up to 10,000 rows so depending on the usage of the APS the query above will only give you a very recent snapshot of query performance. My recommendation for both of the above queries would be to set up SQL Agent job on the loading server to extract these stats, from the PDW, into a dedicated stats database on the loading server. You could then use SSRS or any other tool to do some proactive monitoring of large/long loads and queries for example. At worst you have a nice log of data over time should you start to get feedback about degrading performance for example.

Building BI Projects with Agility

Let me introduce you to an interesting story that was part of the foreword of the Mike Cohn book “Succeeding with Agile: Software Development with Scrum”. In the story, trappers would stop off at the Hudson Bay store to get all their supplies before setting off into the wilderness and make their fortune. However once they had done their shopping at the supply store they would only go a few miles and then set up a camp. The idea was that they would be better to identify what they had missed in that initial shop and only have to go back a few miles rather than being lost in the wilderness without an important piece of equipment. Although most trappers, like most consultants, feel their planning is brilliant the really good ones prepare in a way that they don’t end up frozen in the Rockies!

I like this analogy to projects but does it really resonate with BI? For me it certainly does. On an older BI project we had units of measure to handle in our solution. Applying a Kimball design pattern I asked the company are there really only 3 UOMs you want to handle? The reply was a confident yes with confirmation that they had not reported on more than three for the last 5 years. Great! So based on this confident feedback I built the UOMs into columns in the fact table(s) rather than applying a UOM dimension. I created MDX for time intelligence for each date hierarchy and for each UOM, for three UOMs this wasn’t a hardship.

However, a few months later the customer requested my time to work on an enhancement request to add another 5 UOMs. Unfortunately the nature of consultancy “time & materials” means the customer doesn’t want to spend time (therefore money) on redesigning the core design and including a UOM dimension. So I was left in MDX script hell, although not with the usual complexities of writing MDX but in copy paste and having the longest MDX script in the western world (there must be longer ones in China with 1000s of characters in their writing system!).

If we had been more agile and delivered an earlier version of this cube (as this was the deliverable) then perhaps the business would have seen how easy things now were with these UOMs and suggested they would like to slice the data by more. We then would have had time to refactor the design and implement our UOM dimension.

BUT not all projects fall easily into a completely agile process. For example, did the trappers go to the Hudson store and buy just a sleeping bag (forgive me if these weren’t available in 1890) and then head off to camp to check it worked. Then they bought a kettle on day two before on day three realising they needed some tea bags… I don’t think so. Yet many software developers who talk of pure Agile say that there is value in delivering a web page with a username text box, no password box and no logon button… I don’t agree and would argue that there is value in QA and UAT at this point, but NO value in that delivery to the business.

With BI projects there may be some value, depending on the business requirements, to deliver a product dimension and let the business see this; I certainly found this with retailers that could then identify missing attributes and start to design hierarchies they wanted or with customers that had a customer dimension for the first time. Of course there was value in applying a new business definition of a unique customer and then seeing what this list of customers was for the first time.

However the key to both of these examples is that the biggest business value for these customers was looking at sales orders over time for these products or for identifying how customer recency affected the actions of this new list of customers. To delivery the biggest value to the business we have to make sure we have the tent, sleeping bag, cooking stove and, at least, a kettle to boil water. Everything needed to do a basic camping trip in the Rockies (I am not a camper). For me this is the high level design of the BI project and I suggest to deliver any value to the business the high level design needs to include everything you know you need at the start of the project. On later camping trips it is likely you will have more experience and that the initial design or shopping trip will be more detailed and there will be less trips back to the store.

From that point on, continually delivering value, based on business priority/need, is achievable. Breaking down complex tasks into work items and grouping these into sprints works really well but not just for the sake of unrelated work items. It is much better that a sprint focusses around a group of items that, together, may allow delivery of something that is valuable to the business. To do this you must be interacting constantly with the business. In a recent project we had an excellent MI team who helpfully worked with us to group work items in a set of business headings all relating to a key area that would add value to the team and therefore the business. We could then focus sprints around these headings and, once deployed, the MI team could start to see value in the BI project.

But the key on a new project is that without that high level design and that first big effort of choosing what you need, building it and then heading out to the wilderness, you are simply going camping with just a sleeping bag and will make more trips to the supply store than are really needed. Get good requirements from the right people (not IT), build as comprehensive a design as your experience and the requirements allow and then deliver the first cut for business review as early as possible. To help with this I often try and not worry about control processes, partitioning strategies, incremental ETL loads etc. I move that to later stages of the build so we can focus purely on delivering that first cut to the business.

In summary I do not think that a single “pure” process has been required across all my projects. But being more agile by using best practices from across the various methodologies has helped me to be more and more consistent in delivering high business value solutions to my customers. And just like evolving your development processes helps improve delivery always look to learn from each project to help evolve your way of working, this for me is one of the best things being Agile teaches us.