one of the more interesting aspects of Studio, compared to EIS (Essbase Integration Services) and other building methodologies, is that it abstracts the complexity of data access and treats both relational and text-based sources in much the same manner. note
that I did not state that it treats them in the same manner, it does not. Studio provides a much more robust set of capabilities when leveraging relational sources. however, when first exploring data during prototyping for a deployment, it is important to simply get the data. to that end, it is just as advantageous to get access to data in a flat file as it is in a relational connection.
3.3.1 You Do Not Need 39 Dimensions: Working with Text Sources
It is worth repeating, Studio does not provide the same breath of capability when ing with text sources as it does when working with relational. For example, when work-ing with text sources, you do not have the ability to create joins and, thereby, create dimensions by combining multiple text files. The only exception is performing a recur-sive join on a text file to enable a parent/child build. nonetheless, Studio does provide a robust means of both introspecting and modeling with text.
A question often asked is whether or not it is worth using Studio if you are leveraging flat files? As an experienced Essbase developer, there is no doubt that you are adept at working with Load rules (whether in prototyping or production). however, depending on the nature of the file and the dimensional requirements of the business, Studio can be significantly easier. This is easily illustrated by telling a story about the 39 dimensions.
“We would like 39 dimensions,” the client said with great confidence. Their current BI deployment had 39 dimensions, so they felt that they Essbase model should have 39 dimensions as well. It is important to note, at this point that they were talking about base dimensions, not attributes. They were convinced that Essbase was the proper solu-tion, but they also were convinced that they needed to see what 39 dimensions with their data would look like. The sample source data was provided in one 3-gb file with 70 columns as seen in table 3.1.
Looking at the names of the columns in the file, it is obvious to a true online ana-lytical processing (oLAP) person that much of this information is either not relevant to the solution or can be leveraged in other ways (such as with an attribute or uDA [user-defined attribute]). The client, being newer to Essbase had to be convinced. In this situa-tion, you can either sit through multiple meetings explaining the dimensional options or you can build the prototype the client is asking for along with a prototype of your own design. In leveraging Studio, our team was able to build the 39 dimensions in a single day from the test file and a “more proper” prototype in another day. to have done the same thing with the Data Prep Editor would have taken the better part of a week (bar-ring major error). to have retrofitted the file into a relational model would have involved three days of It (Information technology) requests and another few days of setting of the system. Studio’s flat file functionality allowed for quick conversations and modeling that cut through the clutter and came up with a good business solution for long-term deployment. In short, you should absolutely consider leveraging Studio for flat files.
3.3.1.1 Importance of Server.Properties While it may seem remedial, it is important to remember that all text sources for Studio need to reside in a single root directory. This directory is determined by the Studio server.properties file. This master directory can contain subdirectories, and it need not be on the same physical volume as Studio. It sim-ply denotes a consistent location for data file location (not dissimilar from a system DSn (data source name) pointing to and setting properties for a relational source). Within the file, the specific property line denoting the data file location is server.datafile.dir as shown in Figure 3.1.
In the more recent releases of the oracle EPm System, this file can be found in the following directory:
…Middleware\user_projects\epmsystem1\BPMS\bpms1\bin Note: Any change to this file requires restart of the Studio service.
3.3.1.2 Text Options ultimately, the selections you make when setting up a text file connection are similar to setting up your data source properties in the Data Prep Editor (in Essbase Administration Services [EAS]). What is often confusing about setting up a text connection in Studio (aside from the file location) is the multiple means of setting
Table 3.1 Source Columns
up the properties. When you first initiate the data source connection, you have the option of specifying standard properties (such as delimiter, header lines to skip, etc.) as shown in Figure 3.2.
however, after selecting these options and clicking Next, you are presented with both the list of data files and the same options. The options on the first screen are sim-ply a set of default options. These become a starting point for the options on the sec-ond screen. you can, on the secsec-ond screen, set individual properties per data file. It is important to remember that once you select the data source options and create the data source you cannot change these options for a file. Although you have the option of incrementally updating a text data source (as shown in Figure 3.3), the options to com-mit data source changes on incremental update are disabled. In short, if you forget to
Figure 3.1 Server.properties.
Figure 3.2 Connection wizard.
ding the Dirt: Tricks for Rapid Prototyping with Essbase Studio • 77
Figure 3.3 Incremental property setting.
specify the properties for a file, you need to delete the text file from the data source and incrementally add it back in with the proper settings.
3.3.1.3 Think Ahead; Select a Number Just as it is easier to think ahead and specify the file properties correctly the first time, you also should consider the eventual creation of the cube schema. A cube schema requires the existence of a numeric field in your data source. If you do not have a numeric field, you can define one when creating the data source. The question then arises: Why you would have a source without a numeric field?
The answer is quite simple. Depending on the nature of the data you are given, there may or may not be a numeric field present. take for example our 39 dimension, 70- column example. While there are columns in the text file that are most definitely relational, the client extracted that data in a hurry and wrote the 3-gb file as all text.
Later in this chapter, I will discuss a trick for deploying a cube from Studio lever-aging metadata only, but the most common approach is to ensure you have a numeric field up front. When setting most file properties if you do not initially select a given property, you can delete the file and incrementally update the data source, add the file back, and specify the desired settings. however, this is not true for changing column properties.
If you neglect to specify the desired data type you cannot change this within the data source. In order to read the text file with the new column properties, you need to add a completely new text data source, select the desired file, and then set the properties.
3.3.2 To Change Column Data Type
to change the column data type in either text or relational sources, perform the follow-ing steps: