Tutorial

This tutorial will lead you through the process of loading data into the Sunrise cube. The Sunrise cube is an example Empower cube that comes in the standard Metapraxis Empower installation.

The pympx module is an object model used to allow python to communicate with Empower in an intuitive fashion.

The pympx module includes classes which can read and write elements, structures and data from and to an Empower site.

When you use the object model, you will typically do some or all of the following things:

  • Connect to a Site

  • Read the Dimension you wish to manipulate

  • Create or edit fields

  • Edit structure

  • Set security

  • Add transactional data to a viewpoint

The object model has been designed to work well with the pandas module. See Using Pandas with PyMPX.

Getting Started - Importing the PyMPX module

To use the Empower object model in python, you first need to import it:

>>> from pympx import pympx as mpx

If you get an error, check that you have installed PyMPX correctly. See Installation.

You can check the version of the utilities that you have installed using the following code snippet:

>>> import pympx
>>> pympx._version.__version__
'1.2.5'

Overview of the PyMPX object model

The object model is made of a number of interlinking classes. The main classes are shown in the diagram below.

_images/pympx_object_model.png

The pympx module’s primary purpose is to make manipulating both metadata and data easy in Empower, using python.

Manipulating Empower metadata involves:

  • Seeing what metadata is available in Empower

  • Reading the required metadata object from Empower

  • Changing the object in python

  • Synchronising the changed python objects back to Empower

Connecting to a Site, and viewing the available objects

The fundamental class in the Empower object model is the Site.

As a minimum we can specify just the .eks or .beks alone:

>>> from pympx import pympx as mpx
>>> sunrise = mpx.Site(r"C:\Empower Sites\Sunrise Brands Limited\Sunrise Brands Limited.eks")

The first time you connect to a site you will be prompted for a user and password. Subsequent site connections will not need a password, because the module stores a locked down encrypted file containing the password that will work only on a single machine. The pympx module retrieves this information each time you log on to a site.

Note - if you are following along, the user is “The Supervisor” (without the double quotes) and the password is MPTraining

The Site object keeps track of object synchronization, and reads data from Empower when it needs to. It manages all interactions with Empower, and keeps track of whether any data has been edited.

If your site is likely to get quite big it is wise to specify a work directory for holding the data imports and exports.

Listing Viewpoints

We can access all of the site’s viewpoints through the viewpoints attribute:

>>> sunrise.viewpoints
{'Real':<pympx.pympx.Viewpoint object at 0x000001FEB7D64710>
'All':<pympx.pympx.Viewpoint object at 0x000001FEB7D64588>
'MainView':<pympx.pympx.Viewpoint object at 0x000001FEB7D563C8>} from <_ViewpointsGetter object at 2193524627664>

You’ll note that the returned object is a _ViewpointsGetter object. It behaves like a python dictionary. For instance, we can get the ‘Mainview’ viewpoint with the square bracket syntax:

>>> sunrise.viewpoints['MainView']
<pympx.pympx.Viewpoint at 0x2c49a668cc0>

Listing Dimensions

In a similar way as with viewpoints we can get all of the dimensions from a site:

>>> sunrise.dimensions
{0: <pympx.pympx.Dimension at 0x1601523ac50>,
 1: <pympx.pympx.Dimension at 0x16015c5d898>,
 2: <pympx.pympx.Dimension at 0x1601522acf8>,
 3: <pympx.pympx.Dimension at 0x160152b8198>,
 4: <pympx.pympx.Dimension at 0x160157ef240>,
 5: <pympx.pympx.Dimension at 0x1601352f278>,
 6: <pympx.pympx.Dimension at 0x160157e89e8>,
 8: <pympx.pympx.Dimension at 0x160152a7e48>,
 9: <pympx.pympx.Dimension at 0x160152a7630>,
 10: <pympx.pympx.Dimension at 0x160157e6128>,
 11: <pympx.pympx.Dimension at 0x1601529bd68>,
 12: <pympx.pympx.Dimension at 0x1601529b668>}

Note that dimension 7 (i.e. the 8th Unit dimension) is missing from the dictionary. This is because the Sunrise Brands site has only 7 unit dimensions.

We can use the dimensions property like a dictionary: dimensions[0] gets us the first Unit dimension:

>>> sunrise.dimensions[0]
<pympx.pympx.Dimension at 0x1efbc749470>

The first unit dimension has a long name: Region

>>> sunrise.dimensions[0].longname
'Region'

We can assign any of the pympx.Dimension objects in the .dimensions attribute to a python variable

>>> region = sunrise.dimensions[0]
>>> type(region)
pympx.pympx.Dimension

And we can retrieve information about the dimension using the object’s properties.

>>> region.longname
'Region'

Listing Structures

An Empower dimension has structures, so, likewise, an Dimension object has a .structures attribute, and this too behaves like a dictionary:

>>> sunrise.dimensions[0].structures
{'Unused':<pympx.pympx.Structure object at 0x000001FEB8468048>
'Real':<pympx.pympx.Structure object at 0x000001FEB8419EF0>
'All':<pympx.pympx.Structure object at 0x000001FEB8468240>
'XARUnits':<pympx.pympx.Structure object at 0x000001FEB84682E8>
'Test':<pympx.pympx.Structure object at 0x000001FEB84683C8>} from <_StructureGetter object at 2193462384344>
>>> sunrise.dimensions[0].structures['XARUnits']
<pympx.pympx.Structure at 0x16015bf4c50>
>>> sunrise.dimensions[0].structures['XARUnits'].longname
'01. Global Monthly'

Listing Elements

