Why I created a blog

Its been four years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.

Tuesday, September 18, 2012

A little surprise in 11.1.2.2 data loading

Today on OTN was a tread asking about wildcards in MaxL import statements. OTN Thread . I didn’t realize it was possible and up until 11.1.2.2 it was not. John Goodwin researched the new features guide and found

11.1.2.2 essbase new features readme
"Block Storage Parallel Data Load
Parallel data load refers to the concurrent loading of multiple data files into an Essbase database. When working with large data sets (for example, a set of ten 2 GB files), loading the data sources concurrently enables you to fully utilize the CPU resources and I/O channels of modern servers with multiple processors and high-performance storage subsystems.
Parallel data load uses multiple parallel pipelines on the server side, and multiple threads on the client-side, to load multiple data files concurrently, thus enabling data loads to be truly optimized to the capabilities of modern servers."

In the tech reference (for BSO cubes only) is the ability to use wild cards.

For the import statement

Specify whether the data import file(s) are local or on the server, and specify the type of import file(s).

To import from multiple files in parallel, use the wildcard characters * and/or ? in the IMP-FILE name so that all intended import files are matched.

  • * substitutes any number of characters, and can be used anywhere in the pattern. For example, day*.txt matches an entire set of import files ranging from day1.txt - day9.txt.

  • ?* substitutes one occurrence of any character, and can be used anywhere in the pattern. For example, 0?-*-2011.txt matches data source files named by date, for the single-digit months (Jan to Sept).

Example:

import database Sample.Basic
data from local data_file '/nfshome/data/foo*.txt'
using local rules_file '/nfshome/data/foo.rul'
on error abort;


and for



using max_threads INTEGER



Optionally specify a maximum number of threads to use, if this is a parallel data load.



Example:



import database Sample.Basic using max_threads 12
data from data_file '/nfshome/data/foo*.txt'
using rules_file '/nfshome/data/foo.rul'
on error write to 'nfshome/error/foo.err';


If this clause is omitted for a parallel data load, Essbase uses a number of pipelines equal to the lesser of number of files, or half the number of CPU cores.



While I have not had a chance to try this, it would have been very useful to me in the past. I’m guessing it was added for Exalytics efficiency, but we certainly reap the benefits of it.



Another tidbit



While not really a MaxL but more of a windows tip that has been around, but most don’t know. In a path statement in a windows batch file we know if we want to have a path statement in a script (like the import) most people use something like c:\\datafile\\Sample\\mydata.txt. We have to use the \\ because MaxL uses the backslash as an escape character. Did you know you can use forward slashes instead, just like on Unix.



c:/datafiles/Sample/mydat.txt. It can make your life easier

8 comments:

Jason said...

Nice feature pickup -- can't wait to play with it. As for the forward slashes on Windows -- have been doing this for years. It's not an Essbase-specific thing, rather, Windows provides the compatibility and has for quite some time, perhaps going back even to Server 2003 or so.

GlennS said...

I can't claim ownership of finding this, I just reported it as I know it will be of interest to others. As for the slash/backslash, I too know it has been around for a while but there are so many who don't I thought I would share and make people's lives a little easier

Anonymous said...

You are always full of surprises! :) Thanks again.

Jeff Davis said...

Glenn, have you seen this (talvisllc.com/Savant) application yet? Looks like you can just talk to Essbase pretty much with just your voice! Looks like a much easier way than Smartview!

Michael Sterling said...

Glenn, Do you ever see a performance difference in loading 'from server' versus 'from LOCAL' {local on the server - but not in the app/db} ?

Michael Sterling said...

Glenn, Do you see a performance difference when loading 'from server' versus 'from LOCAL' {where LOCAL is on the same server as the app/db, but not in the app/db folder?

GlennS said...

When loading from local even if the file is on the server, it seems to upload the file through the network to a tmp directory, so yes I see a performance difference. If Essbase knows the file is on the server, it uses its server location to load from and doesen't transfer it

Anonymous said...

Hi, if the Maxl Import command is on a remote server, can the import file be anywhere on the Essbase server or it has to be under an app fodler like sample/data ?