Greetings, fellow data analysts!
At the end of 1986, the German TV network ARD accidentally broadcast Helmut Kohl’s New Year’s address from 1985. This became a major public embarrassment both for the network which switched the tapes as well as for the former Chancellor because it turned out that he re-used the same rhetoric and messages in both speeches. In a direct comparison (going back to a favorite topic of ours), that came across as insincere.
Apparently, producing and communicating current material and appropriate messages can be challenging – as far as content and technical aspects are concerned. As BI users, we rarely lack current data. But it is also important to understand when we consider something to be current and which time frame we want to present. The current day? The last full month? That all depends. That’s why it is always important to monitor if something is current ourselves. Fortunately, DeltaMaster offers sophisticated options to make or keep our reports precisely up to date. We will introduce these options and explain how this works in the background in our current edition of DeltaMaster clicks!.
Best regards,
Your Bissantz & Company Team
One of the most important objectives in implementing Business Intelligence solutions is creating a current, common base of data for making decisions. With a little organization and the help of technology, companies can ensure that their data warehouse is regularly fed with fresh data from their ERP, CRM, and other operative source systems. And what about reports and analyses, you ask? How current must this information be? Especially in the case of standard reporting, you don’t always need to have up-to-the-minute data. Oftentimes, information from the last monthly close will suffice. On the other hand, you want to be able to rely on the fact that this data is correct and complete. That is why updating reports, for example, on a weekly or monthly basis is one of the important (but not exactly favorite) tasks for controllers. Fortunately, DeltaMaster is there to help.
As so often in business, you should assess the costs and benefits of updating reports. Calculating a report always requires a bit of time. If the reports are simple, it might be so minimal that you don’t even realize it. As reports get more comprehensive or complex, however, they will take longer. In general, you achieve the fastest response times when a report is already calculated and the database does not have to run the query at the exact moment when the report is opened and rendered on the screen. That’s why DeltaMaster offers several sophisticated options to influence how it updates your reports.
Reusing known values with DeltaMaster cache
With this trade-off between current information and query times, DeltaMaster uses a comprehensive yet simple approach:
- If DeltaMaster has already calculated a report in the current program session (i.e. after you have started DeltaMaster), it will render the calculated values the next time it displays them. These will either be the results that were generated in the session, for example, the first or previous view of the repor,t or saved values that DeltaMaster has loaded from the analysis session (.das file).
- If DeltaMaster has not already calculated a report, it starts with the necessary database queries and calculations at the very moment when it should display the report. This also occurs when we change the view, e.g. select a month which we have not yet used in the current session (i.e. since the start of the program) or if we change the report structure, for example, by editing the Axis definition or selecting another measure in an analysis method. DeltaMaster notes the results so that it can present them without running a new database query if the user requests the same view some time later.
In other words, DeltaMaster has a cache function. It saves the calculated values so that it can reuse and immediately display them if the same query is requested at a later time.
Updating reports automatically
The cache in DeltaMaster improves query times and ensures an overall smoother usability. This is very helpful when the data doesn’t change daily or even several times a day which is the case with monthly reports. These types of briefing books are updated after the monthly close and are valid for several weeks. In this time frame, you will probably want to slice, dice, and analyze these numbers from multiple dimensions using all available options. DeltaMaster, however, does not need to retrieve the data from the server every time it displays a report. (If you wish to do so, you can at any time by hitting the F9 key. More on that below.)
In other cases, you might want to ensure that DeltaMaster always uses the most recent data. DeltaMaster helps you do that with two different Options (Extras menu) which ensure that your reports update themselves automatically.
- On the General tab you can determine if DeltaMaster should Savereports in analysis sessions (.das files) without data values. This setting applies to the current analysis session (.das file). If the option is activated (i.e. the box is checked), DeltaMaster will save the definitions and the properties of reports but not the numbers in the .das file. In this case, you cannot use these types of saved files in Offline reader. If you open this type of analysis session, DeltaMaster will only display uncalculated reports and immediately run the respective database queries and calculations from scratch – at least the first time that the report is displayed. Afterwards, the cache mechanism described above will apply in case you have not deactivated it as described in the next point below.
- On the System tab you can determine if you want to Cache results or not. This setting also applies to the current analysis file (.das or .dam). This option, which is activated in the default setting, ensures that query and calculation results are stored internally as alluded to above. If you deactivate this option, DeltaMaster will discard all results before it displays them and calculate them anew. Once again, this happens each time and not just the first time. This means that every time you change the report, cockpit, analysis or view, DeltaMaster will recalculate the respective queries. This option is designed for planning applications in which multiple users are writing back to the same database simultaneously. Removing cache in analysis and reporting applications, however, slows down your progress because the system often needs to run side calculations.
Both options ensure that DeltaMaster updates reports from the database before it displays them – either the first time or every time. These options differ based on how current the data is and on the desired response times. The first option (analysis sessions without data values) generally suffices for analysis and reporting applications because it ensures that your data is very current and reduces query response times. The second option (deactivating cache) ensures up-to-the-minute accuracy for your data at the expense of speed.
Add fresh data to reports by hitting the F9 key
You want to be absolutely sure that you have the most current numbers from your database? Simply hit the F9 key. When you do this, DeltaMaster ‘forgets’ the results of the last database queries and then calculates and refreshes the currently displayed report, cockpit, or analysis. When you use F9 to update reports and cockpits, DeltaMaster will automatically run the queries and calculations as well. For analyses in Miner mode, click on Calculate after you have done an F9 refresh.
You might already know the F9 key from Microsoft Excel where it also recalculates your spreadsheet. In DeltaMaster you can use this function in all user levels from Reader to Miner. In Offline reader, however, F9 does not work because as the name suggests it does not maintain a connection to the database and only works with pre-calculated values that are stored in the analysis session (.das file). To switch from Offline to Online reader where you can use F9 to update your data, simply click on Connect in the status bar (see DeltaMaster clicks! 7/2005 for more information).
Switching reports to another point in time
Hitting the F9 key updates your reports in the sense that they contain the most current data from your database. You are not, however, changing the reference time period. A report for August 2010 will once again show the data from August 2010; DeltaMaster will simply reload it from the database. For monthly reports, however, you also want to change the valid time period, for example, from August 2010 to September 2010. In DeltaMaster this is called changing the View – and this, too, can be done in several ways.
Basically, you can open the respective report (e.g. sales statistics), select the desired month in the View window, and then Save the report.
If you are dealing with more than one report as in most real-world scenarios, you can simplify and speed up this process: You simply group the respective reports in a folder of the Briefing book in Pivotizer, Analyzer and Miner levels, and update the entire folder (context menu, I want to… menu) at once.
When you select this function, DeltaMaster will open the dialog box shown on your right. You can execute this task using the Change reports in report folder to option – in this case, in the time dimension (‘Period’) which you can Select with the dimension browser.
Please note the two options on the bottom part of the dialog box. If they are activated, DeltaMaster will Clear report action titles and comments. The action titles and comments are designed so that the report author can create individual, personal comments referring to a specific report. If the view changes, for example, because a report has been updated, the report will generally contain other data and the action titles and comments that refer to the data will most likely no longer be valid. For this reason, these two options are activated in the default setting. If you want to keep your action titles and comments, however, simply deactivate both options. For helpful hints on how to use action titles, please refer to DeltaMaster clicks! 10/2009.
You can also use the nearby Keep existing reports option to copy reports and report folders. DeltaMaster will store the updated reports and folders additionally in the briefing book. Your previous reports, the ‘copy templates’, will remain untouched.
When you click on Update, DeltaMaster will recalculate the reports.
In Viewer you can update reports folder by folder from the dialog box but only in the sense of a new calculation with freshly loaded data and not as a change in the view.
Viewer, however, does offer support in changing the view. If you select a folder (and not a report) in the briefing book and then select the desired member (e.g. a new month) in the View window, this member selection will be inherited to all reports and subfolders. This way, you can quickly do an update in the sense of switching your perspective on data even in multiple dimensions at a time. This special usability is only supported in Viewer mode.
Using dynamic naming for folders and reports
Here’s a tip for the ‘periodicals’ in your reporting. If you note the respective time frame in the names of report folders or reports, you can find them more easily in the briefing book and your consumers can quickly see at a glance what the report is referencing. Optimally, you should use view variables (see DeltaMaster clicks! 11/2009) – in this case ‘{cp}’ which stands for ‘current period’.
In place of this expression, DeltaMaster will automatically display the selected member in the respective folder or report in the briefing book, window titles and so on. This way, the name is automatically updated when you change the view. How practical!
Updates by the masses with ReportServer
If you regularly want to supply a large number of report consumers with current reports, for example, so that all outside sales staff can access their monthly sales statistics, it makes sense to use ReportServer (see DeltaMaster clicks! 8/2005). In the Report update field you can select how ReportServer should change the reports located in the Report source (e.g. analysis session, .das file). The successive report calculations will be parameterized for each individual recipient in the Report generator field (not shown in the screenshot above).
These individual analysis sessions that ReportServer generates and sends via e-mail or stores on a file server are always fully calculated and saved including the data values. As a result, the recipients do not need to update them from the database.
In the screenshot above, you can see an example for a set time when the reports should be updated. You can make this selection dynamically – and, therefore, fully automatically – using the SQL update command setting in the job definition or also with a dynamic MDX expression. Your personal DeltaMaster account manager can help you with the configuration.
What does ‘current’ mean?
Every now and then, someone will ask us if they can configure DeltaMaster so that it displays the current period as soon as someone opens an analysis session. The answer is yes. But this seemingly simple question is more complicated than it appears. When you take a closer look, it is not that easy to create an across-the-board definition for what is ‘current’ and what is not. The computer’s system date, for example, would not be the best selection criterion because a data warehouse rarely contains many (if any) data for the current calendar date. And DeltaMaster cannot ensure that the data in a data warehouse is current, complete or correct. Even the time stamp of the last processing of an OLAP cube is only an indication that this has taken place. (Technically adept users using Microsoft Analysis Services can access this information in DeltaMaster by using the function ‘ASSP.CubeLastProcessedDate()’ within a user-defined measure. DeltaMaster will also display the value in the Model browser if available.)
And nevertheless, as mentioned above, ReportServer is able to automatically generate analysis sessions with a pre-selected time period which, in turn, is dynamically determined by the SQL update command or the above mentioned MDX expression and based on several server-side criteria. Simply contact your DeltaMaster account manager to learn how you can implement this type of custom pre-configuration.
Questions? Comments?
Just contact your Bissantz team for more information.
