Greetings, fellow data analysts!
When you take a trip, you can still count, measure, and analyze your numbers – provided that you pack your bag (and laptop) with DeltaMaster, a few reports, cockpits, and the respective data. Things that are practical, compact, and lightweight are nice for travelling because you have to make do with less and, essentially, fend for yourself. The same applies to data analysis. When you are out under cloudless skies, it is sometimes hard to connect to the database servers in your corporate data center. Nevertheless, you can still work with the analytic functions in DeltaMaster – all the way up to Miner mode. You simply have to bundle your analysis session and the relevant part of the database into a package which we call “DeltaMaster to go” or DM2GO for short. This edition of clicks! will explain how. And one last thing…think of it as a list to pack for your next trip and not something to read on it.
Your Bissantz & Company team
What can you do if you want to examine and interactively analyze your data but can’t connect to your company’s database, for example, on trips? Where can you get the data you need?
You could view calculated reports in DeltaMaster using Offline Reader mode (see DeltaMaster clicks! 07/2005). For usage in this mode, you can save reports and the respective data to an analysis session (DAS file) so that you can view them on screen or present them in presentation mode. This user mode, however, is not suited for interactive analysis.
In Viewer, Pivotizer, Analyzer, and Miner modes, in contrast, you need an entire database or an extract thereof – and not just the values for a previously defined view. In this case, you can use local cubes. This concept from Microsoft Analysis Services allows you to access multidimensional data from a file without server services. The most important limitation of a cube file in comparison to a server is that a cube file does not support multiple users. But that isn’t an issue when it is intended for decentralized, isolated use anyway. DeltaMaster can access cube files as a data source.
In other words, you can take everything that you need to conduct interactive data analysis with DeltaMaster in two files: a cube file (.CUB) containing the data, and the relevant cockpits, reports, measure definitions, etc. as an analysis session (.DAS). To make working with these two files even simpler, DeltaMaster also offers a special format called “DeltaMaster to go” (DM2GO). This format compresses both files and saves them as a single, encrypted file. DeltaMaster can then automatically connect the analysis session to the cube file.
In this edition of clicks!, we will explain how you can work with DM2GO files as well as generate the cube files that you will need as the data source.
Opening an analysis session from a DM2GO file
In DeltaMaster, there is very little difference between a DM2GO file and a DAS file.
The dialog box that you use to open an analysis session will display both DAS and DM2GO files. Using the filter on the bottom-right part of the dialog box, you can limit the list to only show DM2GO files.
The Portal also displays the recently used DM2GO files including their file extension so that you can easily distinguish them from DAS files.
When you open a DM2GO file, DeltaMaster will extract the two files into a temporary directory. In the case of very large files, that can take some time – similar to a very large ZIP archive. DeltaMaster will display the status in the status bar on the bottom-left side.
If you open a DM2GO file in Reader mode, DeltaMaster will proceed as defined in the settings under Options (Extras menu) on the Mode tab. If the Work Offline option is activated, DeltaMaster will not connect to the cube file initially.
You can do that any time later, however, by clicking on the Login link on the right side of the status bar.
In Viewer, Pivotizer, Analyzer, and Miner modes as well as when logging in in Reader mode, you will see the familiar Login to OLAP Database dialog box. This already contains the setting that a cube file should be opened and includes the name of the cube file. DeltaMaster has automatically determined the file name – it is the file that was extracted when the DM2GO file was opened in the temporary directory. This is much easier than working with separate DAS and CUB files. Since DeltaMaster already knows where it can find the data for the analysis session, the user doesn’t have to search for data or set up any directories regardless of the storage location.
When you activate the respective check box and save the analysis session, DeltaMaster will Automatically connect using this information from this point onward. When a DM2GO file is configured this way, DeltaMaster will simply locate the cube file and not display the login dialog box – which simplifies things even more.
Now you can work with the analysis session that you have just opened as you normally would. In Viewer, you can switch the view to a different time period, country, or color. In Pivotizer, you can create new reports. In Analyzer, you can break down variances with the pivot navigation. In Miner, you can apply multiple analytic methods and use the threaded analysis technology. And you can do all of that with just the data that you have in the DM2GO file without needing a database server in the background.
Saving an analysis session
If you are working in a mode from Viewer to Miner, you can save any changes in the analysis session to the DM2GO file (Ctrl+S or File menu). In this case, DeltaMaster will first save the DAS file in the temporary directory and then in the DM2GO file. As was the case with opening the DM2GO file, compressing and encrypting the DAS file and cube file can take time if the two are very large. DeltaMaster will display the progress on the bottom-left side of the status bar.
In the File menu under Save as, you can save the current analysis session with a different name and change the File Type as well. This means that you can save an analysis session including the cube file upon which it is based as a DM2GO file and, vice versa, save a DM2GO file as a simple DAS file separated from the cube file. You can only select the DM2GO format, however, when the analysis session is based on a cube file – in other words, it was already loaded from a DM2GO file or based on an independent cube file. In the case of server-based databases, DeltaMaster will not offer DM2GO as a File Type.
And that brings us to the question: How can you create a DM2GO file with data and reports for use on the road?
Creating cube files for DM2GO files
A DM2GO file contains two separate parts: a cube file and an analysis session.
In Pivotizer, Analyzer, and Miner modes, you can Create an OLAP Database (Model menu) with DeltaMaster CubeWizard, an additional component that is integrated into DeltaMaster (see DeltaMaster clicks! 01/2013 for a brief overview). CubeWizard requires a separate license. If you wish to test it, please contact us to receive a trial.
You can use CubeWizard to create Analysis Services cubes, either as a server database or a cube file. The source can be a relational database, e.g. from DeltaMaster TableWizard, or a server database in Analysis Services.
The CubeWizard appears as a dialog box containing four sections. At the top, you state if you want to create a server database or a cube file. Next, you specify either the server and database or the cube file that you wish to create. You can then select various options for creating the cube. For the purpose of preparing a DM2GO file, you can simply keep the default settings.
Creating subsets from the analysis model
In the main part of the dialog box, you can specify which components you want to transfer from the analysis model into the cube that you are creating. This means that you can just copy a subset of the underlying database. That is especially interesting for this scenario because you can define and “pack” specific extracts of the database.
In the example on your right, for example, the time dimension is limited to two quarters, and the “Sales Group” and “Company” dimensions as well as “Market Data” and “Balance Sheet Ratios” are deactivated. This so-called cube slicing, i.e. the process of “trimming” the database that you are about to create, has two advantages:
- It reduces the volume of data that you save and use in a cube or DM2GO file. This makes the file easier to handle and accelerates processing as well.
- It also allows you to create a personal slice of the entire cube for specific users. For example, you could extract the data for the region “South” into a slice for the respective sales director. The sales director could then analyze this cube slice – even up to Miner mode with the respective access rights – and still only see the part of the total database that is relevant for his or her work. If desired, you can even automate this step with DeltaMaster ReportServer. We will explain how in a later edition of DeltaMaster clicks!.
By ticking the check boxes, you can include or exclude dimensions, hierarchies, levels, measure groups, and even individual measures. If you wish to select individual members such as a certain region, simply double click on the “Members” item that is displayed in every dimension. You can then select the desired members using the Dimension Browser.
DeltaMaster will then save the respective selection as a Filter Set in the analysis session. Using the selection field, you can then open and administer various filter sets. From the context menu of the selection field, you can Create [a new] Filter Set, Rename the current one, or Duplicate it. DeltaMaster will automatically save any changes in the selection to the current filter set.
When you Create the cube, DeltaMaster will log this process in the bottom section of the dialog box.
Analysis session for the cube file
Once you have created the cube file, you have also generated the underlying data for the DM2GO file. For this file, you will now need an analysis session containing measure definitions, cockpits, reports, etc. You can create a new one – but it is easier to simply use and edit the analysis session which you used to build the cube file. Since this analysis session is based on the same structures as the cube file that was derived from it, DeltaMaster can reuse many of its components (e.g. calculated members, user-defined measures, named sets, cockpits, reports, and analyses). Reusing an analysis session, however, has its limitations as well, especially when it comes to structural changes in the generated cube file. If you have removed objects or filtered dimensions, you may only be able to use parts of the original analysis session. If the dimension “Company” wasn’t included into the cube file, for example, the reports containing this dimension will not work.
You can easily switch the original analysis session to the newly generated cube file. Simply open the DAS file in the Maintenance Dialog using the respective entry in the context menu of the analysis session on the Portal page or by selecting Maintenance in the Model menu.
In the maintenance dialog box, you can edit properties that cannot be changed during a current session. These fields are highlighted in yellow. Relevant for this scenario is the Server/Name field, which lists the name of the database server upon which the analysis session is based. In place of the server name, you can enter a cube file – in this case, the cube file that you have just generated.
When you close the maintenance dialog box, DeltaMaster will ask if you wish to save the analysis session under a different name so that you do not accidently overwrite the file that you have been using up until this point.
Now you have a DAS file that is connected to the cube file that you created to take with you. As the final step, you now have to combine the two files – and that is easily done. Simply open the DAS file, save it using a new name, and change the File Type to DM2GO as described above.
When you open a DM2GO file, DeltaMaster will extract the respective CUB and DAS file into a temporary directory as defined in the Options (Extras menu) on the Folder tab. The default setting is the temporary folder in Windows for application data of the current users (in Windows 7: “%LOCALAPPDATA%”). In this directory, DeltaMaster will create a subfolder “DM2GO” where it will save the files. When you close the analysis session, DeltaMaster will automatically delete the temporary files.
In local cubes as well as DM2GO files, the authorization concepts and other security measures within the database server are invalid. Anyone who gets ahold 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.
In order to write and read local cube files, DeltaMaster requires Microsoft Analysis Services OLE DB Provider for SQL Server. This database access component must be installed on each computer where users generate or open cube files (directly or in DM2GO format). This component is automatically installed with Microsoft SQL Server Analysis Services and can be installed separately as well. Microsoft offers this component at no cost as part of its SQL Server Feature Pack.
The following list contains the download links for version 2008 (R2), which is compatible with the version of ADOMD.NET that is currently installed with DeltaMaster. The actual download packages are located on the overview page under the section “Install Instructions”.
- Microsoft SQL Server 2008 R2 Feature Pack, English
Microsoft Analysis Services OLE DB Provider for Microsoft SQL Server 2008 R2:
x86 (SQLServer2008_ASOLEDB10.msi): go.microsoft.com/fwlink/?LinkID=188444&clcid=0x409
x64 (SQLServer2008_ASOLEDB10.msi): go.microsoft.com/fwlink/?LinkID=188445&clcid=0x409
- Microsoft SQL Server 2008 R2 Feature Pack, German
Microsoft Analysis Services OLE DB-Anbieter für Microsoft SQL Server 2008 R2:
x86-Paket (SQLServer2008_ASOLEDB10.msi): go.microsoft.com/fwlink/?LinkID=188444&clcid=0x407
x64-Paket (SQLServer2008_ASOLEDB10.msi): go.microsoft.com/fwlink/?LinkID=188445&clcid=0x407
If this component is missing, DeltaMaster will display a message upon the attempt to open a DM2GO file.
Just contact your Bissantz team for more information.