Finally, a Dimension object has an .elements attribute, and this too behaves like a dictionary, allowing us to look up the elements or loop over them, one at a time.

>>> sunrise.dimensions[0].elements
{'TMarkets': <pympx.pympx.Element object at 0x000001EFBD01B400>, 'NA': <pympx.pympx.Element object at 0x000001EFBD01B4E0>, 'Europe': <pympx.pympx.Element object at 0x000001EFBD01B630>, 'UK': <pympx.pympx.Element object at 0x000001EFBD01B748>, 'BE&NL': <pympx.pympx.Element object at 0x000001EFBD01B828>, 'NL': <pympx.pympx.Element object at 0x000001EFBD01B9B0>, 'BL': <pympx.pympx.Element object at 0x000001EFBD01B2B0>, 'GER': <pympx.pympx.Element object at 0x000001EFBD01B278>, 'ITA': <pympx.pympx.Element object at 0x000001EFBD01B240>, 'SPA_POR': <pympx.pympx.Element object at 0x000001EFBD01B208>, 'SPN': <pympx.pympx.Element object at 0x000001EFBD01B470>, 'POR': <pympx.pympx.Element object at 0x000001EFBD01B550>, 'NORD': <pympx.pympx.Element object at 0x000001EFBD01B6A0>, 'SWE': <pympx.pympx.Element object at 0x000001EFBD01B7B8>, 'RON': <pympx.pympx.Element object at 0x000001EFBD01B898>, 'ASIAPAC': <pympx.pympx.Element object at 0x000001EFBD01BA20>, 'TAI': <pympx.pympx.Element object at 0x000001EFBD01BB70>, 'PHIL': <pympx.pympx.Element object at 0x000001EFBD01BCC0>, 'SK': <pympx.pympx.Element object at 0x000001EFBD01BE10>, 'THA': <pympx.pympx.Element object at 0x000001EFBD01BEF0>, 'IND': <pympx.pympx.Element object at 0x000001EFBD020080>, 'JAPAN': <pympx.pympx.Element object at 0x000001EFBD020160>, 'MAL': <pympx.pympx.Element object at 0x000001EFBD0202B0>, 'AUS': <pympx.pympx.Element object at 0x000001EFBD020400>, 'HK': <pympx.pympx.Element object at 0x000001EFBD0204E0>, 'APOTH': <pympx.pympx.Element object at 0x000001EFBD0205C0>, 'SA': <pympx.pympx.Element object at 0x000001EFBD020710>, 'CH': <pympx.pympx.Element object at 0x000001EFBD0207F0>, 'BRA': <pympx.pympx.Element object at 0x000001EFBD0208D0>, 'MEX': <pympx.pympx.Element object at 0x000001EFBD0209B0>, 'CA': <pympx.pympx.Element object at 0x000001EFBD020A90>, 'ARG': <pympx.pympx.Element object at 0x000001EFBD020BE0>, 'GRAND': <pympx.pympx.Element object at 0x000001EFBD020CC0>, 'HOffice': <pympx.pympx.Element object at 0x000001EFBD020DA0>, 'GMarkets': <pympx.pympx.Element object at 0x000001EFBD020E80>} from <_ElementsGetter object at 2129170569088>

Examining a Dimension’s Elements

Creating and Editing Elements

A powerful feature of the pympx module is its ability to manipulate Empower Dimension Elements using code.

Ways in which an element can be manipulated are:

  • Changing the description

  • Altering the values of fields

  • Changing the calculation

  • Changing the Real/Virtual/Group status

Creating Elements from scratch is discussed in the next section.

Often you will wish to manipulate elements in a loop. For example:

>>> for el in sunrise.dimensions[0].elements:
...     print(el.longname)
Global Markets
North America
Europe
UK
Belgium & Holland
Holland
Belgium
Germany
Italy
Spain & Portugal
Spain
Portugal
Nordic
Sweden
Rest of Nordics
Asia Pacific
Taiwan
Philippines
South Korea
Thailand
Indonesia
Japan
Malaysia
Australia
Hong Kong
AsPac Other
South America
Chile
Brazil
Mexico
Central America
Argentina
Peru
Head Office
Global Markets

We can filter these elements down using an if statement:

>>> for el in sunrise.dimensions[0].elements:
...     if '&' in el.longname:
...         print(el.longname)
Belgium & Holland
Spain & Portugal

>>> for el in sunrise.dimensions[0].elements:
...     if '&' in el.longname:
...         el.description = 'Group of countries ' + el.longname
...         print(el.description)
Group of countries Belgium & Holland
Group of countries Spain & Portugal

Creating New Elements

Single Elements can be created using the Element class:

>>> iberia = mpx.Element(longname = "Iberia")

We can specify a shortname if we wish, however Empower will create one of its own if we don’t do so when we come to add the element into Empower.

This element will not be part of any dimension yet. We can add the element top the geography dimension like so:

>>> sunrise.dimensions[0].elements += iberia

Each time we run this code a new Empower element will be added to the Geography dimension, with the longname “Iberia”, creating multiple elements with the same longname. If we want to create only a single element with that longname, whether it exists already or not, we can use the merge method of the dimension.elements attribute like this:

>>> sunrise.dimensions[0].elements.merge(iberia, keys = ["Longname"])

However this could present a problem if e wish to use the iberia object again, we wouldn’t know if it had been added to the dimension[0].elements, or if it existed already. To solve this problem we can call:

>>> iberia = sunrise.dimensions[0].elements.merge(iberia, keys = ["Longname"])

The returned pympx.Element object is the ‘canonical’ version.

The data have changed on our copy of the site, but must be written back to the site to save the changed elements. We call the synchronise() method to save the changes back to the Sunrise site.

