Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72

    Unanswered: Excel as Database Front-end

    In all enterprises from the smallest to the largest there are many databases. These databases are full of pieces of data that can become information if they are extracted, assembled, analyzed and shared within reports.

    There are databases (SQL server, Oracle, Sybase, Access and others). There are also databases within data warehouses (Essbase, BI and others), accounting programs (Oracle, SAP, PeopleSoft, JDE and others), manufacturing and sales programs, production planning programs and so many other programs.

    Within all these programs there are some generic reports that can be created quite easily but when it is time to develop customizes analyzes and reports problems start. Even with extensive training you ht not e able to develop the analysis or the report that you need. During a conversation with a JDE expert he told me that most creator of these centralized programs don't get much into reporting because the needs vary too much. They could develop 1,000 templates but everybody need number 1,0001 or 1,00 and so on.

    The solution for all enterprises comes from Excel.

    - The administrator of all these programs can make the data available in XLS, TXT or CSV format so that the data users can copy/paste the data within their analysis and reporting applications.

    - You can use the "Data/Import External Data" functionality to automate the importation of the data from the files created by the programs administrator or to get data from the Internet. You can make this query to automatically refresh the data on opening so that your analysis and reports are always up to date.

    - You can use the "Data/Import External Data" functionality to extract data directly from the databases (with the authorization of the Database Administrator whose main responsibility is to protect the database).

    - All these processes can be automated or duplicated for hundreds of different queries with VBA the programming language within Excel. In VBA you can use SQL and even use all the functions of Essbase.

    The simple VBA code to extract data from a database looks like this:

    Sub proQuery()
    Dim varConn As String
    Dim varSql As String
    Dim varQuery As QueryTable
    varConn = "ODBC;DefaultDir=E:\Garson;Driver={Microsoft Text-Treiber (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;Ma xScanRows=8;PageTimeout=5;
    SafeTransactions=0;Threads=3;UID=admin;UserCommitS ync=Yes;"
    varSql = "SELECT DISTINCT `Generic Call Detail Report Rev`.`Subaccount #:`
    FROM `Generic Call Detail Report Rev.csv` `Generic Call Detail Report Rev`"
    Set varQuery = ActiveSheet.QueryTables.Add( Connection:=varConn,
    Destination:=Range("a1"), Sql:=varSql)
    varQuery.Refresh
    End Sub

    No need to learn SQL. Develop your sentences in wysiwyg with Access and copy/paste within your VBA code.

    And all this becomes interesting when you have learned to automatically transform a set of data into the report exactly as you want it with the magic function SUMPRODUCT.
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I think you should change the sentence:
    The solution for all enterprises comes from Excel.
    to

    The solution for some situations in some enterprises comes from Excel.
    The data in most enterprises is changing on a second to second basis. ANY data export could be out of date the instant is is generated...

    Plus, the issue of data proliferation and data validity will come into play when users have the ability to edit the data that is provided to them.

    What about when the number of rows of data exceed excel's ability to view/import?

    There are just too many issues with the excel front-end approach for someone to declare that it is a universal solution.
    Last edited by loquin; 09-11-07 at 14:24.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72

    Excel as front-end

    I agree with you that Excel has some limits.

    What would you say if I changed my sentence from "Excel is the solution" to "Excel is a solution"
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Excel is one of the worst possible solutions to most business problems. It is more appropriate for those in the academic or scientific community who need its powerful analytic functions but do not rely heavily upon maintaining relational integrity.
    My concerns about Pierre's original post being deleted arose from my opinion that he was not using it to spam or sell a service. I was certainly not putting my stamp of approval on the quality of its content.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by pierrevbaexcel
    And all this becomes interesting when you have learned to automatically transform a set of data into the report exactly as you want it with the magic function SUMPRODUCT.
    It would be far more interesting if it weren't so cumbersome to use SUMPRODUCT. The other huge problem with Excel is that lists don't work: if you have 10 items and add another, there's no way to make the formulas automatically extend and ranges won't reliably extend when you add new rows to the end.

    Excel's weakness (and, in terms of usabilty, a strength) is that it tries to satisfy the "magic paper" mentality of inexperienced users.

    I'm actually designing a kind of Excel front-end. I've got a lot of inexperienced users who need to share portions of data in a central repository. There's not much data, which is important because I'll be copying all of it on a regular basis. Even a simple Access front-end frightens them off, but they'll happily bang away at Excel. The other big advantage Excel has is that they can copy it on a thumbdrive and take data home with them.

    My solution is this: I have a script generate OOXML documents which, thanks to Office's very liberal extension interpreting, when named .xls behave exactly like a proper compound document. These are either emailed or stashed on a shared drive.

    Another script runs on a scheduler and checks the files for updates, does some sanity checks and synchronizes them with the DBMS. (It'll bork, of course, if they resave it in OLE compound format...)
    Last edited by sco08y; 09-12-07 at 02:27.

  6. #6
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    sco08y

    If you have data from row 3 to row 200 and expect to add data you enter a SUMPRODUCT formula that covers from row 3 to row 500. When a record is added the formula includes it.

    Fortunately the world is full of "inexperienced users" who need developers. They are inexperienced in technology but usually very knowledgeable in their own field where I am the "inexperienced". Together we make a team.

    If a bright solution makes the user feel stupid it is not a good solution.

    As for your solution I am an accountant by trade turned Excel-VBA Consultant so I am too "inexperienced" in technology to understand your solution. The vocabulary is out of my range. But if it works and "inexperienced users" are happy it's good
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by sco08y
    It would be far more interesting if it weren't so cumbersome to use SUMPRODUCT. The other huge problem with Excel is that lists don't work: if you have 10 items and add another, there's no way to make the formulas automatically extend and ranges won't reliably extend when you add new rows to the end.
    Using dynamic named ranges solves the extending problem.

    For instance, if you have a file in which columns contain data (columns and rows could be added), and it will be added to every day/week/month, then define that range, by going to Insert, enter a name in the top (myRange), and then in the box below, put this formula in:

    =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Da ta!$1:$1))

    where

    Data!$A$1 is the starting cell

    0 is the offset rows from starting cell

    0 is the offset columns from starting cell

    COUNTA(Data!$A:$A) counts the number of rows

    COUNTA(Data!$1:$1) counts the number of columns

    Thus, you can use any formula to get what you want.

    Or you can setup three ranges, one for the data, one for the column headings, and one for the rows identifier, and use lookup purposes

    =INDEX(myRange,MATCH($A2,myRow,FALSE),MATCH(B$1,my Col,FALSE))

    All ranges are automated. So also,

    =SUM(myRange)

    Unless I have misunderstood what you mean.

    EDIT: I notice the board is arbitrarily adding blanks between some of the formulas. There should be no space in the named ranges myRow, and myCol.

    ===========

    I pull data from our mainframe every week (or day if necessary), for 100 Markets with 30 different categories with 3 different systems for each. I use a slightly different method than described above, but once the data is pulled, I can change one cell in four region workbooks and all data are updated with 13 week rolling views (and some are 18 month views), all 900 charts are automatically updated, and all are linked pictures to PPT.

    When I pulled the data via VBA, it took 20 minutes, and then 10 minutes to update all PPT files. About four months ago, IT changed the access application; now we can't access the original data in the terabite databases, but only tables that IT allows access to. While it required a new setup for pulling the data, the table/charts setup of all Excel files did not have to be changed. I set up the process:

    Database pull ---- Excel setup ---- Excel charts ---- Powerpoint files

    That way major changes in one does not necessarily cause change in the others (unless the categories changed, the number and consolidation patterns, which just happened too). But the principle still works.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    Shades

    There are many different ways of doing things and your way is OK.

    When I choose a way I always choose a way that most users can understand I look for the simple way.

    For example I only use named ranges to create drop-down lists with the feeding list on another sheet.

    I never use COUNT, COUNTA SUMIF, VLOOKUP, HLOOKPUP or LOOKUP because I use SUMPRODUCT and INDEX/MATCH

    In your example you are talking about complex and huge sets of data. Most users have fixed number of fields and variable number of records.

    If in the first month there ar 50 records an it is expected that there will be aroun 1,000 records in the last imort of the year and I want to sum a column with 2 criteria I will not write:
    =SUMPRODUCT(($A$2:$A$50=whatever)*($B$2:$B$50=what ever)*($C$2:$C$50))
    but
    =SUMPRODUCT(($A$2:$A$1000=whatever)*($B$2:$B$1000= whatever)*($C$2:$C$1000))

    I apply the same approach with INDEX/MATCH.

    What is the "False" argument for in your INDEX/MATCH

    Would you be kind enough to give me your opinion on my posts being censored by Pat Phelan see the thread (Psot Deleted) in the Excel forum
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  9. #9
    Join Date
    Oct 2003
    Posts
    1,091
    I agree, a lot depends on the needs and setup. My setup has been in use for 18 months with no problems, and several other people use them now. And there is no VBA in any of this (since we moved to the new access application in Excel). Thus, I teach people how to use it. But the people I teach are Analysts. The original set up takes time, but I have even developed a template for them with most formulas and links already established. They quickly catch on and then can provide for themselves. Granted, I never use this for the Director/VP/SVP level. But the results from all of these are seen by the executive level.

    FALSE in the MATCH formula insures that there is an exact match; otherwise, it will pick the closest value.

    Here is how I would use dynamic named ranges for your use in SUMPRODUCT. Anytime you extend the SUMPRODUCT beyond the used range, it will incur overhead. I would define three named ranges:

    RngA would be

    =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Da ta!$1:$1))


    RngB would be

    =OFFSET(RngA,0,1)

    (which is one column to the right of the first one, hence, Col. B)

    RngC would be

    =OFFSET(RngA,0,2)

    (which refers to Col. C, two columns to the right of the original range)

    Then your SUMPRODUCT formula would be

    =SUMPRODUCT((RngA=whatever)*(RngB= whatever),RngC)

    And it would only take those rows that are actually filled, no matter how many rows. Just more efficient.
    Last edited by shades; 09-12-07 at 15:02.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  10. #10
    Join Date
    May 2007
    Location
    Lost in Translation
    Posts
    946
    Excel could be considered a good reporting tool for many different database backends, but to say that it is a "Database Front-End" is a misnomer at it's core. A Database is live data. An Excel spreadsheet, once extracted from the data" is merely old data.

    You cannot update, delete or otherwise modify data using your Excel front end without uploads into and downloads out repeatedly, which uses too many calls to the backend, and too many avenues of GIGO problems on your database. Excel is a functional reporting tool, but it is not a Database Front-End. It is merely reporting a copy of the data for the user. It is not interacting with the database once it is in Excel.

    There is a huge difference between a Reporting Tool and a Database Front-End.

    Not to mention that Crystal Reports probably does all these functions you are talking about more efficiently, with less problems and greater user-friendly tools.
    "Passion rebuilds the world for the youth. It makes all things alive and significant. ~Ralph Waldo Emerson

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "User Friendly" is not a phrase associated commonly associated with Crystal Reports, unless preceded by the word "not".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    May 2007
    Location
    Lost in Translation
    Posts
    946
    Crystal XI is highly user-friendly. Anything 9 and below is certainly not. There is a lot of great functionality in the newest version including a lot of drag and drop functions. I have plenty of users who find it really easy to work with.

    Excel... not so much.
    "Passion rebuilds the world for the youth. It makes all things alive and significant. ~Ralph Waldo Emerson

  13. #13
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    Liebling

    Yes and no.

    Most of the applications that I develop indeed work with dead data (yesterday, last week, last month) and are indeed analysis and reporting applications.

    But I have also built front-ends with live data using SQL queries or stored proc within VBA and working with live data.

    I have also built back-ends to mass upload millions of records on an HMIS database in collaboration with DBAs

    As for Crystal Report I am certified in it but I don't use it for one main reason: my clients want to work with Excel.

    Some would argue that Ford is sh... some others will say that GM is sh.. but they both get you where you want to go.

    When it is time to take the plane I trust my IT friends. But if I can get somewhere by myself cheaper, I drive and that is the position of most of my clients.

    We don't all drive Mercedes and fly planes sometimes because we can't afford it, sometimes because we don't want to. Freedom of choice.

    When people call me looking for the best program to do something I send them to a expert in technology. When they ask me for a temporary solution in Excel I develop it (some of them are 10 years old). When they ask me for a permanent solution in Excel with some knowledge transfer I am very pleased to accommodate them and they usually become very good at it.

    Thanks for your input.
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

  14. #14
    Join Date
    Oct 2003
    Posts
    1,091
    There might be better solutions. However, I work for a Fortune 50 company with 60,000 employees. I have as much say in which program will or will not be used for reporting and analysis as I do in influencing the sun's movement.

    Excel has drawbacks. But I have found that large data sets, even ones that change daily can be handled by Excel, but it needs extended planning and development to do what is required. My first foray into this required 2 months (keep in mind that at the same time I had acquired 50% of another person's work who left the company, plus my own work, so it was not total development time). But the process worked so well that the Director and several VPs saw the immediate advantage of the setup.

    BTW, I learned much of this approach from Charley Kyd who knows his way around enterprise solutions. He provides great help in many ways integrating databases and Excel.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  15. #15
    Join Date
    May 2007
    Location
    Lost in Translation
    Posts
    946
    Quote Originally Posted by pierrevbaexcel
    Liebling

    Yes and no.

    Most of the applications that I develop indeed work with dead data (yesterday, last week, last month) and are indeed analysis and reporting applications.

    But I have also built front-ends with live data using SQL queries or stored proc within VBA and working with live data.

    I have also built back-ends to mass upload millions of records on an HMIS database in collaboration with DBAs
    Please explain how you define "live data"?

    Once the data is queried, it is no longer live. If someone else changes it (in another program or in another open copy of your Excel spreadsheet) it is no longer the same as what you just queried without a refresh of your query. While your applications might be fine in some circumstances (limited access and users and not a lot of transactions) it would be completely useless for databases that are heavily used or in large corporate environments. In my case, most of the databases I work with run reactors and tanks that update on the millisecond, and Excel is not an option at all.

    Mass uploading isn't the same as working with live data. If your system exists on uploads and downloads, then it is not really working with live data.
    "Passion rebuilds the world for the youth. It makes all things alive and significant. ~Ralph Waldo Emerson

Posting Permissions

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