8 Replies Latest reply: May 15, 2014 10:03 AM by Data Kruncher RSS

    V10 Template Question

    gpeters _

      I have a report that has about 480 fields that I need to export into a database. Monarch seems to have a limit of 255 fields in the detail section of a template. Any suggestions on how to work around this?

       

      I have thought about creating multiple templates and splitting up the detail into multiple templates. I would then run the report against multiple templates. However, I will then need to combine the exported data later. If there is something I am missing that will allow me to do this all in one template in Monarch, that would be the preferred choice.

        • V10 Template Question
          Data Kruncher

          That is the limit of fields that can be defined in a single Monarch model, so you will require at least two models to extract your data. You'll find Monarch's limits documented in the online help under Technical Specifications.

           

          Are you certain that the destination database can support that many fields in a single table? Access cannot, but I don't think that Access is what you have in mind.

           

          I'd be more tempted to find a way to normalize the data or otherwise logically split the data into multiple tables employing a common key.

            • V10 Template Question
              Nick Osdale-Popa

              I have to agree with Data Kruncher on this. Any table that even borders on 100's of fields has some serious design issues.

                • V10 Template Question
                  gpeters _

                  Thanks for the replies. The report that I am trying to work with is a security report for our hosted system here. There are about 400 fields that pertain to the security of each individual. Originally Monarch tries to mark each field as one whole unit, but the problem with that is when a user does not have access to a function, a null is left in it's place which is not being handled correctly by my import. Could not think of another way to compare these fields without having each field be a seperate item. Below is a snapshot of the security fields. Each user needs to be compared against a template to verify they have the correct access for their job function.

                   

                  -


                  PRM--- -


                  CIS- -
                  DDA
                  - -
                  DDL
                  - -
                  SAV
                  ---

                  123456789012123456789012123456789012123456789012123456789012

                  2  1 1111111    F2 11F11 1                1  11111 1

                   

                  There are 5 areas shown above (top line of data) and each area has 12 functions (second line of data). The specific access for each function is on line 3. So This user has a 2 for PRM function 1. The problem I am having is that Monarch wishes to export PRM 1-12 as one data item, but the first 7 functions carry a null and that is not working for my compare.

                   

                  Any ideas?

                    • V10 Template Question
                      Data Kruncher

                      What would be a better representative character for null values? Would a zero be acceptable? Maybe an "N"?

                        • V10 Template Question
                          gpeters _

                          Yes, I suppose one of those would work. How would I do that though?

                            • V10 Template Question
                              Data Kruncher

                              I don't envy you with this one at all. It'll a fair bit of effort, but remember that you only need to model it once. If that's any consolation.

                               

                              You'll do yourself a favor if you change your modelling approach a bit though. You mentioned Monarch is export the groups as a single field. You'll need to define each individual character in the group as a distinct field.

                               

                              Because you've got alpha characters and numerics, I'd treat them all as character.

                               

                              Then, to convert those empty strings (they're not true nulls) to the zero characters you need.

                               

                              For each and every field, you probably need to check for, and convert empty strings. So you'll need a calculated field for every extracted field.

                               

                              Each calculated field will be similar to:

                              If(PMR1="","0",PMR1)[/CODE]

                               

                              You can use Monarch duplicate feature to help speed things along somewhat.

                               

                              I cannot dream up a better solution for your challenge. Anybody else want to play?

                                • V10 Template Question
                                  gpeters _

                                  OK, thanks Kruncher. Just to make sure I am following you though, you are suggesting that I create multiple templates then and run the report through each? I ask because if I am following what you are suggestnig I need to create a seperate field for each item (PRm function 1 would be 1 PRM function 2 would be another, etc.). If so, this goes back to my original problem of there not being enough fields in one template (I run up against the 255 max).

                                   

                                  Just trying to make sure I understand this completely before I try to tackle this.

                                    • V10 Template Question
                                      Data Kruncher

                                      That's correct. I might go so far as creating a separate model for each logical group, but that might be a bit much. It would be a nice clear nearly self-documenting process though, in the end.

                                       

                                      You could have models and projects named like:

                                       

                                      User Security - PRM

                                      User Security - CIS

                                      User Security - DDA

                                      etc.

                                      /LIST