>>> sunrise.dimensions[0].elements.synchronise()

The Sunrise site will now contain an Element with a longname of “Iberia”. At this point the iberia object will have a .shortname attribute and a .physid attribute. Also iberia.mastered will be set to True.

>>> iberia.mastered
True

Note the elements.merge method can be used with lists of elements, and can even merge in elements from a pandas dataframe. When merging lists it will return a list of ‘canonical’ `Element`s.

Creating New Elements based on a natural key

Often we will want to create Elements in an Empower site where they exist in a a source system which has keys of its own.

The best way of doing this is to set field values in an Element to match the fields in the source, and then to merge using these fields as the key. For instance if the source for the “Iberia” element has a “Continent” and “Region” we can use these fields in an Empower element to master our “Iberia” element.

>>> iberia.fields["Continent"] = "EUROPE"
>>> iberia.fields["Region"] = "IB"
>>> iberia = sunrise.dimensions[0].elements.merge(iberia, keys = ["Continent","Region"])

Normally we’d do this by reading in a file and creating Elements from the contents. In the following example, the csv file has headers “Country”, “Continent” and “Region”. Possibly new elements are created from the file, and then synchronised with the Sunrise site.

>>> from csv import DictReader:
>>> new_elements = []
>>> for record in DictReader("c:/path/to/source_file.csv"):
...     geog_element = mpx.Element(longname = record["Country"], fields = {"Continent":record["Continent"],"Region":record["Region"]})
...     new_elements.append(geog_element)
>>> new_elements = sunrise.dimensions[0].elements.merge(new_elements, keys = ["Continent","Region"])
>>> sunrise.dimensions[0].elements.synchronise()

Editing an Element and its fields

An Element can be edited and synchronised back with Empower.

The following attributes can be edited:

  • .calculation

  • .calculation_status (Takes values “Virtual”, “Real”)

  • .colour

  • .description

  • .group_only

  • .longname

  • .measure

For instance, we can set iberia to have a sparse calculation of the elements with shortnames SPAIN and PORTUGAL:

>>> iberia.calculation = "SPAIN | PORTUGAL"

The data have changed on our copy of the site, but must be written back to the site to save the changed elements. We call the synchronise() method to save the changes back to the Site

>>> sunrise.dimensions[0].elements.synchronise()

Editing Structures and Hierarchies

One of the most powerful features of the object model is the ability to manipulate hierarchies.

Parts of the hierarchy tree can be added, moved and deleted.

Moving StructureElements within hierarchies

StructureElements can be copied and pasted from one part of one Structure to another.

First of all we need to create or .get() the StructureElement we wish to copy

>>> ASIAPAC = sunrise.dimensions[0].get('XARUnits.GMarkets/TMarkets/ASIAPAC')
>>> print(ASIAPAC)
ASIAPAC    (0)                Asia Pacific
+-TAI        (1)                Taiwan
+-PHIL       (2)                Philippines
+-SK         (3)                South Korea
+-THA        (4)                Thailand
+-IND        (5)                Indonesia
+-JAPAN      (6)                Japan
+-MAL        (7)                Malaysia
+-AUS        (8)                Australia
+-HK         (9)                Hong Kong
+-APOTH      (10)               AsPac Other

For comparison with the end result, this is the XARUnits structure before changes are made

>>> print(sunrise.dimensions[0].structures['XARUnits'])
GMarkets   (0)                Global Markets
+-TMarkets   (1)                Global Markets
  +-NA         (2)                North America
  +-Europe     (3)                Europe
  | +-UK         (4)                UK
  | +-BE&NL      (5)                Belgium & Holland
  | | +-BL         (6)                Belgium
  | | +-NL         (7)                Holland
  | +-GER        (8)                Germany
  | +-ITA        (9)                Italy
  | +-SPA_POR    (10)               Spain & Portugal
  | | +-SPN        (11)               Spain
  | | +-POR        (12)               Portugal
  | +-NORD       (13)               Nordic
  |   +-SWE        (14)               Sweden
  |   +-RON        (15)               Rest of Nordics
  +-ASIAPAC    (16)               Asia Pacific
  | +-HK         (17)               Hong Kong
  | +-MAL        (18)               Malaysia
  | +-AUS        (19)               Australia
  | +-IND        (20)               Indonesia
  | +-JAPAN      (21)               Japan
  | +-PHIL       (22)               Philippines
  | +-SK         (23)               South Korea
  | +-TAI        (24)               Taiwan
  | +-THA        (25)               Thailand
  | +-APOTH      (26)               AsPac Other
  +-SA         (27)               South America
  | +-CH         (28)               Chile
  | +-BRA        (29)               Brazil
  | +-MEX        (30)               Mexico
  | +-CA         (31)               Central America
  | +-ARG        (32)               Argentina
  | +-GRAND      (33)               Peru
  +-HOffice    (34)               Head Office

We can then “paste” into a .children() location using the += syntax:

