Microsoft BI User Group

Business Intelligence Developers Role

Jon Bloom

• Bloom Consulting (Independent Consultant)

• http://www.bloomconsultingbi.com/

• Reporting since 1991

• Twitter: @SQLJon

What is BI Reporting?

• You start with a BI Developer.

• He/she basically converts raw materials, the Data, into a product, Information.

Meet with Customers

The BI Developer meets with the customer to define the purpose of the report.

• What is the purpose of the Report Request?

• What question is the customer trying to answer?

Business Side

The BI Developer must know the Business side of the Report.

• How many widgets were created in the month of June?

• How much revenue did that generate?

• What was the same production of widgets last year?

• The past 5 years?

• Can I see the graphical representation?

Gather Specs

The BI Developer must gather the Report Specification.

• What would you like the end product to look like?

• In what format would you like the final report (PDF, Excel)?

• What parameters would you like to be able to search?

• How often would you like the report delivered?

• Do you want to see Summed up data and/or Detailed data?

• How should the Report be Sorted?

Estimate Time

The BI Developer must determine Time and Resources to create the Report.

• Need to estimate the length of time to complete the Report request.

• It's good practice to write up the Report Specs in an SDLC document.

• The Customer should review, agree and sign the SDLC document.

Data Side

The BI Developer must know the data side of the Report.

• Determine where does this data reside?

• What Database Server?

• What Database Name?

• What UserID/Password is available for Reporting?

• What Tables / Views / Stored Procedures are available?

• Are there any existing reports to leverage from?

Technology Side

The BI Developer must know the technology side of the Report.• Create the Data Source query in either SQL or Stored Procedure.• Add the Data Source to the Report.• Add Header/Footer to the Report.• Add the Database Fields to the Report.• Add Parameters to the Report.• Add Groups, Customer Fields, Sums, Counts, Average to the Report.• Add Charts/Graphs to the Report.

Change Management

The BI Developer must know the company procedures for Change Management for new Reports.• The Report Specifications must be documented by the BI Developer.• The Report must be verified by either Quality Assurance or the Customer.• A Release date must be established for the new Report.• The Report Release must be scheduled with the Change Manager.• The Customer and Key Stake Holders must be notified of the Report Release.• The actual Report and Stored Procedure must be moved to Production.

Report Delivery

The BI Developer must know how to deliver the Report.

• Once approved, the Report is added to the Web/Portal in a specified Folder.

• The Permissions are set for specific Users on the Report.

• Automated Subscriptions can be added to the Report.

Troubleshoot Report

The BI Developer must know how to troubleshoot the Report.

• Once the Report is in Production, if any bugs are found they must be troubleshot and corrected.

• The Server Log Files are useful for troubleshooting Failed Generated Reports.

• If the accuracy of the Data is in question, the BI Developer can view the SQL and/or Report.

Close Ticket

The BI Developer must close out the Request.• The Report should be stored off in a Source Code Repository.• The Report Documentation should be stored in an accessible place.• The Customer should sign off on the Project that it was completed and accurate and within budget.• The Customer's Cost Center should be charged for the BI Developer's time if applicable.

Universal

• The above information is generic enough that it doesn't apply to just one vendor (ie, Crystal Reports, SSRS, Actuate, Cognos, etc.), it applies to all BI Report Developers.

Business Intelligence

• Business Intelligence is about solving problems.

About providing insight.

And guiding actions.

If you as a developer can do all three, you will be a success.

Evolution

• Reporting has been around for a while.

Mainframe

• Back on the Mainframe / AS400, people were writing batch reports for over 30 years, some are still doing it.

Relational Database

• Then along came the Relational Databasewith the definition being: "is a collection of data items organized as a set of formally described tables from which data can be accessed easily"

4th Generation Languages

• So then there were 4th generational languages which allow the developer to easily connect to a database, apply the necessary joins, without having know much about SQL.

Data Warehouse

•

Then came the Data Warehouse, which stores data for reporting and analysis. Data was denormalized for quick access with slicing and dicing the dimensions and measures for multi level dissection and drill down using a language called MDX, which is a query language for OLAP databases.

Discovery

• Because of time delays and complexity involved, users began to demand faster access to their data, delivered in a variety of formats, for consumption in near real time.

Which gave rise to a new breed of reporting such as Tableau, QlikView and Microsoft version Power Pivot. These new tools allow a business user, not necessarily IT people, to rapidly connect to a data source, pull data in, join the data, see their data, then deploy to other users, Power Point or web interfaces with permissions almost instantly.

Advanced BI

• Users can create stunning visualizations including Dashboards which drill down, drill through, color graphics, dynamically adjust based on Slicers and Pivots. With other tools such as Performance Point which usually require a developer, Balanced Scorecards are now common place with Key Performance Indicators KPI to quickly draw attention visually to points of interest on the Scorecard.

Mobile BI

• Users don't always stay put. And by that they sometimes are on the golf course, at the air port, at home, sometimes connected to the VPN other times not. Reports still need to handle this feature. A lot of Reporting solutions offer some type of Mobile access, through portals such as Microsoft SharePoint.

Hadoop

• Next step in the evolution of Reporting is a sub-set of Big Data, called Hadoop. This type of system uses thousands of low end servers to host enormous amounts of data, mostly un-structured, as in non-relational. Basically a central controller replicates 3 copies of every item to a variety of servers, which self regenerate if corrupt, and the data gets widdled down to key, element pairs, which can then be queries on in batch queries. The concept is quite powerful and has been around for some time, although because of the reduction in costs of hardware and software and developer time, it has entered the mainstream.

Artificial Intelligence

•Another intersection of Reporting and Data and Big Data is the advent of Artificial Intelligence. The goal of AI is to simulate a human cognitive brain, that understands vast amounts of information, can query in real time, can understand human speech and thought patterns, as well as fragmented speech that is regional or specific to circumstances, etc. If you've seen Space Odyssey series, you may be familiar with the computer character HAL, move each digit 1 letter (IBM), although the real version IBMWatson seems to be a lot friendlier and can win on Jeopardy.

Future looks Bright for BI

• So as you can see, Reporting has been here for a while, has changed somewhat over time, and will continue to grow and expand in the future.

THANK YOU!

• http://www.bloomconsultingbi.com/2011/06/business-intellgience-report-developer.html

• http://www.bloomconsultingbi.com/