10 Replies Latest reply on Mar 4, 2009 1:51 PM by Rachel_M_Garrett

    Corporate metrics dashboard - need advice on data source

    Rachel_M_Garrett
      Hi. I'm new to Flex, but about 1/3 of the way through the "Flex in a week" tutorials. I'd like to describe what I'm trying to use Flex for, and then get back some advice on whether it's doable.

      Our department has various metrics. There are multiple sources of data (SAP, data warehouses, Access databases, Excel spreadsheets), but everything eventually ends up in Excel. I've been asked to create a new dashboard, specifically for our department. I already know I could write enough VBA/formula "plumbing" to do the job.

      But I'd rather do it in Flex, for a couple reasons. One, Flex applications are so darn pretty! Two, even when I teach people about PivotTables and PivotCharts, that kind of data analysis is just not as intuitive as the interactivity you can get with Flex. Three, I want to push for more better information management (consolidate and integrate, rather than spawning new metrics and data sources ad hoc), and people are going to find that project easier to swallow if I'm bringing them a shiny, interactive web application along with it.

      We have Excel 2003, so using Excel Services (2007) is not an option. But I am wondering whether, if I took each spreadsheet and used the "Publish to Web" option, selecting XML as the format, whether Flex could consume that like a web service. I've looked at XML docs before, messed with InfoPath some, but never actually created XML schemas or anything. So the path from "what I'm looking at on a spreadsheet" to "what I want to see in my Flex application" is very fuzzy to me. I'd really like to hear advice from people who have solved similar problems.

      Thank you!
        • 1. Re: Corporate metrics dashboard - need advice on data source
          Level 7

          "Rachel_M_Garrett" <webforumsuser@macromedia.com> wrote in message
          news:gjc968$rta$1@forums.macromedia.com...
          > Hi. I'm new to Flex, but about 1/3 of the way through the "Flex in a week"
          > tutorials. I'd like to describe what I'm trying to use Flex for, and then
          > get
          > back some advice on whether it's doable.
          >
          > Our department has various metrics. There are multiple sources of data
          > (SAP,
          > data warehouses, Access databases, Excel spreadsheets), but everything
          > eventually ends up in Excel. I've been asked to create a new dashboard,
          > specifically for our department. I already know I could write enough
          > VBA/formula "plumbing" to do the job.

          You're going to still need that. Flex can't do the data mining for
          you...you're going to need some kind of back end technology to do that.
          With your VBA history, you'll probably find that ASP classic is the easiest
          for you to get up and running in. However, if you use ASP classic, you'll
          probably be limited to sending XML to Flex, whereas if you use one of the
          other available technologies you'll be able to take advantage of typed
          objects.

          > But I'd rather do it in Flex, for a couple reasons. One, Flex applications
          > are
          > so darn pretty! Two, even when I teach people about PivotTables and
          > PivotCharts, that kind of data analysis is just not as intuitive as the
          > interactivity you can get with Flex. Three, I want to push for more better
          > information management (consolidate and integrate, rather than spawning
          > new
          > metrics and data sources ad hoc), and people are going to find that
          > project
          > easier to swallow if I'm bringing them a shiny, interactive web
          > application
          > along with it.

          If you think Excel's pivot tables are unintuitive, I'd suggest you read the
          docs on the Flex equivalent, the OLAP datagrid. Even if your users won't
          have to learn that stuff, _you_ will, and you need to decide if you're up
          for that (or can do it in the time you have).

          > We have Excel 2003, so using Excel Services (2007) is not an option. But I
          > am
          > wondering whether, if I took each spreadsheet and used the "Publish to
          > Web"
          > option, selecting XML as the format, whether Flex could consume that like
          > a web
          > service.

          Possibly, but I suspect you'll find that what you get there isn't going to
          take you very far in data analysis.

          > I've looked at XML docs before, messed with InfoPath some, but never
          > actually created XML schemas or anything. So the path from "what I'm
          > looking at
          > on a spreadsheet" to "what I want to see in my Flex application" is very
          > fuzzy
          > to me. I'd really like to hear advice from people who have solved similar
          > problems.

          You're probably better off using a database, because SQL is far more
          powerful for doing calculations and data analysis than anything you'll have
          access to in a spread sheet. However, if you're used to "thinking in spread
          sheet" you will also need to learn about relational table design. I'd
          suggest that if your data is at all hierarchical that you use something like
          SQL Server (or Access, if this doesn't need to support a lot of concurrent
          users), because MS has provided a cool type of connection that lets you use
          a SHAPE query to pull down data in its natural tree shape. I suspect there
          might be classes out there that will allow you to go straight from a SHAPE
          to hierarchical XML, but I haven't used a SHAPE query in probably 3-4 years,
          so I don't know what people are doing with them today.

          MPO is that this is a far bigger task than you can imagine based on what you
          know now. Learning Flex on its own is probably at least a year-long process
          to get to the level you need to be to accomplish this. Then when you add
          learning about relational databases and data mining them to produce the XML
          or whatever you need to transmit that information, consider this
          conservatively to be a 24 month project--18 months or so to learn what you
          need to know, and then 6 (if you're lucky) to get it built. I'm assuming
          you already have the graphic skills you'll need to make this a shiny RIA
          like you want.

          Good luck;

          Amy


          • 2. Re: Corporate metrics dashboard - need advice on data source
            Rachel_M_Garrett Level 1
            Thanks, Amy. I marked this question as the answer, but I have some more questions.

            I'm more optimistic about the 18-month timeframe; still I think 12+ months is realistic. I've built Access databases before, and I'm comfortable with SQL. I guess I should revise my comment about PivotTables and PivotCharts; I use them every day and find them easy to use, but many end-users here are not comfortable enough *interacting* with the data, dragging around columns, etc.

            I didn't realize there was anything that would let Flex talk to an Access database. That would definitely be easier than talking to each Excel spreadsheet. But would I have to write a web service or other code?

            It has to be an Access database rather than SQL, because only being a power user rather than a "real" IT person, I don't have access to any kind of database server.

            Thanks!
            • 3. Re: Corporate metrics dashboard - need advice on data source
              rtalton Level 4
              The good thing about Flex is that it can work with all kinds of back-end technology. Maybe you can get someone in your organization to set up the methods of retrieving data from your existing databases. Then you can concentrate on the client-side aspects using Flex.
              There are lots of Flex books which can help you get up to speed quickly. Look for one which concentrates on the data retrieval methods that Flex uses: RemoteObject, WebService and HTTPService.
              • 4. Re: Corporate metrics dashboard - need advice on data source
                Level 7

                "Rachel_M_Garrett" <webforumsuser@macromedia.com> wrote in message
                news:gjdgk7$ggl$1@forums.macromedia.com...
                > Thanks, Amy. I marked this question as the answer, but I have some more
                > questions.
                >
                > I'm more optimistic about the 18-month timeframe; still I think 12+ months
                > is
                > realistic. I've built Access databases before, and I'm comfortable with
                > SQL. I
                > guess I should revise my comment about PivotTables and PivotCharts; I use
                > them
                > every day and find them easy to use, but many end-users here are not
                > comfortable enough *interacting* with the data, dragging around columns,
                > etc.

                I'm sure you are, but I'd still suggest you look at the OLAP datagrid docs.

                > I didn't realize there was anything that would let Flex talk to an Access
                > database. That would definitely be easier than talking to each Excel
                > spreadsheet. But would I have to write a web service or other code?

                You'd have to write some kind of back end code. I've used this before to
                take the results of a stored query and dump it to XML for use in Flex
                http://www.4guysfromrolla.com/webtech/060601-1.shtml. I like using stored
                queries for data analysis because, among other things, you can "stack" them
                to get realy complicated results that would be almost impossible in a single
                query. The problem with using stored queries from Flex is that when you
                call them you just pass along the parameters and Access knows how to
                interpret them. But Flex doesn't respect the order of the parameters, so
                you have to put them back into order before calling the query.
                http://flexdiary.blogspot.com/2007/07/flex-reorders-url-string-parameters.html

                > It has to be an Access database rather than SQL, because only being a
                > power
                > user rather than a "real" IT person, I don't have access to any kind of
                > database server.

                If you're going to use Access and you decide to use stored queries, they're
                going to all need to be right before your system goes live, because you
                actually have to physically take the database down and change the queries if
                you need to make any changes. You also need to be aware that you'll need
                some kind of server to make this work, so you will probably need an IT
                person involved. I believe there's also an intermediate form of Access
                that's a step below SQL Server, but I'm not sure how that operates.

                If your company is willing to dedicate a solid year to 2 years of your time
                to this project, they should be willing to get you SQL Server :-).


                • 5. Re: Corporate metrics dashboard - need advice on data source
                  Rachel_M_Garrett Level 1
                  That would be great if I could get someone to set up the data retrieval for me, but it's not going to happen - our developers are all working on products for our actual customers. We only happen to have some Flex Builder licenses available, because we bought another Adobe product.

                  I will research the methods you mentioned. I think I can write a web service in Visual C#, and it sounds like one of these three methods is what I would use to talk to the web service and display the info in a Flex application.

                  • 6. Re: Corporate metrics dashboard - need advice on data source
                    rtalton Level 4
                    SQL Server is good, and your Access databases can be upsized/imported into it.
                    Check out the Microsoft's SQL Server Management Studio Express and Visual Web Developer 2008. Both are free tools from MS. You install these on your development machine. You will need a web host with a plan that includes .NET and SQL Server. Then you can create your DB's locally and upload them to the web server.
                    This will allow you to leverage your Access DB skills. It is quite a learning curve!
                    • 7. Re: Corporate metrics dashboard - need advice on data source
                      Rachel_M_Garrett Level 1
                      Thank you. I'm still confused over whether what I'm trying to do is "doable" with the resources at hand.

                      --I have an Access database. It's on a shared drive, but I control it and have full rights to it. (I can pull all the Excel stuff together in Access.)
                      --I have Flex Builder (trial), DreamWeaver, Visual Studio .NET 2005
                      --I have administrative control over certain intranet pages, and can upload files to the intranet server
                      --The server will allow ASP (.asp), but not ASP.NET (.aspx)

                      With the existing setup, is there any solution I can create that will allow a Flex application to interact with the Access data? With a decent "proof of concept", I am sure I could get access to other resources, but right now, I can't purchase web hosting or use our own SQL servers. Could a web service sit on a shared drive and get called from there, or does it need to be on a web server?

                      Danke schoen...
                      • 8. Re: Corporate metrics dashboard - need advice on data source
                        rtalton Level 4
                        You can certainly do a proof-of-concept using SQL Server on your development machine.
                        I've never used an Access db as a data source on an intranet-maybe someone else here can answer that question.
                        If you need further info, send me an email at: rtalton at anaheim dot net
                        Good luck!
                        • 9. Re: Corporate metrics dashboard - need advice on data source
                          Level 7

                          "Rachel_M_Garrett" <webforumsuser@macromedia.com> wrote in message
                          news:gjdl94$m7k$1@forums.macromedia.com...
                          > Thank you. I'm still confused over whether what I'm trying to do is
                          > "doable"
                          > with the resources at hand.
                          >
                          > --I have an Access database. It's on a shared drive, but I control it and
                          > have
                          > full rights to it. (I can pull all the Excel stuff together in Access.)

                          Can't connect to it without a server... Even if you roll your own driver
                          with a socket connection you'll have to have something installed to act as a
                          server.

                          > --I have Flex Builder (trial), DreamWeaver, Visual Studio .NET 2005

                          Flex Builder trial lasts 60 days. I'd strongly suggest you stick with ASP
                          classic to leverage your existing VBA skills. .Net is a whole different
                          kettle of fish.

                          > --I have administrative control over certain intranet pages, and can
                          > upload
                          > files to the intranet server

                          This is where you'll want to put your stuff.

                          > --The server will allow ASP (.asp), but not ASP.NET (.aspx)

                          Then you'll need to use ASP classic, which you can write in notepad if you
                          want. I sometimes use Dreamweaver for this, since it has line numbers.
                          Don't get involved in trying to use the automagic stuff Dreamweaver does,
                          because it's not powerful enough for what you need, and it's aimed at
                          producing stuff that's formatted for viewing in a browser.

                          > With the existing setup, is there any solution I can create that will
                          > allow a
                          > Flex application to interact with the Access data? With a decent "proof of
                          > concept", I am sure I could get access to other resources, but right now,
                          > I
                          > can't purchase web hosting or use our own SQL servers. Could a web service
                          > sit
                          > on a shared drive and get called from there, or does it need to be on a
                          > web
                          > server?

                          ASP classic doesn't do web services. It's a different thing. Here's a good
                          tutorial on how to get up and running with ASP classic and an Access
                          database
                          http://web.archive.org/web/20070212004852/www.webmonkey.com/webmonkey/99/13/index0a.html.
                          It's also a good intro to relational table design.

                          > Danke schoen...

                          Bitte.


                          • 10. Re: Corporate metrics dashboard - need advice on data source
                            Rachel_M_Garrett Level 1
                            I don't understand how ASP would help, if it doesn't do web services. I thought a web service was required for Flex to get at the database information, since it doesn't talk to the database directly. Am I missing something?

                            The Wayback Machine link is broken, unfortunately.