The idea of a data mart is to collect specific information to report on. The hardest part about creating a data mart is determining which fields are necessary to achieve the appropriate end result. With that in mind, many users will select every field that may potentially be used. This often results in longer processing times because we are brining over fields that will never be used.
It is recommended to come up with a plan before you start designing your data mart. It is easier to add fields to a data mart than it is to take them away. As you come up with your plan, keep the following things in mind.
Note: The following are some basic ideas that can help increase the performance of your data mart:
- Avoid using Summary Information fields in the Export Definition. You can use smart fields that will process much faster in The Information Edge than in The Raiser's Edge. Use the sum smart field to get total amounts, use the count smart field to get counts. Use the Filter tab to include specific record types.
- Roll up data onto your parent table if a 1:1 relationship exists.
For example if you only need Home phone and email on a Constituent Record, you can have these fields put on the Constituent table, rather then having a phone table created. To do this, select Phone, and in the criteria, select 1 for number of phones to export and then select the appropriate phone type. Repeat this process for the next phone type. The result will be that these phones will be on the Constituent table and there will not be a need to create a smart field to move them later.
- Use a query for testing. Before you start exporting all of your data into The Information Edge, use a subset of your records. You can design your smart fields, pivot tables, Crystal Reports with this subset of data. Then when you have everything they way you want it, you can do a full refresh with a larger set of data. The result is saving processing time and your time by working with a smaller set of data and not having to wait for smart fields to process to see if something will work or not.
- Set up procedures for accessing the data mart. Allow only a few users the ability to create smart fields. If you have 5 people creating smart fields, you could have 5 different smart fields that do the same thing and taking 5 times as long to process.
- Use Smart Field options. Enabled allows you to turn off the data mart so it will not be refreshed. Process incrementally will process only the rows of data that have changed saving time on the refresh of the data mart.
- Break your Data Mart into subject areas. Particularly if you have data that needs to be updated at varying frequencies.
For example you may have a Donor/Gift Analysis Data Mart that is updated every night, but an Event Data Mart that is updated every week. You can usually restrict your data mart to only include constituents that have data against them. For example, create a dynamic constituent query which checks in Event Participation if Event ID is present. This will only then return Constituents with event info against them