>>> Europe = sunrise.dimensions[0].get('XARUnits.GMarkets/TMarkets/Europe')
>>> Europe.children += ASIAPAC
>>> print(sunrise.dimensions[0].structures['XARUnits'])
GMarkets   (0)                Global Markets
+-TMarkets   (1)                Global Markets
  +-NA         (2)                North America
  +-Europe     (3)                Europe
  | +-UK         (4)                UK
  | +-BE&NL      (5)                Belgium & Holland
  | | +-BL         (6)                Belgium
  | | +-NL         (7)                Holland
  | +-GER        (8)                Germany
  | +-ITA        (9)                Italy
  | +-SPA_POR    (10)               Spain & Portugal
  | | +-SPN        (11)               Spain
  | | +-POR        (12)               Portugal
  | +-NORD       (13)               Nordic
  | | +-SWE        (14)               Sweden
  | | +-RON        (15)               Rest of Nordics
  | +-ASIAPAC    (16)               Asia Pacific
  |   +-TAI        (17)               Taiwan
  |   +-PHIL       (18)               Philippines
  |   +-SK         (19)               South Korea
  |   +-THA        (20)               Thailand
  |   +-IND        (21)               Indonesia
  |   +-JAPAN      (22)               Japan
  |   +-MAL        (23)               Malaysia
  |   +-AUS        (24)               Australia
  |   +-HK         (25)               Hong Kong
  |   +-APOTH      (26)               AsPac Other
  +-ASIAPAC    (27)               Asia Pacific
  | +-TAI        (28)               Taiwan
  | +-PHIL       (29)               Philippines
  | +-SK         (30)               South Korea
  | +-THA        (31)               Thailand
  | +-IND        (32)               Indonesia
  | +-JAPAN      (33)               Japan
  | +-MAL        (34)               Malaysia
  | +-AUS        (35)               Australia
  | +-HK         (36)               Hong Kong
  | +-APOTH      (37)               AsPac Other
  +-SA         (38)               South America
  | +-CH         (39)               Chile
  | +-BRA        (40)               Brazil
  | +-MEX        (41)               Mexico
  | +-CA         (42)               Central America
  | +-ARG        (43)               Argentina
  | +-GRAND      (44)               Peru
  +-HOffice    (45)               Head Office

Note that ASIAPAC has been copied into europe.

We can copy one set of children into another, so if we want to copy ASIAPAC’s children into NORD we get this:

>>> NORD = sunrise.dimensions[0].get('XARUnits.GMarkets/TMarkets/Europe/NORD')
>>> NORD.children = ASIAPAC.children
>>> print(sunrise.dimensions[0].structures['XARUnits'])
GMarkets   (0)                Global Markets
+-TMarkets   (1)                Global Markets
  +-NA         (2)                North America
  +-Europe     (3)                Europe
  | +-UK         (4)                UK
  | +-BE&NL      (5)                Belgium & Holland
  | | +-BL         (6)                Belgium
  | | +-NL         (7)                Holland
  | +-GER        (8)                Germany
  | +-ITA        (9)                Italy
  | +-SPA_POR    (10)               Spain & Portugal
  | | +-SPN        (11)               Spain
  | | +-POR        (12)               Portugal
  | +-NORD       (13)               Nordic
  |   +-TAI        (14)               Taiwan
  |   +-PHIL       (15)               Philippines
  |   +-SK         (16)               South Korea
  |   +-THA        (17)               Thailand
  |   +-IND        (18)               Indonesia
  |   +-JAPAN      (19)               Japan
  |   +-MAL        (20)               Malaysia
  |   +-AUS        (21)               Australia
  |   +-HK         (22)               Hong Kong
  |   +-APOTH      (23)               AsPac Other
  | +-ASIAPAC    (24)               Asia Pacific
  |   +-TAI        (25)               Taiwan
  |   +-PHIL       (26)               Philippines
  |   +-SK         (27)               South Korea
  |   +-THA        (28)               Thailand
  |   +-IND        (29)               Indonesia
  |   +-JAPAN      (30)               Japan
  |   +-MAL        (31)               Malaysia
  |   +-AUS        (32)               Australia
  |   +-HK         (33)               Hong Kong
  |   +-APOTH      (34)               AsPac Other
  +-ASIAPAC    (35)               Asia Pacific
  | +-TAI        (36)               Taiwan
  | +-PHIL       (37)               Philippines
  | +-SK         (38)               South Korea
  | +-THA        (39)               Thailand
  | +-IND        (40)               Indonesia
  | +-JAPAN      (41)               Japan
  | +-MAL        (42)               Malaysia
  | +-AUS        (43)               Australia
  | +-HK         (44)               Hong Kong
  | +-APOTH      (45)               AsPac Other
  +-SA         (46)               South America
  | +-CH         (48)               Chile
  | +-BRA        (49)               Brazil
  | +-MEX        (50)               Mexico
  | +-CA         (51)               Central America
  | +-ARG        (52)               Argentina
  | +-GRAND      (53)               Peru
  +-HOffice    (54)               Head Office

To cut and paste we can use the .cut() method. Starting from scratch: Note we are using the .cut method in the next line to remove the ASIAPAC StructureElement from TMarkets, then we are copying it in to Europe’s children.

>>> ASIAPAC = sunrise.dimensions[0].get('XARUnits.GMarkets/TMarkets/ASIAPAC').cut()
>>> Europe = sunrise.dimensions[0].get('XARUnits.GMarkets/TMarkets/Europe')
>>> Europe.children += ASIAPAC
>>> print(sunrise.dimensions[0].structures['XARUnits'])
GMarkets   (0)                Global Markets
+-TMarkets   (1)                Global Markets
  +-NA         (2)                North America
  +-Europe     (3)                Europe
  | +-UK         (4)                UK
  | +-BE&NL      (5)                Belgium & Holland
  | | +-BL         (6)                Belgium
  | | +-NL         (7)                Holland
  | +-GER        (8)                Germany
  | +-ITA        (9)                Italy
  | +-SPA_POR    (10)               Spain & Portugal
  | | +-SPN        (11)               Spain
  | | +-POR        (12)               Portugal
  | +-NORD       (13)               Nordic
  | | +-SWE        (14)               Sweden
  | | +-RON        (15)               Rest of Nordics
  | +-ASIAPAC    (16)               Asia Pacific
  |   +-TAI        (17)               Taiwan
  |   +-PHIL       (18)               Philippines
  |   +-SK         (19)               South Korea
  |   +-THA        (20)               Thailand
  |   +-IND        (21)               Indonesia
  |   +-JAPAN      (22)               Japan
  |   +-MAL        (23)               Malaysia
  |   +-AUS        (24)               Australia
  |   +-HK         (25)               Hong Kong
  |   +-APOTH      (26)               AsPac Other
  +-SA         (27)               South America
  | +-CH         (28)               Chile
  | +-BRA        (29)               Brazil
  | +-MEX        (30)               Mexico
  | +-CA         (31)               Central America
  | +-ARG        (32)               Argentina
  | +-GRAND      (33)               Peru
  +-HOffice    (34)               Head Office

