May 2, 2008 at 4:47 am
We are migrating from an Access db because the volumes of data have got beyond its abilities.
Everything we do centres around reports. These are always output in Excel, and may or may not contain pivots, charts and macros for onward processing by recipients. In most cases the reports need the base data rather than just the completed pivots.
I've just spent 2 weeks writing a report I could do in a morning in Access, and it still won't work. Despite a good deal of help from this and other sites, I am stymied by 2 fields of greater than 255 characters. If I leave them as a unicode string they fail on output due to truncation. If I use a data conversion to make them into unicode text streams I get an error about not being able to create an OLE DB Accessor.
If I get past this problem I know I'll get another and I've reached the end of the road with this, as I've got at least 50 more reports queuing up for the same treatment.
So what I'm thinking now is to abandon SSIS, write views that extract the subsets of data needed for each report, and do the nitty-gritty processing in Access.
Is this a bad idea, or does anyone think it has some genuine merit?
May 2, 2008 at 6:53 am
Personal opinion, so please take with a grain of salt...
You should put the time in to learn how to do things properly in SSIS & SQL Server. That will serve you better in the long run rather than falling back into familiar patterns in order to get something done quickly. You've already stated that your app is growing beyond the bounds of Access. What happens when one of the reports that squeeze down into Access also goes past the ability of Access to deal with? Now it's in production and the users are breathing down your neck for a fast solution. You think learning when the pressure is light is hard, try learning with several VP's standing behind you.
That's my 1.5 cents. I'm sure others will have different, and better, opinions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 2, 2008 at 7:36 am
I used to work with a guy who had created an access database with a bunch of linked tables in it so he could use the query builder in access to build queries and then use the queries in reporting services to build a report.
He got the job done - actually, come to think of it, he did not get the job done. In fact, he never really did much other than come up with bad designes that I later had to fix.
I have apparently lost my train of thought. Learning something new is a good thing. If you find yourself having to use MS Access as a crutch for something having to do with SQL Server, you need some training. You should find that building and deploying reports using SSRS and SQL Server is actually more flexible and FASTER than using reports in an Access database. If this is not the case for you, training is a good idea.
This is complicated stuff. You are not going to teach yourself in a weekend. I know it is difficult to convince a company to pony up the funds for some training, but it is going to pay for itself in a few weeks if you are spending this much time on a single report.
Sorry if that was in any way harsh - everyone starts at the same place. Training helped me get where I am and I am sure it can help you as well.
May 2, 2008 at 7:50 am
Guys. I totally agree with you both. Unfortunately, I've had a 5 day training course covering SSIS, SSAS and SSRS. Now I'm on my own - apart from these forums. The problems I'm coming up against are never gonna be covered in the 2 days that was made available for the SSIS part. The real training is now hard slog searching for error messages and waiting for answers from forums.
I've now found the answer to my problem, but it's the latest one in a series and I'm still not out of the woods with this report. That's why it's taking so long
Budget is a huge issue and so is turnaround time. There is a quick and dirty solution to this reporting out there, and it won't involve our team or be as robust as our knowledge could make it. But the dozen or so VPs that I DO have breathing down my neck are only looking at short term, so we will lose out if I don't come up with something soon.
I'm not trying to create a job for myself. I have much more business knowledge and experience than the alternative, and I'm not prepared to lose this opportunity for the sake of expediency.
May 2, 2008 at 7:57 am
Long-term, the idea of going back to Access because of ease-of-reporting is a bad one. Definitely. If you have immediate needs, and Access is the only way you can achieve those needs, then by all means, use it.
But definitely take the time to learn SSRS enough to wean yourself off of Access for this, as rapidly as you can. SSRS is better at this.
There are reasons MS is putting more and more effort into SQL Server and it's satelite applications (SSIS, SSAS, SSRS) than they are putting into Access.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2008 at 8:01 am
Ok, that being said, I guess in my opinion, the answer is that the idea is not technically sound and you should try to come up with another solution.
From a technical standpoint, adding in what will end up being several unnecessary layers of technology is going to make the solution more difficult to maintain, less reliable, and difficult to upgrade. It may provide you with a short-term solution, but the overall cost will be higher from a basic maintenance standpoint and probably much higher as the technology choice eventually limits future options for providing solutions for the business.
May 9, 2008 at 1:03 pm
If you are dedicated to the SQL Server craft then you will find that it becomes easier each day. You can not learn it all at once, in fact, I will venture to say you will never learn it all. However, remember, you don't have to know everything, just where to find the answers.
That being said, this community is a great teacher. There are some outstanding DBAs and Developers that really care and never hesitate to teach their skills to others. All you have to do is post here and we all come running.
Look at the current scripts on the site, they should get you started in the right direction. And there is no such thing as a stupid post. Unless you don't include the information and your real issue.
I have been working with SQL Server full time for about seven years now, coming over from an Oracle background I used to feel as you do. Now I'm to the point that I actually prefer SQL Server over Oracle. It takes time, but it's time that is well spent.
You will find that there are some people here that you tend to watch and learn from. Me myself, I think Jeff Moden is awesome. Lynn Pettis is also great. Gila is one of the best DBAs I have ever seen. Michael (mr ten centeries) has his stuff together. Steve ole' wise one Jones is like the papa bear, always around to steer you when you go off track. GSquared is a new name to me, but it is active and everything I've seen from the name is good. Jack is the bomb as well. I can go on and on. The talent is here and from what I have seen and been part of. A little effort will get you a long way.
Marvin Dillard
Senior Consultant
Claraview Inc
May 12, 2008 at 2:15 am
Thanks for all the feedback. I've got my first report working, so I'm feeling more optimistic. I did it without Access as well!
I had to pad out line 2 of my excel workbook with text to the length of the input column, and then write a macro that kicks in the first time the workbook is opened, to remove the line and refresh all the pivots. It all seems very naff and I'm sure it would be better done in Analysis and Reporting Services, but no-one is paying me to learn. The pressure to deliver has been enormous, and continues, so I will try to pick up what I can as I go.
Thanks again to everyone who helped me this time and all those I know are there for me next time I need them
B2B
May 12, 2008 at 5:13 am
If you must use Excel for reporting, why not write a couple of SQL Server views and use "Get External Data" in Excel to interogate the views and seemingly "import" the data?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2008 at 7:20 am
It's an appealing idea, but I think it means either giving ODBC access to the user base, or someone opening the workbook and running the macro prior to delivering the report. While this latter option is a lot less demanding on clerical effort than the current Access/Excel version, we were trying to get away from manual involvement.
May 12, 2008 at 7:25 am
If you use views as suggested, you could give access only to the views. If you create a SQL login for just this process and give the login only access to the views, you still have a pretty secure solution.
May 12, 2008 at 8:14 pm
born2bongo (5/12/2008)
It's an appealing idea, but I think it means either giving ODBC access to the user base, or someone opening the workbook and running the macro prior to delivering the report. While this latter option is a lot less demanding on clerical effort than the current Access/Excel version, we were trying to get away from manual involvement.
Self executing macro on load...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2008 at 11:06 pm
There's nothing wrong with employing base views for reporting purposes either. This is a standard practice and will make your life easier for the other 49 reports. Probably you'll use the first reports to learn the new tools on and then be up and laughing with the others. A decent book or two would helkp yout out too.
May 13, 2008 at 7:17 am
I sympathize. The fact is that several things are easier in Access than SQL. It also sounds like you have unrealistic management expectations.
I don't know if they'll have anything against pressing a button in Excel, but I've given access to SQL data via a dialogue box. The desired report is selected, a button is pressed, activating a macro of ADO code that ultimately accesses a stored procedure (I'm not a big fan of views under ordinary circumstances), with each report having it's own stored proc. It worked very well.
May 13, 2008 at 7:46 am
There are some great tips here and thank you all.
The report I've just built is one of around 10 very similar reports, all with 20 pages of charts and pivots. As it's now working ok, I'm going to build the next few using this blueprint, and I'm hoping I'll learn a few more things along the way.
Yes, management expectations are high, but they aren't interested in the nitty-gritty. They want the information and they want it asap. Another technology (and team) can deliver what they want now, but not what they need next year. It's down to me to look beyond the short term, but to protect the future I need to buy some time
Thanks again
B2B
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply