Adatis BI Hackathon – Q3 2014 Inaugural Hack Day

Hackathons or ‘Hack Days’ are incredibly common in the wider development community. They encourage innovation, research, cross-team communication and collaboration. However, many people working with the SQL Server BI stack don’t see themselves as real developers and, as such, don’t see themselves as eligible for such events.

Here at Adatis HQ we disagree – the development practices driving forwards the entire community can and do apply to Business Intelligence. We’ve successfully implemented agile approaches, CI, TDD and a host of other disciplines previously seen as ‘only for real developers’, so why not adopt the community events as well?

To test this out, we recently held the very first Adatis BI Hackday. This was a purely internal event to trial the idea and see what we could come up with. It was very much a learning experience, but we had a great day and are already planning the next!

The structure:

Given it was the first hackathon for many, we opted for an unusual structure. We polled the company for a couple of ideas, picked the best two and then let everyone choose which team they wanted to join. Usually you would attend the event as a team and decide on your project during the time allowance.

Team A – Power BI

The first team aimed to recreate our internal BI systems using only PowerBI – no SQL Licences allowed!

Much of the work went on creating a PowerPivot model that pulled data in from our various timesheet, HR and Finance systems to provide a single model that represented our core business.

This data was then augmented using PowerQuery – clients were given descriptions pulled from Wikipedia and employees linked to their LinkedIn page.

Finally, several dashboards were produced in PowerView, charting earning by consultant over time, consultant-client cross-overs, core client dashboards and more.

Whilst the team achieved a huge amount in a day, there was a sense that there was a lot more than could be done. The building of the PowerPivot model consumed the lion’s share of the day, meaning further technologies such as PowerMap, Q&A and AzureML were beyond reach. Plenty of ideas for next time though!

Team B – Auto BI

The second team wanted to automate the early parts of the full datawarehouse build using BIML. If a base SSIS template can be produced to pull data from a standard source to a staging table, this would save a lot of time spent manually duplicating and tweaking template packages.

The team split into 2 groups – one to create a small C# front end that connected to a source system (SQL Server in the demo instance) and pull out the required metadata around the selected tables. It would then deploy a template configuration database and store the metadata there for later use.

The other half of Team B got to grips with BIML – working to build a BIML script for a fixed source table that created the necessary SSIS package. As this was their first time seeing BIML script, there was quite a learning curve getting to grips with it, but they soon had a package that matched the necessary requirements.

In the final hours of the day, the two groups reunited to add the C# wrapper to the BIML script that iterated through the source metadata and modified the BIML script for each table, creating a custom SSIS package for each table.

The proof of concept is certainly there and, with time to improve the front-end with additional source system types and more advanced functionality added to the BIML scripts, this could be a huge improvement in how we approach the more standard parts of the traditional warehouse build.

Conclusions

Everyone agreed that the day was hugely successful in getting everyone involved. Many technologies were new to Adatis, meaning the junior members were working at the same level as the experienced consultants and directors, with interesting results!

As a staunch supporter of Team B I couldn’t possibly comment on the overall victor (although I think it was clear…), but it was immediately obvious that HackDays are immensely beneficial to business intelligence developers.

Q4 2014 HackDay is already in the works – whilst the day will still be made up of internal teams while we revise and improve the format of the day, future sessions may expand to include teams from the wider BI community. Then we’ll see some real competition…