Finally, we can replace all the top level hierarchies with a single StructureElement copied from another location:

>>> sunrise.dimensions[0].structures['XARUnits'].hierarchies = ASIAPAC
>>> print(sunrise.dimensions[0].structures['XARUnits'])
ASIAPAC    (0)                Asia Pacific
+-TAI        (1)                Taiwan
+-PHIL       (2)                Philippines
+-SK         (3)                South Korea
+-THA        (4)                Thailand
+-IND        (5)                Indonesia
+-JAPAN      (6)                Japan
+-MAL        (7)                Malaysia
+-AUS        (8)                Australia
+-HK         (9)                Hong Kong
+-APOTH      (10)               AsPac Other

Tip

When a StructureElement is moved into a new Structure - it no longer refers to the old structure - it is essentially a copy of the original StructureElement

Special StructureElement operations

abdicate():

Structure elements can be removed from a hierarchy, leaving their children in place. Once again we start from scratch:

>>> Europe = sunrise.dimensions[0].get('XARUnits.GMarkets/TMarkets/Europe')
>>> print(Europe)
Europe     (0)                Europe
+-UK         (1)                UK
+-BE&NL      (2)                Belgium & Holland
| +-BL         (3)                Belgium
| +-NL         (4)                Holland
+-GER        (5)                Germany
+-ITA        (6)                Italy
+-SPA_POR    (7)                Spain & Portugal
| +-SPN        (8)                Spain
| +-POR        (9)                Portugal
+-NORD       (10)               Nordic
  +-SWE        (11)               Sweden
  +-RON        (12)               Rest of Nordics
>>> Europe.children['SPA_POR'].abdicate()
>>> print(Europe)
Europe     (0)                Europe
+-UK         (1)                UK
+-BE&NL      (2)                Belgium & Holland
| +-BL         (3)                Belgium
| +-NL         (4)                Holland
+-GER        (5)                Germany
+-ITA        (6)                Italy
+-SPN        (7)                Spain
+-POR        (8)                Portugal
+-NORD       (9)                Nordic
  +-SWE        (10)               Sweden
  +-RON        (11)               Rest of Nordics

SPA_POR has disappeared and been replaced by its children SPN and POR

Sorting StructureElements

The easiest way to sort structure elements is to sort children of a single StructureElement at a time.

>>> ASIAPAC = sunrise.dimensions[0].get('XARUnits.GMarkets/TMarkets/ASIAPAC')
>>> print(ASIAPAC)
ASIAPAC    (0)                Asia Pacific
+-TAI        (1)                Taiwan
+-PHIL       (2)                Philippines
+-SK         (3)                South Korea
+-THA        (4)                Thailand
+-IND        (5)                Indonesia
+-JAPAN      (6)                Japan
+-MAL        (7)                Malaysia
+-AUS        (8)                Australia
+-HK         (9)                Hong Kong
+-APOTH      (10)               AsPac Other

Using the Structureelement.children property, we can order the children, and then set the children back to the new list of sorted children. For instance, we can sort by longname:

>>> ASIAPAC.children = sorted(ASIAPAC.children, key = lambda x:x.longname)
>>> print(ASIAPAC)
ASIAPAC    (0)                Asia Pacific
+-APOTH      (1)                AsPac Other
+-AUS        (2)                Australia
+-HK         (3)                Hong Kong
+-IND        (4)                Indonesia
+-JAPAN      (5)                Japan
+-MAL        (6)                Malaysia
+-PHIL       (7)                Philippines
+-SK         (8)                South Korea
+-TAI        (9)                Taiwan
+-THA        (10)               Thailand

We can do something a bit more sophisticated to get the ‘AsPac Other’ element down to the bottom:

>>> ASIAPAC.children = sorted(ASIAPAC.children, key = lambda x: x.longname if x.shortname != 'APOTH' else 'ZZZZZ')
>>> print(ASIAPAC)
ASIAPAC    (0)                Asia Pacific
+-AUS        (1)                Australia
+-HK         (2)                Hong Kong
+-IND        (3)                Indonesia
+-JAPAN      (4)                Japan
+-MAL        (5)                Malaysia
+-PHIL       (6)                Philippines
+-SK         (7)                South Korea
+-TAI        (8)                Taiwan
+-THA        (9)                Thailand
+-APOTH      (10)               AsPac Other

To save the changes to Empower we’ll need to synchronise the structure (i.e. ‘XARUnits’):

>>> ASIAPAC.structure.synchronise()

or:

>>> sunrise.dimensions[0].structures['XARUnits'].synchronise()

Sometimes we want to sort by shortcode. Since the shortcodes can change over time, the .order_by_shortcode_list() method ignores non-existent shortcodes, and puts unmentioned shortcodes at the end, keeping their original order:

