6/30/2011

Business Intellgience Report Developer Role

What is BI Reporting?

You start with a BI Developer.

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

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?

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?

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?

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.

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?

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.

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.

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.

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.

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.

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.

6/28/2011

SSRS Interview Questions

If you were asked to interview someone for an SSRS position, what questions would you ask?

Here's a few basic questions:

(Background questions) how long programming in SSRS, how did you learn Reporting, Are there any other reporting tools you've used?

(SSRS BIDS Report Creation) - How do you create a report (wizard, template)?  How do you create a new project in BIDS?

(SSRS BIDS Report Environment) - How do you create a data source?  What's difference between Tabular & Matrix Report?  What is a Cascading Parameter?  Why would you create a Group?

(SQL) - How do you limit the query result set?  What is a UNION?  How do you Sort/Order a query?  What is Group By?  What is a Having Clause?  Have you used #Temp Tables or Derived Tables?

(SSRS Report Server) - How do you upload an SSRS Report?  How do you assign permission to a Folder/Report?  How do you create a Subscription (Data Driven)?

Obviously you can dive deeper into any of these questions as they just scratch the surface.

Here's a link I found on the web:

6/27/2011

Future of Information

When we think of Business Intelligence (BI) we thing of answering user questions by querying some data to solve some business problem.

Most of that information has already been captured into a repository.

That information is based on fact (the past).  What happened, when, by whom, where and how.

Who was my biggest customer from 2005-2010?  What was my highest selling product in the NE USA?  What month has the slowest sales volume?

All these questions can be answered with a SQL (Sequel) Query against a Relational Database.  All that data can be dumped into a Data Warehouse Cubed database which pre-populates the aggregates into fast slicing information targeted towards the VIPs.

Taken another step, Google.  A repository of information available to anyone, anywhere, with no account information, at no cost.

You ask, "nearest Ice Cream store in Tampa?", it returns pages and pages of results.  So Google could be a user friendly Business Intelligence tool.

Benefit of traditional BI and the Google example is you can query on the information already captured in the past from a centralized repository/database.  With any luck, you can use that data as a model to predict future events / occurrences / trends.

There is still limitation to this.

Google has to spend all their time and money to capture, store, provide their version of the data/truth.  And for using their services which have advertisements, they receive revenue.

Google does not own all their information, but they spend a lot of money gathering and storing that info.


What if all that info were available to anyone, anytime from anywhere?


What if all that information was decentralized?

What if every company, school, university, or any existing website for that matter inherently exposed Information in a user friendly, open access, web service to anyone at anytime.

Sort of like an open ended database, where instead of having tables and rows all contained within a single or series of databases, the information was stored in millions of Websites/devices,server/computer silos, each exposing their own data through a Public Information Portal?

For example, a Florist shop would expose the location, industry, merchandise, volume, prices, quantities through a Public WSDL using open source standards.  From a central utility/website/cell phone, when you search for the word "Florist" you could query all their information, etc. in addition to all other Portals containing the same WSDL information.

Kind of like a free, encyclopedia of dynamic information, owned by no-one, used by all, which could answer any question posed to it.

How many Florist companies exist in the US, the SE, in Florida, in Tampa, that are open on Saturdays, that sell begonias, who are minority owned and operated, etc. etc. etc.  It would answer the big aggregate questions like how many, averages, sums, min, max, etc and it would also provide the granular detail information row by row.

The data would not be stored by a single company, (ie, Google, Microsoft, Yahoo).

The data would be stored on millions of websites, devices, etc. which exposed pertinent information through Public WSDL Web Services, accessible by anyone (permissions could be added with pay for info concept), dynamically search able from any device using a common web information language.

Every electronic piece of information would have to be identifiable through a self describing XML like language along with any other characteristics which would help describe the information contained within that unit of information. 

Then the Public Web Service which exposes that information would have to divulge to the world what information is located on that device/web/computer through a Public WSDL and a SOAP like language could scan all information simultaneously until the information is found and returned to the querying user.

