May 6, 2009 at 10:28 am
I am frustrated and need assistance writing a formal business justification for obtaining a container on a SQL server.
I have worked with SQL in past positions, but my latest promotion placed me in a position where the only access we have to SQL is the use of Query Analyzer.
We pull all of the data we need through query analyzer, place it in .txt files and add data from other sources to create a data set of all Medicare providers (hospitals, facilities, allied health professionals and physicians) that are associated with the health insurance provider I work for.
It is a huge final dataset that must then be analyzed 100 ways from Tuesday.
We do this all through Access. My Union Query takes over 2 hours to run. It takes a full week to run data for our executive report through 10 different access databases. I know 90% could be automated through SQL. Some manual data cleanup would still need to be done prior to loading into SQL.
We have multiple SQL servers with space throughout the network and have client services.
Each time I have mentioned getting a container I am immediately shut down by a cost argument. I have explained that the cost is minimal, only the cost of a developers time in creating a container with proper permissions and security that I could use. I believe the basis of the tunnel vision is that the other people in my environment do not know sql and are comfortable with Access.
I unfortunately cannot prove the time savings without a test environment. Is there anyone who has used both environments and can help me create a business justification with actual examples of time savings.
Thank you,
Jennifer
May 6, 2009 at 10:46 am
I would shutdown that request if I could because all that may be done better and faster using SSIS. I can guarantee that the right package will do cleaning, ETL, and all other nice pieces faster and easier. Try that and I believe your DBAs and developers will support you. Container is not the best way for the scenario you are explaining. I hope now you are not frustrated more … 🙂
I mean that 2 hour run is quite impressive and makes me wonder if you have multi-terabyte databases. Otherwise, it sounds to me as the query need improvement. We are doing SSIS ETL process for financial company and loading 200 million records a day in the same time.
Also, what container you mean: I understand it as a web service that can be addressed from Access. If this is right assumption – I would try to convince you to develop SSIS package that is scheduled to run at night and will prepare whatever you need in Access database.
Alex Prusakov
May 6, 2009 at 11:49 am
Not familiar with SSIS, but a quick read on the internet suggests you may have a point. Since cost is such a great factor these days and this is not a program on my computer do you have any idea of licensing costs? Any good links to learning tools?
Can you expand on the benefits of SSIS over a container. I know I can build processes to automate loads, create views and schedule reporting from a container, all happening behind the scenes while I do other work. Not happening in access.
I am not sure any package can do some of the manual cleanup. An example from last month was matching EDERSREEH HOSPITAL with Evergreen Medical Center in the same zip code with two different addresses. I had to guess that this was the only hospital in the zip code that could be a match. We have data sources that are pretty dirty without any limits on what can be entered.
Thanks,
Jennifer
May 6, 2009 at 12:07 pm
(puzzled) What do you mean by "a container"? I have never heard that term applied to something that costs money or in your context?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 12:12 pm
SSIS is already included in the price of the MS SQL Server. You do not need to buy anything extra. You need to install tools on your computer from the original installation CD with MS SQL Server and using SQL Server Business Intelligence Studio build package using simple drug-and-drop. There are a lot of good info on the internet and books. I was using Microsoft help files. This site has good training videos as well. It is easy to understand and start if you have used SSMS or Visual Studio. You can hire us to help you out 🙂
Alex Prusakov
May 6, 2009 at 1:22 pm
What I currently have is very limited access to a sql database through sql client services.
I access the database using query analyzer, run my query to extract just the data we need to multiple .txt files. I cannot even view the views or stored procedures, just tables.
I can attach to the SQL tables directly through the ODBC connection, but Access becomes non-responsive and fails when I run the same query in access as I do in query analyzer.
My job is to take the data from our sql server that holds the contract data for our main organization and match it to the data from all of our mergers and acquisitions databases to create a single list of medicare providers with indicators that show what providers have service locations in each zip code in the United States, what programs they are associated with and how they are contracted (Specialty, Products, roles etc). One record for each provider with columns showing each affiliation.
I performed similar tasks when I was in a different segment of our organization, but it was working with only mental health providers in the county of San Diego. There I had full rights on a "container" in SQL. It was my own database where I could link to data from SQL databases, import external data, create whatever tables, views or stored procedures that I deemed necessary to provide the management with the tools they needed. Through this and Crystal reports the bulk of my work was automated and reports appeared on our network.
Using only Access I am feeling like I am riding an old beat up trail horse after riding a competition hunter jumper.
To make a viable plea for better tools, be it SSIS or a database container I need help to justify the request. I am working with a group of people who have worked from the business management side toward data analysis and have no experience with any tool better than Access. Their knowledge of the business rules is fantastic and outshines mine 10 to 1, but the toolsets we are using is pretty much limited to the MS Office tools that they are familiar with. I have created some automation, such as using excel to import each regions summary onto a separate worksheet rather than running the query 5 times and copying and pasting the data into each sheet. Query analyzer seems to be an evil necessity that they were forced to use and resistance to change abounds.
May 6, 2009 at 1:34 pm
Just an FYI - when I create the ODBC SQL connection I am provided a list of over 600 SQL servers on our network.
May 6, 2009 at 1:38 pm
OK, I guess by Container, maybe you mean a VM? No matter.
SQL Server Developer's Edition costs less than $65. A 1 Terabyte USB drive can be had for about $150. (I can provide online references for both if you want). Is that really too much money for your managers?
If they still need justification, then make a reasonable estimate of how much time this would save you, multiply that times what they pay you (you can actually uplift that by 1.5x to 2x for overhead), then compare that to the $200 it will cost them to set you up with a 1TB SQL Server development system.
If that's not persuasive, then, well, I would start looking at options for longer-term changes.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2009 at 2:23 pm
OK, I am sorry for you but in big organizations you have to follow some rules. These rules can seem extreme but it helps to keep everything organized. Slow, but kind of efficient.
Now I guess I’ve got an idea of where you are. I am supporting the same type of organization and I understand where you are coming from. Here is my way of thinking:
- Query Analyzer is the tool from MS SQL Server 2000. So you have to do DTS package and ask your DBA to deploy it in production with the schedule to run.
- Since MS SQL Server 2000 is out of mainstream support almost for a year, DTS package is another headache for development team to convert to SSIS package in migration to MS SQL Server 2005. That is why they might not really willing to help you out.
- Access becomes non-responsive when you trying to create ODBC connection due to the size of the attached tables, memory on your PC, restrictions on the timeouts, etc. Also, I would say query needs tuning if it runs 2 hours.
- You were asked to “create business justification for obtaining container” that basically means politically correct rejection or necessity to start process.
Conclusion:
- Find a way to IT people who can actually help you out with placing of DTS package in production. That might need some papers and/or sacrifices :-). Try not to confront but make them shine in their own eyes. Remember, if you starting from “I had that on my previous place…” – this is the way to disaster. At the end of the day it doesn’t matter who is smarter and you trying to give them more troubles…
- You can send me you query for the quick analysis. I do not know your database structure, details, etc. but I have good experience with finding obvious bottlenecks. I might find out how to do it faster.
- Check if you have access to Enterprise Manager (it is in the same place where Query Analyzer is), if you can go to Data Transformation Services, and if you can create and save there a package. If you can do that, you might be in better position.
- If not, just let it go. It means that nobody really wants to have it done fast and easy. You can write a paper that explains why DTS package will be faster:
o Store procedure is faster than ad-hoc query because it already has been compiled, has execution plan and cached in memory;
o Executes at predetermine time that can be off the business hours that increase ROI from usage of the hardware and software in non-business hours;
o Minimize manual work (obviously you can do something else instead of process runs);
o IT department can see, manage, and improve performance of the system by tuning of the package and scheduling of the execution in non-business hours;
o Doesn't need changes in security and access rights because it might run under existing account;
o Can be deployed by internal personnel without any additional spending;
o It is a part of the MS SQL Server: do not need any extra money.
Hope that helps…. Any case, lets keep it out of forum because from that point it is not interesting to others. You can use my direct e-mail.
Alex Prusakov
May 6, 2009 at 2:26 pm
I think she needs to know an automated SSIS ETL package can make most if not all of the existing employees redundant. I remember helping our Project server group automate 3000 Excel files a week from Doctors so all the work associated with those files no longer exist.
Kind regards,
Gift Peddie
May 6, 2009 at 2:46 pm
You guys are missing something: she is working with a huge HIPAA regulated company (like Kaiser Permanente) in California where nobody can do anything without signed paper, there is no MS SQL Server 2005 – only MS SQL Server 2000. IT department can’t use magician stick to “make it happen” in five minutes. This is government like heavily regulated and very slow environment. So, let’s try not to push her out of job!
Alex Prusakov
May 6, 2009 at 2:55 pm
I did that at big Pharma which was regulated and it was in 2000 a DTS package automated with xp_cmdshell worked like a charm.
Kind regards,
Gift Peddie
May 6, 2009 at 3:06 pm
Here's what I would do.
Step 1 - Update my resume
Step 2 - Find a new job
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 6, 2009 at 3:44 pm
There is no place that is perfect in every way. It is pure math and normal that a lot of people just want a paycheck but not an actual work. And, by the way, this is not advise we were asked about.
Alex Prusakov
May 6, 2009 at 4:05 pm
Alex Prusakov (5/6/2009)
There is no place that is perfect in every way. It is pure math and normal that a lot of people just want a paycheck but not an actual work. And, by the way, this is not advise we were asked about.
True, but like I try to tell people Freedom of Speech does not equal Freedom from Speech. We are allowed to express our views. Alvin was simply expressing his view on what he would do in that situation.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply