December 28, 2015 at 6:57 am
hey all,
I have been tasked with figuring out a way forward with reporting for our company. We are on SQL Server 2014 with no Data Warehouse and approx. 150 users. Right now we use SSIS to create excel spreadsheets of data for users as our “reporting”. SSRS may fill in some of the gaps but I am looking for something that is more self-service. I have looked at Power BI, Power Query, Power Pivot, and Power View. I need something where I can build the query, the user can run the report and be able to manipulate/filter the results. Can someone provide me with some feedback as to what other shops use? What the best practices are? Do people use a combination of these tools? Etc… I appreciate any help that I can get. Thanks in advance.
January 1, 2016 at 12:51 am
This is a very open-ended question that could take a loooong time to answer well. tl;dr: Know your customers, know the technology, be a team player.
First do some research. Things to discover (in no particular order):
1. How many data sources will you be reporting from? How much data (tables, rows, columns) are you going to be dealing with? Are the data sources well-documented, well normalised? Are they semi-structured (eg XML)? Are some of them just plain terrible in design?
2. Will any of your reporting be hitting live transactional databases (and therefore creating contention for resources)?
3. Some of your reporting will need to be current (say to within 5 minutes of data entry). Some won't. Understanding this will shape how you set up your ETL. If there's reporting that only needs to be current to say midnight last night, then don't have it inconveniencing the live system.
4. Who makes the decisions? If you have a senior "bulldozer" type of decision maker, there's a risk they'll make irrational requests and you'll have to be ready for them. As far as possible resist the temptation to make short-sighted design decisions just to get you over the immediate irrational request. You (or worse, your successors) could pay a very high price for a very long time, for appeasement decisions taken now. A huge mistake you can make is to start reporting off a database without setting up an ETL process.
5. What technologies does your licensing allow you to take advantage of?
6. Not all users are equal. Find out who your power users are and invest in them. They can take a lot of work off your plate down the track. Knowing your users and their needs will guide you in whether you need to focus on SSRS, or Power BI or whatever.
Some dos and don'ts (mostly don'ts)
1. Don't deploy reporting deliverables to production until they have been reviewed by someone capable of doing so. You can't review your own work. That's just life. No peers? Lone wolf dev environment? If so, your organisation is at risk and you should advise them of that risk. What they do with that information is up to them. Many organisations ignore this problem until it's too late.
2. Do have report execution time performance indicators. Your management will tell you how long a punter is happy to wait for a report to run or data to be retrieved. I've seen 30 seconds set as the upper limit in some organisations. I tend to think that's generous even as an upper limit. Your customers are busy people and they need information fast. SSRS ReportServer has some very useful execution log data about every report execution. You can use this to pinpoint many bottlenecks and problem queries.
3. Do make sure your code is well commented (explain the "why" more so than the "what").
4. Do make sure your code is efficient, and that YOU are efficient about making it efficient. It will help to know how often it's likely to be executed. Agonising for hours over some code that is going to save the server a second a week is a false economy... ok, it's not a false economy if it's taught you something that you can employ into the future, but you get my point.
5. Don't hide away in your office working alone for months on end on some project that's captured your imagination. When you emerge from the murk one day proudly bearing your shiny new product, expecting to be showered with praise, get ready for a rude shock. Your colleagues might not be that impressed with you. While you've been MIA, they've been manning the bilge-pumps and covering for you. They won't know your code either. Nobody likes a hero.
6. Know your limitations. If you've only got a handful of staff, you're going to have to make some hard decisions about what you have in your technology stack. The BI sphere is expanding at a frightening rate. In 2008, if you knew SSIS, SSRS, SSAS and TSQL, you were doing well and you could set up a fairly neat little BI Unit. Now (just within MS) there's also MDS, DQS, Power BI, Datazen, Tabular, R integration, SharePoint etc. People are always going to walk through your door and say "company X down the road are using [insert flavour-of-the-month technology buzzword here] - can you set that up for us?" If you want to actually get stuff done you're going to have to say no to requests like this from time to time.
7. Do keep clear records. It's possible that some manager will poke her head in the door one day and ask you to interpret data in an "ethically ambiguous" way. If you acquiesce and do so without an audit trail, you'll carry the can for it. She won't.
8. Grow your stable of deliverables as slowly as possible. When a customer says "I need a new report that does X, Y and Z" do your best to find an existing report or cube that does mostly what they want. The problem with developing hundreds or thousands of bespoke reporting solutions that all suit a single purpose, is that you create a maintenance nightmare.
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply