Generic filters
Exact matches only
Search in title
Search in excerpt
Search in content

Automatically publishing reports and database extracts with ReportServer

PDF Download

Greetings, fellow data analysts!

Many of you already know that DeltaMaster ReportServer offers reliable, automated assistance in updating, assembling, and exporting masses of reports. But did you know that it is an expert at creating excerpts as well? You can also use ReportServer to extract smaller sections of large databases and provide them as separate cubes. This is relevant for the so-called DM2GO files, which we presented in the last edition of clicks!: They combine a DeltaMaster analysis session and the respective multidimensional data into a single file, so that you can use them to analyze your data all the way up to Miner level without needing to connect to a database server or corporate network. And when you need these types of files for each outside sales rep, product manager, branch director, cost-center manager, etc., then look no further – ReportServer is ready to help.

Best regards
Your Bissantz & Company

In DeltaMaster clicks! 07/2013, we described how you can unite cockpits, analyses, and reports with their corresponding data, for example, so that you can work without being connected to your corporate network. This “DeltaMaster to Go” (DM2GO) format combines an analysis session and a local cube in a single file. This cube contains a data warehouse extract that you can query apart from the database server. With DM2GO files, you can just take the “slice” of the database that you need for certain application scenarios or users so that they can analyze it using the full flexibility offered in Viewer, Pivotizer, Analyzer, or even Miner levels.

ReportServer creates these DM2GO files automatically. Each outside sales rep, product manager, branch director, or cost-center manager, therefore, can receive a personalized slice and appropriate analysis session that supports all of the analytic capabilities. One very useful feature is that ReportServer can iterate members successively. As a result, you can use it to set the view that you wish to create in the analysis as well as the individual cube slices.

Defining jobs in ReportServer

To generate DM2GO files with ReportServer, select this Report Format in the job definition. You can combine it with the Distribution Types “file”, “mail”, and “ftp”. In the screenshot on your right, a local path is listed as the Address. This path contains the ReportServer variable “@IMN” (i.e. Iterator Member Name). When ReportServer runs the job, it will replace this variable with the name of the iterator member so that the output files receive different names. For more information, please read DeltaMaster clicks! 11/2011. If DM2GO format is set for the selected job, ReportServer will add three additional columns – the Filter Set in the Jobs window as well as the Filter Mode in the Report Update and Report Generator windows. You can use these fields to define how DeltaMaster should generate cube slices.

Creating custom cubes with Filter Sets

The Filter Set field contains all filter sets that were defined with CubeWizard in the underlying analysis session (see DeltaMaster clicks! 07/2013 for additional information).

If you select a filter set in the job definition, only the specified components of the model will be saved to the DM2GO file. If the field remains empty, ReportServer will transfer the entire cube of the Report Source. (You can modify the slicing at a later time using the Filter Mode. See below for more information).

When you work with Filter sets in DeltaMaster, please take the following information into consideration. If you Create [an] OLAP Database (Model menu) with the Cube­Wizard (Model menu), you can define which components of the analysis model should be transferred to the cubes that you are generating. Delta­Master saves this configuration as a Filter set in the analysis session. In the selection field of the dialog box on your right, you can access and administer different filter sets. By opening the context menu of the selection field, you can create a Filter set, Rename the current one, or Duplicate it. DeltaMaster automatically saves this selection to the current filter set. In order to use a filter set in ReportServer, all you need to do is define a filter set in the dialog box (i.e. not actually create the OLAP database).

You can use the Filter set to limit the scope of the slices that you are creating with the job. These modifications will then apply to all DM2GO files that the job generates. In the screenshot above, for example, we removed two measure groups and the companies as well as limited the time dimension to two quarters. The reasons were twofold. First, we wanted to ensure that the recipients could only work in this defined space. Secondly, these tight boundaries keep the total data to a minimum, and working with smaller amounts of data is faster than with larger ones.

Shape your slices even further with filter mode

You can use the Report Update and Report Generator options to make additional changes to the slice within the job. The following section will explain how to edit cubes in Report Generator. You can, however, use these exact same steps to change them using the Report Update option as well.

In the Report Generator, you can define the members for which ReportServer should create separate output files. ReportServer processes these so-called iterator members in a loop, one after the other. In this case, ReportServer will successively change the view that is saved in the Report Source to each of these members, recalculate the report source with this changed view, and process the result as a separate file (e.g. send as an e-mail or save as a file to a server). In this manner, many individually customized briefing books, Office documents, PDF files, etc. can all be generated from one single Report Source. This makes standard reporting much more efficient.

When creating DM2GO files, Report Generator works the exact same way – with one difference: In addition to using iterator members to adjust the view in the analysis session, you can also use them to modify (i.e. filter) the cube.

You can define if (and how) ReportServer uses iterator members as a filter for the cube in the Filter Mode. As mentioned previously, this field will only appear in the Report Generator (and the Report Update) when you edit a job in DM2GO format. Here you can choose from five filter modes.

The screenshots on the following pages illustrate how these different modes work. Here we have applied each filter mode to the customer dimension from our “Chair” reference application. In each case, we filtered the same member “South 2 (Greater London)”. For comparison: The Dimension Browser shows how the dimension was set up in the report source before the ReportServer processed it.

Filter mode Description Effect
SliceIgnore The iterator member is not used to filter the cube. It only applies to the reports in the analysis session that is contained in the DM2GO file, but not the cube file. As a result, the dimension structure is the same as the initial one.
SliceFilter All hierarchy levels above and below the iterator member stay the same. The iterator member, however, is the only member on its level that persists; all other members are removed.
SliceTop The levels above the iterator member (including the “All” member) are removed. The level of the iterator member and the levels below it persist. The iterator member, however, is the only member on its level that persists; all other members are removed.
SliceBottom  The levels below the iterator member are removed. The level of the member as well as the ones above it up to and including the “All” member persist. The iterator member, however, is the only member on its level that persists; all other members are removed.
SliceExclusive All levels except for the level of the iterator member will be removed. The iterator member, however, is the only member on its level that persists; all other members are removed.

Prerequisites and licensing

In order to automatically create DM2GO files with ReportServer, the Report Source must be based on Microsoft Analysis Services; it is irrelevant whether the server database or a local cube file is the source. As ReportServer must rebuild the cubes, a valid connection to the relational data source of the Analysis Services database or the local cube file is required. In addition, the same technical prerequisites that were outlined in DeltaMaster clicks! 07/2013 apply here as well. In particular, the database access component “Microsoft Analysis Services OLE DB Provider for SQL Server” must be installed on both the computer on which ReportServer generates DM2GO files as well as on those where users will open these files with DeltaMaster.

Furthermore, you will need a special license (i.e. “ReportServer-DM2GO” or “DM-RPSC” for short) to generate DM2GO files with ReportServer. If you wish to test this functionality, we would be happy provide you a trial license. A separate CubeWizard license is required if you want to create filter sets, but not to run ReportServer jobs containing filter sets.

Security concerns

In local cubes as well as DM2GO files, the authorization concepts and other security measures within the database server are invalid. Anyone who gets a hold of a CUB or DM2GO file can view the data in it with DeltaMaster without any further obstacles. As a result, people using these files should handle them with caution – just as if they were sensitive PDF or Office files with no password protection.

Questions? Comments?

Just contact your Bissantz team for more information.