What do you think?

Toad SQL Server Freeware Version

Thanks to Brent Miller @bamiller3 for providing on Twitter the Link to download the Freeware version of Toad for SQL Server:

http://www.toadworld.com/Downloads/FreewareandTrials/ToadforSQLServerFreeware/tabid/562/Default.aspx

The install was easy, connected to 4 instances of SQL Server within minutes.

There is a limitation for this version where you can only have 2 concurrent connections at a time.

However, it has Code Complete, flexible Layout Option on install (I chose T-SQL layout), a Verify SQL button in addition to Check Syntax which is nice.

When running a query, you can Show Execution Plan.  The result set is in a crisp and clean format.  You can easily export to Excel, File, etc. although I was prompted to do download a plug in from Micrsoft (Primary Interop Assembly Download).  It then allowed an export to Excel.

This version does not allow for "Generate SQL" which is a nice feature in T-SQL.

There are a lot of buttons on top of the App which provide shortcuts.

In the Object Explorer, you get a nice view of all the Database objects.

Connection Manager displays all saved connections.

It's got an eyeball looking button which is actually the "View the object at cursor" when clicked displays all the properties of that object, in this case it was a table and it showed Properties, Columns, Data, Statistics, Indexes, Foreign Keys and the list goes on and on.

I have used the Toad for Oracle in 3 or 4 previous jobs and that is definitely the way to go instead of using SQL Plus*.

I've also used the Toad for SQL Server at a prior job as well.

This Freeware version seems to have enough features that I would use it on a daily basis in addition to Microsoft Transact SQL tool.

6/26/2011

Rubiks Cube

When I was a kid around 1980, my brother, Dave, let me borrow his Rubics Cube.  I was able to get one side completed.  He showed me a way to move the cube in a certain pattern as to not disrupt the other side.  This allowed me to get all the corners aligned.  Once I could get all the corners in place, it was just a matter of filling in the middle pieces until all colors were in place.

I remember working a contract in 2004 doing Crystal Reports, T-SQL, VB.net.  The secretary had a cube on her desk.  One night I completed the cube and placed it back on the desk.  The next day everyone was talking about who completed the cube.  It was a mystery until I finally gave a demo of how its done.

At another job, the assistant director had a cube which I completed.  Then he gave me a cube with 4 squares per side and one with 16 squares per side, which all have the same pattern to solve.

Another time at a Starbucks I sat next to a guy that had a cube.  I asked to see it.  He was talking about how difficult it was and I listened politely.  When he was through talking I handed him back the cube completed.

My wife gave me a cube with dogs on each side which I have on my desk at work.  It's a bit more challenging but sometimes I work on it in between tickets.

6/23/2011

Customers who want everything, yesterday

Working as BI Project Manager, we get to see all sorts of customers.

How many times have you been asked for everything in the world possible on a project.  On top of that, they want it ALL done yesterday.

Well, I learned from a very smart Project Manager, who happens to be my father, that there are only three possible ways to satisfy a customer on a Project:

  1. Time
  2. Resources
  3. Scope
You can either throw more time at the project by extending the due date.
Or you can throw more developers, DBA's, Business Analysts/Architects, etc And/Or Money at the Project.
Or you can trim the Scope of the project by leaving out some of the Specs for a future project.

And those are the ONLY 3 ways to satisfy a customer on a Project.

Business Intelligence Goal in 140 Characters

Opened the Twitter door as to the Goal of BI:
Jon Bloom

Jose Chinchilla

Jon Bloom

6/17/2011

SSRS Suggested Guidelines

1. Temp tables: drop table at top of query

IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL
DROP TABLE #temptable

2. Add Parameters to the top of SQL Data Sets in SSRS and comment them out so next developer can easily execute SQL in T-SQL.

3. When deploying report from BIDS, just deploy the one report, not the entire folder

4. Standard Font Times New Roman (Details: Size 10)

5. Remove un-used fields from the SQL for better performance.

6. Temp tables are ok but the preferred method is Derived Tables for better performance

7. Perform the Sorts in SQL and not in the report unless that’s the only way. Only sort the last query if using temp tables.

8. SSRS Report Heights should be consistent for Detail, Groups and Header/Footers unless the Header requires custom height to squeeze in Field Label. Also, the Vertical Align should also be consistent.

What other Guidelines do you practice when developing SSRS Reports?

6/13/2011

Ticket Closing Business

Back in the days of Rapid Application Development, Microsoft Visual Basic & ASP were the tools of choice for a lot of developers including me.

The user called on the phone, emailed or spoke to you in the hall about what changes they needed.  For those of us who didn't know any better we usually did what they asked.  There was no record of the request and no history of the exchange.

Today, things move super fast.  And coding enhancements have to keep up.  However, I have discovered that ALL user requests should be documented and recorded in a ticketing system.

Typically the user contacts the Help Desk with their one or two sentence request.  The ticket then gets forwarded to a department where it sits in the queue.  At first glance the request may have well been written in hieroglyphics because the nugget of request is nowhere to be found.

At that time, it's up to the developer to contact the user and figure out exactly what they really want.  The ticket should get updated to record the request.  Some shops use the SDLC process to enforce requirements and hold the user's feet to the fire.  After specs are formally gathered into the document, the users typically signs off on the request.

I find that users still like to email/phone in their request.  When that happens I like to enter a ticket in and assign the user so an email gets generated to all parties involved.

While developing the report/software, more clarification is needed and emails are exchanged between user and developer.  I like to add the gist of the conversation to the ticket for audit purposes.

In addition, when I manage the queue, I like to go through all the tickets assigned to the reporting team and document where we stand on each item.  That way I can print a quick report to bring to a meeting to discuss current status with peers/customers.

Only when a customer agrees should the ticket be closed.  A lot of times the eager developer closes the ticket premature and the user gets annoyed.

I like to tell people "I'm in the ticket closing business."  It seems odd but that's basically the truth.  I told one of my customer's this and he replied, "I'm in the ticket creating business."

What are the benefits of the ticketing system.  More reports.  You can now dissect who requested what when, how long it takes, heavy users, and the list goes on and on.  I recently created a report which got sent to 122 users asking them to close all their tickets as they went into the black hole of ticketing and were not updated for a long time.

The trick is to effectively manage the queue and not let anything exceed 180 days.  Obviously things out of our control cause delays.  However, if you document the ticket, anyone at anytime can go through the ticket and see where the hold ups occurred.

When a ticket enters our queue, I review, estimate the difficulty, see what resources I have available, juggle the priorities and assign the report.  About half the time I end up writing the report myself as we have limited resources and never ending requests.  We also have some reports that have greater complexity and I tend to work on those.

I think by having some level of order, IT people can now manage the chaos that once existed.

Few words of advice, don't call the help desk if your Doritos got stuck in the vending machine or if the Twitter site is moving slow.  Also, don't place a "wake up call for 3pm" with the help desk or ask if the 900# access has been blocked!!

6/08/2011

BI Project Manager

What makes a good BI Project Manager?

--Click Here for Link--

A blend of PM, Business Analyst, Information Analyst, Developer, Architect, Quality Assurance and whatever else hat you can think of.

The business knows what they want (sometimes).  The BI Developer knows the technology side.

The BI Project Manager unites the two:

Business + Technology.

The BI PM would not have to know every little detail regarding the technology side.  But they should have a good high level knowledge of the inner workings. He/she should be able to get into the code and read the SQL, modify reports when necessary, manage the web server, validate the data, speak the customer lingo, know the underlying database structure.

It seems to me this role is valuable to the organization. 

I wonder if there's a niche market for BI Project Managers?

Microsoft WebMatrix

Things are changing all the time.  Was on the MSDN site and noticed a new, free, easy to use Website Creation tool called: WebMatrix