>>> ASIAPAC.children.order_by_shortcode_list(['HK','MAL','NONSENSE'])
>>> print(ASIAPAC)
ASIAPAC    (0)                Asia Pacific
+-HK         (1)                Hong Kong
+-MAL        (2)                Malaysia
+-AUS        (3)                Australia
+-IND        (4)                Indonesia
+-JAPAN      (5)                Japan
+-PHIL       (6)                Philippines
+-SK         (7)                South Korea
+-TAI        (8)                Taiwan
+-THA        (9)                Thailand
+-APOTH      (10)               AsPac Other

Creating Time Elements and Structures

Time Elements can be created in the same way as standard elements, only they must be given a shortname.

Time Elements can be one of the standard Empower time types:

‘Year’, ‘Half-year’, ‘Quarter’, ‘Month’, ‘Week’, ‘Day’

These have an interval_index of 0 for ‘Year’ to 5 for ‘Day’.

The following example shows how they can be created in a loop and added to a Structure:

Example - Creating Time Elements and Structures

Notebook

Setting Security on Elements

Element Security is divided into three types of permissions

  • Viewer - A user can view an Element and associated metadata

  • Data Viewer - A user can view the data associated with an Element - note they will need access to veiw other Elements on a data tuple

  • Modifier - A user can modify the data associated with an Element and the associated metadata

An Empower Element’s security can be edited in PyMPX using Element.security which gives access to viewers, data_viewers and modifiers

>>> europe = sunrise.dimensions[0].elements['Europe']
>>> europe.security.viewers
Users {} from <_SecurityUsersGetter object at 0x25fe2b276a0>
>>> europe.security.data_viewers
Users {} from <_SecurityUsersGetter object at 0x25fe2b276a0>
>>> europe.security.modifiers
Users {} from <_SecurityUsersGetter object at 0x25fe2b276a0>

viewers, data_viewers and modifiers can have user shortcodes added to them, removed from them and so on using the operations allowed on pythons sets.

>>> europe.security.viewers.add('Test01')
>>> list(europe.security.viewers)
['Test01']
>>> europe.security.data_viewers
Users {} from <_SecurityUsersGetter object at 0x25fe2b276a0>
>>> europe.security.viewers.remove('Test01')
>>> list(europe.security.viewers)
[]

Security can be cleared:

>>> europe.security.viewers.clear()
>>> list(europe.security.viewers)
[]

Users can also be added with += …

>>> europe.security.viewers += 'Test01'

… and removed with -=

>>> europe.security.viewers -= 'Test01'

Finally, The security will need to be synchronised back to the site. This happens when Dimension.elements are sychronised to the site

>>> sunrise.dimensions.elements.synchronise()

Example - Setting Security based on a Structure

Notebook

Amending the Site Definition - Adding new Structures and Fields

Since the Dimension.structures attribute behaves like a dictionary, when we ask for a Structure that doesn’t exist, we get a KeyError.

>>> sunrise.dimensions[0].structures['NewStruct']
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-41-2472b619d0f3> in <module>()
----> 1 sunrise.dimensions[0].structures['NewStruct']
c:\users\harry.spencer\pympx\pympx.py in __getitem__(self, item)
    675             except KeyError:
    676                 self._load_structure(item)
--> 677                 self._structures[item].dimension=self.dimension
KeyError: 'NewStruct'

You can add the structure in the same way that you add an item, to a dictionary:

>>> sunrise.dimensions[0].structures['NewStruct'] = mpx.Structure(shortname = 'NewStruct', longname = 'My Shiny New Structure')

Another way is to use the “plus equals” syntax:

>>> sunrise.dimensions[0].structures += mpx.Structure(shortname = 'NewStruct', longname = 'My Shiny New Structure')

The structure won’t exist in the site yet - the site definition needs to be synchronised:

>>> sunrise.definition.synchronise()

In a similar way, fields can be added to a dimension:

>>> sunrise.dimensions[0].fields['Type']             = mpx.FieldDefinition(longname='Type')

Note, field names cannot contain spaces - they can however contain underscores.

Once again - new fields are a site definition change and need to be synchronised with Empower:

>>> sunrise.definition.synchronise()

A handy shortcut for adding Structures and Fields if they do not exist, or continue gracefully if they already exist is to use the “or equals” syntax.

For example:

>>> sunrise.dimensions[0].structures |= Structure(shortname = 'NewStruct', longname = 'My Shiny New Structure')

and similarly for fields:

>>> sunrise.dimensions[0].fields |= mpx.FieldDefinition(longname='Type')

Using the “or equals” syntax makes for much more compact code than the equivalent of checking for the existence of a structure or field before creating it in the site.

Loading Transactional Data

Transactional data can be loaded from pandas DataFrames.

The transactional data loads are created so that data is pre-aggregated up each Structure before being loaded into Empower.

The following example notebook can be found in pympx\doc\examples\import_dataframe_into_empower_viewpoint.ipynb :

Notebook

Note that the .to_empower_viewpoint function got added to the pandas dataframe during the obmod import

Viewpoint

The Viewpoint must be suitable for accepting a load, and this generally means single hierarchies on each of the 13 Structures.

The Viewpoint does not necessarily have to exist in Empower - it can be created in python from Structures, and never be synchronised.

Mappings

The mappings parameter maps Empower Dimensions to columns and elements. We pass in a dictionary of Dimension names to mapping instructions:

    >>> df.to_empower_viewpoint( site.viewpoints['Load1']
    ...                        , mappings = { 'Region':     'A column'
    ...                                     , 'Product':    {'B Column':'Long Name'}
    ...                                     , 'Indicator':  {'BusDev Discount':'BDDiscount','Percentage Discount':'PCTDiscoun'}
    ...                                     , 'Time':       'JAN11'
    ...                                     }
    ...                        )

