Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: How to you handle large outputs from RDBMS?

    Obviously Excel is the tool of choice for most people but with it's limited ability to leverage RAM (32 bit) and it's limitation with rows at just over 1 million what other choices do we have for viewing data?

    My bosses boss created several OLAP universes and they seems to fly a lot fast than regular relational database. This still doesn't help with the fact the data can't be worked with unless you have a strong front end that can handle processing all those rows.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you want agility, speed, and detail then you really need to use OLAP (Analysis Services using MDX or one of the BI derivatives) to provide the high level access then use OLTP (SQL Server using Transact-SQL) to provide the detail "drill down" to the raw transactional data. I don't know of any single technology that can provide full "end-to-end" and go from line items on an invoice to fast and flexible analysis needed by analysts.

    Even if you can connect the user directly to the Windows machine running SQL Server and have it do every iota of processing, the sheer magnitude of the data will eventually force it to be slow and cumbersome. You can't build a machine that is big enough and fast enough to provide all services from the cash register to the board room... Even if you could build such a machine, you couldn't afford to run it.

    An OLAP processor like SSAS is generally several orders of magnitude faster for doing the things that it was designed to do than other processors (like SQL Server at the detail end or HADOOP at the "big data" end). There can be some overlap in terms of what SQL Server and SSAS can do, but they're distinct tools with different jobs/target environments.

    By using the appropriate tools for the job, you can make tools that will provide Enterprise-wide, Department-wide, and invoice level details. They can even work when deployed to tablets and smartphones! All of the tools like SQL Server, Analysis Services, Reporting Services, etc. exist because they fill a need. New tools are constantly being developed. Pick the right tools, and you can create fast, flexible, and easy to use tools that your users will love. Try to bludgeon a single tool into doing every job and you'll spend a lot of nights and weekends fighting performance problems, bugs, etc.

    Tools like Excel are great for specific jobs. Excel is perfect for the "one man job" that takes a set of data, manipulates it on a desktop, and ships the result off... As you've discovered, Excel isn't good at "heavy lifting" and it is downright awful at process automation (like building a report at a specific time and emailing it to your boss). You've got a lot to learn, but the trip can be fun and the rewards can be fantastic!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As a side note, I design tools and apps to return 1-100 rows of data at a time. My target is generally about 20 rows. This seems to be a "sweet spot" that is small enough to be quick and comprehensible to the user, but large enough to show them a meaningful amount of data and to uncover any aberrations in the data that is returned.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Quote Originally Posted by Pat Phelan View Post
    As a side note, I design tools and apps to return 1-100 rows of data at a time. My target is generally about 20 rows. This seems to be a "sweet spot" that is small enough to be quick and comprehensible to the user, but large enough to show them a meaningful amount of data and to uncover any aberrations in the data that is returned.

    -PatP
    Pat thanks for the follow up I have a lot to learn you are right about that.

    Do you think web applications like WebMatrix is a good path to go down for building interfaces into the data or is that limited as well?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming that you mean Microsoft's WebMatrix 3 tool, it can certainly do everything that you've described but it is more ambitious than I think you need for what you've described... It would take several hundred hours to become proficient with WebMatrix. You could address all of the needs that I remember you describing with simply SSRS, SSAS, and SQL Server with probably 100-200 hours.

    If you have visions of extending your development skills, WebMatrix is a great choice. If you are looking to solve the problems that you've described so far (and a fair bit beyond what you've described) then I think that the suite of SQL Server, Analysis Services, and Reporting Services ought to be quite sufficient.

    I'd suggest that you visit http://www.sqlpass.org to see if there is a local PASS chapter that you could visit. In most cases you'll find somewhere between a dozen and a hundred people that meet every month and there will be enough experience there to discuss your needs and help you to find a comfortable solution for your specific needs.

    If there is not a convenient local PASS chapter, see the online chapters... They don't offer as much "community" as the local chapters do, but they often have more expertise.

    Feel free to join in the discussion online at the http://www.sqlpass.org web site. Their forums are excellent, but they're dedicated to the SQL Server family of products and rarely address other tools like Microsoft Access, MySQL, Oracle, etc.

    You're always welcome to post questions here at DBForums, but I suspect that you've reached the point where a bit of face-to-face time and live "in person" discussion will help you a lot more than posting a question and waiting for a response.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat thanks again for the free consultation I won't ask any more follow up questions after this. *** You have been most helpful

    I am 39, typical career changer. When I was right at 30 I switched to IT so my knowledge isn't as strong as others and development was not what I walked into originally. I was more around system admin and help desk type work. Eventually grabbed a team lead position and then went into a asset management analyst (scripting powershell against AD to find lost items etc) Mostly stealing code and reverse engineering. Eventually started using Access heavily and then SQL.

    So essentially Excel ~3 years at a high level (VBA etc)
    SQL ~3 years as well, intermediate reporting such as windowing, grouping, case, cast, views, stored procedures and even updates (including key removal and reapplication).
    Access ~ 1 year of hard experience.

    So here you have this IT guy who was thrown into development and (proof of concepts). I have a BS in Management and a MBA so I always tried to be a strategic mind building use cases and removing risk to operations.

    Caught in who knows what but my current job likes me to do technical task such as building dashboards, (business objects and Excel/SQL), load and change data, drive software project and architect data process into our team.

    I'm not even sure what that role is, but my title is BA, even though I have heard techno analyst is really what I do.

    I am going to look into the community piece that you mention, it makes the most sense. ***PS I really like database work and application development but I am realistic I am 39 with ~3 years of SQL dev and VBA. That's it and my VBA has been spotty not full time over the course of 3 years.

    Thanks PatP You are so helpful

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by VLOOKUP View Post
    Pat thanks again for the free consultation I won't ask any more follow up questions after this. *** You have been most helpful
    Great heavenly days, that's not what I meant at all! I have no intent or desire to stop your questions, my suggestion was intended to get you the right kind of help to keep you moving happily! Feel free to ask any questions, but be aware that while I'm working it could be hours before I get back to you... If that happens, one of the other DBForums denizens may well step up to confuse issues on my behalf!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Thanks Pat

    Knowing where to guide your career is difficult including which technologies to go after

    Things I have heard

    SQL is like COBOL it's outdated, learn NOSQL or these other new types of databases
    VBA is getting phased out, no way I would learn that. Learn how to build apps in the app store
    If you want to become a real super analyst you need to learn front end development. This is the future of analytics from a front end standpoint
    You are a BA you don't need to know these technologies. Focus on the BABOK and let the developers handle the reports.

    I'm sure I could come up with other things I have heard, but you get the point.

    It's a tough road out there and I really don't want to spend my nights and weekends learning technologies over and over again.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The hard part is trying to decide what you want to be when you grow up. I tried to figure that out when I went to Kindergarten, and five decades haven't helped me much on that front!

    You describe yourself as a "BA". If BA means Business Analyst, then you are rapidly growing out of that niche. If BA means Business Architect then I think that you've got a way to go, but I expect you to get there in a year or two.

    The bad news is that if you don't want to spend your nights and weekends learning new technologies, you'll soon become outdated. That doesn't mean that you need to spend every night and weekend learning, but you'll have to continue to educate yourself for the remainder of your career just to keep pace.

    There are those who observe that SQL is outdated, but I don't know of anything better that will mature in the next decade. Until there is a credible challenger, it seems premature to wail over the demise of a tool that has laughed off challengers since the late 1980s and still does the vast majority of all structured data manipulation being done today. There are tools like MAP/REDUCE and their derivatives for dealing with HADOOP data, but that's a completely different problem than an ERP or a healthcare system... You can't compare them any more than you can compare a paint sprayer with an artist's brush... Both apply color, but in radically different environments for radically different purposes.

    If you intend to wrestle with data in all of its forms, I strongly recommend PASS. The community is evolving and there are certainly a few bumps, groans, and mis-steps but by and large the community and the organization knows what is happening and also the fastest and easiest ways to get from where you are to where you want to be. The tools that they use and promote can solve nearly every data problem that I've ever seen and they continue to "push the envelope" further out!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pass it is for sure, when I get home tonight I will review.

    Business Analyst is my title right now. I would love to move into a Business Architect role eventually but I am not there yet like you mention, (you are right)

    I don't mind spending time labbing at home and trying different things and learning new technologies. I do it a lot I just don't want to do it all the time. I personally like SQL a lot and the more I use it the more I understand how sets of data work together. It's a great feeling but it's just the beginning steps.

    I do want to be a beast architect who can help companies manage their data problems. It's what drives me and it's my passion to be honest. I think my company is looking to transition me to a software PM eventually once we put a few of these fires out.

    I'll keep you posted on PASS, I can't wait!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •