10 Replies Latest reply on Feb 6, 2014 3:31 PM by Malcolm

    Hosting sales solution for multiple clients - how to handle versioning?

    slayden@msn.com

      I have a CRM written for my industry that I would like to rent to others on a SAAS basis.

      Of course, everything between client companies must be kept separate.

       

      1. Do I need to install completely separate databases for each client, or can I separate the data and logic layers so that all I need to do is point to each client's data tables, respectively?
      2. If just data tables, what is the best way to handle that? On the web, I'd use cookies listing the DSN (datasource) but I'm not sure with Filemaker to to both keep it secure and reliable
      3. If separate databases entirely, what is the best way to handle upgrades/versioning? I'd really NOT like opening 100 or 100s of client program to make one or a few tweaks at a time

       

      Thanks in advance for any help.

       

      Scott

        • 1. Re: Hosting sales solution for multiple clients - how to handle versioning?
          mikebeargie

          #1 - "Of course, everything between client companies must be kept separate."

           

          If that's the case, then you answered your own question. Keep it separate, don't try to overcomplicate it by trying to consolidate it down to single points of access. Yes, it's possible, but if someone finds out they are sharing, then you might have legal issues on your hand.

           

          #2 - see #1

           

          #3 - Use the data separation model, and only deploy major updates. You should thoroughly test your software before releasing it as a SaaS application, to minimize downtime and revisions for upgrades. If you use the data separation model, then your ability to deploy upgrades becomes much easier and faster. Also, in your case I would recommend rolling your own login system and user authentication to a centralized database. If you remove the users from your interface FM file, then upgrading is as easy as close, remove, upload, open on all the interface files. For upgrades to your data file, you should script the upgrade process so that you can automatically import a client's data into the new version before you upload it.

           

          #4 - charge enough to your SaaS users that it covers your ability to roll out updates and upgrades. If you plan to roll out an update once per month, then charge a client one hour extra per month for that time.

          • 2. Re: Hosting sales solution for multiple clients - how to handle versioning?
            miler24

            Mike is absolutely right on everything.  We don't dare combine client data into one data file.  We also use the data separation model for our SaaS solution.  If you're frequently modifying tables, fields, etc., then I recommend RefreshFM to help deploy updates to multiple clients in a fairly simple batch process.

            • 3. Re: Hosting sales solution for multiple clients - how to handle versioning?
              slayden@msn.com
              1. I anticipate very few changes to tables and fields, but, I do expect regular interface and logic tweaks, more reports specific to layouts, etc.
              2. If I don't use a single point of access, and if not just a bunch of hosted fm all-in-one databass, then what do I use(?), and what is the point of the data separation model?
              3. My point of reference is 3-tier web:  presentation-logic-data, where the presentation and logic layers are universal in the program (easy to update in one place), and only present different based on the data in the data layer. 
              4. Then, based on a login table, the logic layer would hold a cookie for datasourcename/location (dsn) to- point ONLY  to database(s) associated with the user/user's company
              5. With Filemaker, since the logic is so intimately tied to presentation, it seems to be a 2-tier implementation. 
              6. How would I keep the presentation/logic layer separate from data with Filemaker and point to the correct remote datasources for individual clients on an in/then basis?
              • 4. Re: Hosting sales solution for multiple clients - how to handle versioning?
                miler24

                1.  If you don't expect changes to tables and fields, then data separation will save you a lot of hassle in the long run.  Everytime you update your interface, you just push out a new file to your clients without ever touching the data on the server.

                 

                2.  Aside from easier updates, data separation provides a big boost of speed, which is especially noticeable in a WAN environment.  To use a rough analogy, do you use the Google Maps website or app on your phone?  Most use the app...why?  The user experience is tremendously better and faster, that's why.  The same principle applies here.  Running a FileMaker solution 100% from the server means you're pushing the entire GUI, scripting, etc over the wire.  In a data separation model, the layouts load quickly, even over a 3G connection since a lot of information the system needs (layout, scripts, conditional formatting) are already local.  As a result, we can allow our users to experience rich layouts with substantial use of conditional formatting and photographic based backgrounds.

                 

                3.  Ok.

                 

                4.  You could do this...I wouldn't recommend it.  I suppose it depends how much you want your solution to scale.  We're shooting for many thousands, which would overload the single server this would be hosted on.

                 

                5.  I agree.

                 

                6.  External Data Sources.  You can point a FileMaker file to any other FileMaker file as long as it's accessible on the web.  Just get some static IPs or, better yet, sub-domains mapped to your servers and viola...you're in business.

                 

                Comparing a FileMaker solution to a standard web solution is fraught with difficulties.  There are significant pros with FileMaker in terms of quick development, good security, and built in features.  However, if you're trying to compete against the likes of Salesforce.com for example, well...they can afford to do things with web technology us mere mortals simply cannot afford.

                1 of 1 people found this helpful
                • 5. Re: Hosting sales solution for multiple clients - how to handle versioning?
                  slayden@msn.com
                  1. Do you have a good resource for how to implement the data separation model correctly for Filemaker you would point me to?
                  2. So:  The basic concept is to create a client (interface) program with the layouts, scripts, and logic and have fm users load that client database directly on their local computer, then merely point to the tables of data on the server as an external datasource?
                    1. If # 2, how do you implement client updates?
                    2. If #2, what is the speed effect of keeping photos and pdfs on the server in remote containers?
                  3. Speed with fmServer question:  If one is using the client-server with fmPro when it's all contained in one database (interface, logic, data) like one develops for one person/user on one machine, does fmPro instantly/programmaticaly seperate the using of those layers as part of the client-server (ie fmPro Server knows which is which and where to put it BECAUSE it recognizes that it's serving to clients - offlloading what is best to clients - presentation and logic - and keeping on the server what is best for the server), or do we need to build the seperate interface/logic programs and point to external datasources to get speed?
                  4. Are there any "gotchas" when splitting up a solution into data separation, like:
                    1. $$global variables not working
                      1. if so, how to handle keeping $$global data local for each user if I need to use global fields, but not have them change everywhere, but just for the current user's "session."
                    2. something needed to be done to address multiple instances of the same table on the relationships graph?
                  • 6. Re: Hosting sales solution for multiple clients - how to handle versioning?
                    miler24

                    1.  There are discussions within Technet, a few FileMaker published books (Ray Cologon's book comes to mind) that mention it and some decent websites that discuss it; just search for "filemaker data separation model".

                     

                    2. Yes.

                         1. Client-side updates can be handled automatically using some scripting and a container field to overwrite the previous file on the client.  That's a whole other topic.  No plug-ins are needed to do that.

                         2.  FMS has a wonderful feature called "Progressive Downloading" that is not talked about enough and is perfect for WAN use.  Large pictures and PDF's not only stream to the client but are also resized before being sent to only fit the size of the container field.  Very cool...and fast.

                     

                    3. I'm not sure I understand your question because that was a very long sentence!  Does FileMaker automatically separate stuff?  No.  However, certain tasks are usually handled by the client, such as the layout, conditional formatting, scripting, etc.  This is done whether you're using data separation or not.  If you're asking whether or not you're going to have to completely redesign your entire solution to take advantage of data separation?...yes, you will.

                     

                    4. There always are.  Variables are file specific, but that's easily overcome by using script parameters and results.  The biggest is the user accounts, but they can be scripted to match in each file as well.

                         1. While globals are file specific (they are a variable), they are user specific in a multi-user environment (same behavior as in a non-data separation solution).

                         2.  In a data separation model, in the interface file, almost all of your table occurances on the relationship graph will be from your external data source...aka, data file.  As for having multiple table occurances, that's not an issue.

                     

                    Obviously, the data separation model is a lot of work.  If you already have a one file solution and it is simple from a UI standpoint, this might not be worth the extra development time.  The more complex and heavy on UI features you get, the more this starts making sense, especially in a WAN environment.

                    • 7. Re: Hosting sales solution for multiple clients - how to handle versioning?
                      Malcolm

                      Have you tried any of these things yet?

                       

                      FileMaker doesn't provide any support for versioning and upgrading. It doesn't support data separation. It doesn't support user migration. It doesn't support scaling up. You will have to implement strategies to deal with these issues as they arise.

                       

                      The thing that we call “The Data Separation Model” is a theory. I use it all the time and I’m happy with it but it is not really possible to separate the data. There are all sorts of things that are attached to the data, most importantly, user accounts, privilege sets and relationships. Managing the user accounts require scripts, so count them in too. You’ll probably have a few custom functions stored with the file because they are great for complex data transformations and you may add a custom menu. That’s the data file.

                       

                      When it comes to performance we simply cannot say that the data separation model performs better than a unified solution. The UI that you develop can have much more impact on performance than the data model. The schema you use in the relationship graph is another factor that is potentially more important than separation.

                       

                      malcolm

                      • 8. Re: Hosting sales solution for multiple clients - how to handle versioning?
                        slayden@msn.com

                        In this case, the driving questions are:

                         

                        in a SAAS model,

                        1. how do I implement upgrades and modifications without risking crashing everything?
                        2. How do I keep user entity client data completely siloed from each other?

                         

                        My plan after this dialogue:

                        1. AFTER the program reaches "it's done and works on one computer."'
                        2. duplicate the database
                        3. name one database_data
                        4. name two database_interfaceV1.0
                        5. designate database_data as a remote datasource for database_interface
                        6. Tediously rework all the relationship graph (can you say "intern"?) to poin-t to the remote datasource tables
                        7. have users use versions of database_interfaceV1.0 until I roll out upgrades WHILE I as developer am working on, say database_interfaceV1.1_developer
                        8. When I have upgrades to roll out, simply have users use the next interface as a total replacement.
                        9. HOPEFULLY, there will be very few database_data upgrades, as they appear to need to be kept separate

                         

                        Any problems with that plan?


                        The main thing I'm still struggling with is how to point to different databases, as I need each client entity to have its own completely siloed database_data.

                        In Coldfusion, I would simply query user database with if/then logic and set DSN = thisUsersDatabase, and every time a query ran, CF would point to that and only that database.

                        I assume infmPro what I need is similar:  Develop some kind of gateway app that every user logs into, them upon login, have the app point to the appropriate separated database for THAT client.

                        Is there a way to dynamically point to different databases based on login?

                        • 9. Re: Hosting sales solution for multiple clients - how to handle versioning?
                          CarlHenshall

                          The External Data Sources that link one FileMaker file to the others in the solution cannot be dynamic.  If you go down the route of having separate files for each customer then you will need an interface file for each customer as well as a data file for each customer.  The best way to do this is with the Developer Utilities you get in FM Pro Advanced (in the Tools menu).  That lets you rename a batch of files and it will update all the file references to point to the newly renamed files.  Lets say your solution is made up of two files - 'Interface.fmp12' and 'Data.fmp12'.  Using the Developer Utilities you can create a new set of files called 'Customer 1 Interface.fmp12' and 'Customer 1 Data.fmp12'.  The file references between the two files will be updated.  Your steps 2-6 above are therefore pretty painless.

                           

                          When you have v1.01 ready, you will have to go through the process of recreating each customers' files and update each one individually.  If the changes are only in the interface file then this can be as simple as replacing each interface file with the new version (do not include the version number in the file name as this will break the reference from the data file back to the interface file).  If there are changes in the data file then you have to go down the route of importing the data from the old data file into the new one.  As mentioned an another post, RefreshFM is a good tool to help with this.

                           

                          If your solution is very successful and you get lots of customers then this process could become very tedious and time consuming. It sounds like what you really need is a 'multi-tenant' solution. Unfortunately FileMaker doesn't have this functionality out of the box but you may be able to create it yourself. I haven't actually done this but it could be possible to have one interface file and one data file for all your customers.  The way you would separate one customer's data from another would be using record level security - every table would have a field that identifies which customer the record belongs to.  In your privilege sets, you set up the 'View' privilege to be 'limited'.  The calc tests the field against the current user.

                           

                          There are some issues to be aware of with this approach.  Depending on the number of records you could have performance issues as FileMaker has to run this calc on every record.  Also, you'd have to hide the toolbar as that shows the total number of records in the table.  You'd also have to create custom menus to override the default 'Show All Records' and 'Show Omitted' menu items otherwise you'll have users seeing records with <No Access> plastered all over them.  There could be other issues as well - these are off the top of my head.

                           

                          I am hopeful that WebDirect could open up the SAAS model with customers accessing our solutions via the browser. It is certainly a model I am interested in.  Multi-tenancy is an important part of the model. I also hope that future versions of FileMaker will enable us to have more users hitting one server with WebDirect than the current version does.

                           

                          Hope that helps.

                          Carl

                          • 10. Re: Hosting sales solution for multiple clients - how to handle versioning?
                            Malcolm

                            There are scripting issues that need to be dealt with when you split a file.

                             

                            There are variable scope issues when working with multiple files.

                             

                            There are data referencing issues when working with remote data sources in a UI.

                             

                             

                            There are some issues to be aware of with this approach.  Depending on the number of records you could have performance issues as FileMaker has to run this calc on every record.  Also, you'd have to hide the toolbar as that shows the total number of records in the table.  You'd also have to create custom menus to override the default 'Show All Records' and 'Show Omitted' menu items otherwise you'll have users seeing records with <No Access> plastered all over them.  There could be other issues as well - these are off the top of my head.

                             

                            These are the big issues. You can minimise the performance pain by reducing the interconnections in the relationship graph. That isn’t a simple cosmetic thing, there are flow-on effects. You have to change the way you think about presenting information and how users interact with it.

                             

                            We handled a project that had a number of research groups share the same database. It started as one group, then another piled in because they wanted the same thing, then a third, fourth and fifth. Assigning groups using Extended Privileges and controlling access via Access Privileges worked beautifully. We trialled it and everyone was happy. Once record counts in the main tables grew to more than a few thousand for each group the system started to slow down. We got a big improvement by creating a shadow tables for each research group. This meant that they faced thousands of records, not thousands out of tens of thousands. However, change comes with a price and the extra effort to modify the system to support the shadow tables wasn’t warranted. We could see ways in which we could make the system handle the load but it meant messing with fundamentals at the wrong stage in the build. It would have been much simpler to give each group their own instance of the database at the outset.

                             

                            malcolm