Create, customize and publish websites with WebMatrix

WebMatrix is a free web development tool from Microsoft that includes everything you need for website development. Start from open source web applications, built-in web templates or just start writing code yourself. It’s all-inclusive, simple and best of all free. Developing websites has never been easier.

--Click Here for Link--

It's amazing how fast things change.  And how many new products there are.  And how much there is to learn.  And things to keep up with.  Learning is a never ending process.  And being in the IT field, you never stop learning!

How to suppress the subreport when there is no data in it? LINK

Today I was working on a Transcript Report which contains Sub-Reports (4).

Sometimes a Sub-Report contains no data.

This causes an error to be displayed on the Main Report.

In order to fix this, you can go to the Sub-Report --> to the Filters Property --> No Rows





There you can enter some verbiage.

When the user runs the Main Report, the Sub-Report no longer throws an error and the user is happy!

--Click Here for Link--

6/06/2011

SQL BI User Group (Tampa Bay)

Tonight's meeting for the Tampa Bay SQL BI Users Group was good. 

A first time speaker gave a demo of  SSRS 2008 R2 and I actually learned a few tricks.

The second speaker gave a good demo on Share point and stressed the importance of Microsoft's vision for BI to incorporate/blend Share point and Power Pivot. 

I thought there could have been some demo of the BI integration piece since it is a BI users group, not promo for Share point. 

There were some slides which showed KPI/Dashboards, most likely pointing to SSAS cubes, but no indication was given.

I believe he dropped the "f-bomb" as well as the word "sh*T" but who's counting.  BI people are tough!  They can take it!

And I was lucky enough to snag an XL Pragmatic Works T-Shirt, cool!

And two slices of pizza.

On top  of that I bumped into Mike S.  We were on the same BI team for Z-Tel in 2001-2 and we also worked for the same company at different times later on.

I also bumped into Allen E. BI guy from Catalina Marketing in 2004/5.  I worked there as a contractor and the full time job I turned down, he accepted.  Small world.

And of course Jose C. the president of the Users Group.  Jose is always jovial and has some humor to interject as well as a plethora of information on BI & Microsoft.

But the main theme that I took away from tonight's meeting is BI programming  is HOT!  There is a huge demand in this field and will increase as time goes on.  They said BI pays a lot.  Hmmm.  I work for the Gov't and haven't seen a raise in 4 years, not only that but this year I'm taking a 10% pay cut due to budget crisis!!!!   Hmmmm!!!

6/04/2011

Satisfy the Customer

The old phrase "Satisfy the Customer" is true today as it was 50 years ago.

I'm working on a project for the Volunteers at the School Board.  We are tasked with converting 25-30 reports from the legacy system.  The new app went live three weeks ago and they need the reports ASAP.

Reporting is usually the last thing people think about during the life cycle of a project from my experience.

So how do we satisfy the customer with enormous workload and limited resources.

Well, first off, wearing the Project Manager hat, I have to meet with the customer to scope out the quantity of reports. 

Then separate each report into a category of complexity: Difficult; Medium; Easy.

Each category gets a specific number of hours assigned to it, for example:
Difficult may take 16 hours
Medium 10
Easy 6

Now you have the number of reports times the number of programmer hours.

Next add in percentage of hours the Report Writer has available each week, in our case 60% of her time, or 24 hours per week. 

From the total number of hours required for the project, and number of hours available per week, we can predict when the project should be complete.

Good.

Next, the customer can prioritize her reports in any order she wants.
Now to enter the info into some type of Project Manager software, Microsoft Project or Open Project.

Going forward, we can give a percentage of completion/remaining with a fair amount of precision.

Obviously some report may take longer and some may be done faster.  If so, the project schedule will need to reflect.

I prefer this methodology as opposed to the tornado approach where the customer frantically throws down her wish list of must haves and demands everything get done now.

I prefer to manage the customer's expectation, manage the project and produce a quality product in order to "Satisfy the Customer".

Mountain Living