As you can see from the example above, not all of the dimensions require a mapping. This is because some of the structures in the viewpoint are single Element structures. For example the ‘Currency’ dimension only has one Element USD, and the Main 01. Structure, which belongs to the Load1 viewpoint only has this single item in it.

Since there is no ambiguity about the Element that needs to be cleared down and reloaded, there is no need to specify it in the mappings.

Types of mappings

A single dimension mapping instruction can be one of the following:

  • Column Name

  • Column Name : Field

  • Column Name to Indicator Shortname dictionary

  • Constant Shortname or date

Some of these may be ambiguous - it is possible for instance to have a column named the same as a shortname of an Empower Element.

Column Name mapping

The mapping for ‘Region’ in the example below maps a single column.

    >>> df.to_empower_viewpoint( site.viewpoints['Load1']
    ...                        , mappings = { 'Region':     'A column'
    ...                                     , 'Product':    {'B Column':'Long Name'}
    ...                                     , 'Indicator':  {'BusDev Discount':'BDDiscount','Percentage Discount':'PCTDiscoun'}
    ...                                     , 'Time':       'JAN11'
    ...                                     }
    ...                        )

The column named A column must exist in the dataframe. If it is not there then the empower utilities will try to determine if A column is a shortname in the appropriate structure in the Load1 viewpoint.

If the column is found in the dataframe then the transactional data for all of the elements in the structure in the viewpoint will be cleared out, using a blockset command.

The values in the column will be translated from shortnames into Empower elements and from then on to physical identifiers. If any of the shortnames in the column don’t match elements in the structure, there will be an error during loading.

Column Name : Field mapping

The mapping for ‘Product’ in the example below maps a single column via a field. The field is Long Name but could be any field in the Product dimension. A unique client defined Product ID field would be ideal for this purpose.

    >>> df.to_empower_viewpoint( site.viewpoints['Load1']
    ...                        , mappings = { 'Region':     'A column'
    ...                                     , 'Product':    {'B Column':'Long Name'}
    ...                                     , 'Indicator':  {'BusDev Discount':'BDDiscount','Percentage Discount':'PCTDiscoun'}
    ...                                     , 'Time':       'JAN11'
    ...                                     }
    ...                        )

The column named B column must exist in the dataframe.

Column Name to Indicator Shortname dictionary

This type of mapping implies that the columns contains real values - floats or integers.

    >>> df.to_empower_viewpoint( site.viewpoints['Load1']
    ...                        , mappings = { 'Region':     'A column'
    ...                                     , 'Product':    {'B Column':'Long Name'}
    ...                                     , 'Indicator':  {'BusDev Discount':'BDDiscount','Percentage Discount':'PCTDiscoun'}
    ...                                     , 'Time':       'JAN11'
    ...                                     }
    ...                        )

Values in the ‘BusDev Discount’ column will be loaded into the indicator/metric with shortcode ‘BDDiscount’.

Values in the ‘Percentage Discount’ column will be loaded into the indicator/metric with shortcode ‘PCTDiscoun’.

Only the Elements specified in the mapping will be cleared down or loaded. In the example, the Indicator structure in the Load1 viewpoint, could contain many elements, but only the two specified metrics will be cleared down with a block-set and reloaded from this dataframe.

Constant Shortname or Date

    >>> df.to_empower_viewpoint( site.viewpoints['Load1']
    ...                        , mappings = { 'Region':     'A column'
    ...                                     , 'Product':    {'B Column':'Long Name'}
    ...                                     , 'Indicator':  {'BusDev Discount':'BDDiscount','Percentage Discount':'PCTDiscoun'}
    ...                                     , 'Time':       'JAN11'
    ...                                     }
    ...                        )

If there is a constant in the mapping, then the focus will be restricted to that constant when it is cleared down. If there is a column-mapping in the mapping, then all elements in the structure will be cleared down

Mapping from the Time Hierarchy

It is often the case that we would want to use exactly the same Viewpoint for a load every month. Since only the constant Time element identified in the mapping is cleared down it is not necessary to change the viewpoint to only include the month we are loading.

If there is a fixed datetime, e.g. ‘Time’: datetime.datetime(2011,1,1) then the time Structure will be searched to see if it only contains Elements with the same Empower date type. All elements in the structure must have ‘Year’ interval or all elements must have ‘Month’ interval. If there are mixed types then a LoaderSetup error will be raised. If all elements are of ‘Year’ interval, then the 2011 element will be cleared and loaded. If all of the elements in the structure are of ‘Month’ interval, then the January-2011 element will be cleared and loaded.

    >>> df.to_empower_viewpoint( site.viewpoints['Load1']
    ...                        , mappings = { 'Region':     'A column'
    ...                                     , 'Product':    {'B Column':'Long Name'}
    ...                                     , 'Indicator':  {'BusDev Discount':'BDDiscount','Percentage Discount':'PCTDiscoun'}
    ...                                     , 'Time':       datetime.datetime(2011,1,1)
    ...                                     }
    ...                        )
Notebook

Running Importer commands

An Site object has access to Empower Importer.

It is usually better to run Importer commands through the Site object, because it will encrypt your username and password, and inject these into the script securely. Also, the python script will trap errors much better than a batch file and raise an error in turn.

Since Empower Batch commands are now available in Empower Importer, these can be run using Site.importer too.

Running standard Importer commands

Some Empower Importer/Batch commands can be run directly on a site. For instance - housekeeping:

>>> from pympx import pympx as mpx
>>> sunrise = mpx.Site(r"C:\Empower Sites\Sunrise Brands Limited\Sunrise Brands Limited.eks")

>>> sunrise.housekeep()
2018-11-22 17:23:21 INFO     : Site C:\Empower Sites\Sunrise Brands Limited\Sunrise Brands Limited.eks housekept

Running custom Importer scripts directly

Often a whole chain of importer commands will need to be run. These may be very specific to a project. You can put the commands to run in a list and they will be run in order

There are two options for run commands.

  • Site.run_commands()

  • Site.yield_commands()

run_commands() will run the commands and return all of the output at once. Always use run_commands() when running commands without output, such as ‘housekeep’. Also, if the data output from the commands is small, then use run_commands.

yield_commands() will run the commands and return a python generator object. Only by looping over the generator object will the commands actually run. yield_commands() should be used when reading large amounts of data, so it can be processed one record at a time, rather than filling memory

>>> from pympx import pympx as mpx
>>> sunrise = mpx.Site(r"C:\Empower Sites\Sunrise Brands Limited\Sunrise Brands Limited.eks")

>>> sunrise.importer.run_commands(['insert-record 1 Banana'],header = ['Fruit'])
[{'Fruit':'Banana'}]

>>> for record in sunrise.importer.yield_commands(['insert-record 1 Banana'],header = ['Fruit']):
...    print(record)
{'Fruit':'Banana'}

Embedded security in Importer commands

Commands that would normally require SiteFile, User or Password to be set can be used as they are, because these parameters are set automatically.

For example, a “Housekeep” command would normally exist in a script like this:

    SiteFile "C:\Empower Sites\Sunrise Brands Limited\Sunrise Brands Limited.eks"
    User "The Supervisor"
    Password "MPTraining"
    Housekeep

Since encrypted logon and sitefile information is already embedded in the Site object, we can call:

>>> sunrise.importer.run_commands(['housekeep'])
[]

The above command will run the Housekeep batch command on the Sunrise site. Notice that it has returned an empty list of output. This can be ignored.

Note

For Importer commands that need the site, user and password set, use the place-holder parameters

  • ${site}

  • ${user}

  • ${password}

>>> sunrise.importer.run_commands(['empower-export-current-periods ${site} ${user} ${password}'
                                  ,'output'])
[{'Index': '0',
  'Interval Type': 'Year',
  'Period Index': '1',
  'Full Current Period': '2010',
  'Current Period': '2010',
  'Current Year': '2010',
  'Full Current Period (short form)': '10',
  'Current Period (short form)': '10',
  'Current Year (short form)': '10'},
 {'Index': '1',
  'Interval Type': 'Half-year',
  'Period Index': '2',
  'Full Current Period': 'Half-year 2 2011',
  'Current Period': 'Half-year 2',
  'Current Year': '2011',
  'Full Current Period (short form)': 'HY2 11',
  'Current Period (short form)': 'HY2',
  'Current Year (short form)': '11'},
 {'Index': '2',
  'Interval Type': 'Quarter',
  'Period Index': '2',
  'Full Current Period': 'Q2 2011',
  'Current Period': 'Q2',
  'Current Year': '2011',
  'Full Current Period (short form)': 'Q2 11',
  'Current Period (short form)': 'Q2',
  'Current Year (short form)': '11'},
 {'Index': '3',
  'Interval Type': 'Month',
  'Period Index': '8',
  'Full Current Period': 'Aug 2011',
  'Current Period': 'Aug',
  'Current Year': '2011',
  'Full Current Period (short form)': 'Aug 11',
  'Current Period (short form)': 'Aug',
  'Current Year (short form)': '11'},
 {'Index': '4',
  'Interval Type': 'Week',
  'Period Index': '26',
  'Full Current Period': 'Week 26 2011',
  'Current Period': 'Week 26',
  'Current Year': '2011',
  'Full Current Period (short form)': 'W26 11',
  'Current Period (short form)': 'W26',
  'Current Year (short form)': '11'},
 {'Index': '5',
  'Interval Type': 'Day',
  'Period Index': '138',
  'Full Current Period': '18 May 2004',
  'Current Period': '18 May ',
  'Current Year': '2004',
  'Full Current Period (short form)': '18 May 04',
  'Current Period (short form)': '18 May ',
  'Current Year (short form)': '04'},
 {'Index': '6',
  'Interval Type': 'Hour',
  'Period Index': '138',
  'Full Current Period': 'Hour 1 18 May 2004',
  'Current Period': 'Hour 1 18 May ',
  'Current Year': '2004',
  'Full Current Period (short form)': 'H1 18 May 04',
  'Current Period (short form)': 'H1 18 May ',
  'Current Year (short form)': '04'},
 {'Index': '7',
  'Interval Type': 'Minute',
  'Period Index': '138',
  'Full Current Period': 'Minute 1 18 May 2004',
  'Current Period': 'Minute 1 18 May ',
  'Current Year': '2004',
  'Full Current Period (short form)': 'M1 18 May 04',
  'Current Period (short form)': 'M1 18 May ',
  'Current Year (short form)': '04'},
 {'Index': '8',
  'Interval Type': 'Second',
  'Period Index': '138',
  'Full Current Period': 'Second 1 18 May 2004',
  'Current Period': 'Second 1 18 May ',
  'Current Year': '2004',
  'Full Current Period (short form)': 'S1 18 May 04',
  'Current Period (short form)': 'S1 18 May ',
  'Current Year (short form)': '04'}]

Getting the Importer version

The installed importer version can be retrieved with the version attribute

>>> sunrise.importer.version
[9, 5, 18, 1943]