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 :class:`~pympx.Site` + Read the :class:`~pympx.Dimension` you wish to manipulate + Create or edit :class:`~pympx.Element`s including setting :attribute:`~pympx.Element.fields` + Edit structure + Set security + Add transactional data to a viewpoint The object model has been designed to work well with the :mod:`pandas` module. See :doc:`pandas`. 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 :mod:`PyMPX` correctly. See :doc:`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.2' 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. .. image:: _static/pympx_object_model.png :align: right 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 :class:`~pympx.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 :class:`~pympx.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': 'All': 'MainView':} from <_ViewpointsGetter object at 2193524627664> You'll note that the returned object is a :class:`~pympx._ViewpointsGetter` object. It behaves like a python dictionary. For instance, we can get the 'Mainview' viewpoint with the square bracket syntax: >>> sunrise.viewpoints['MainView'] Listing Dimensions ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ In a similar way as with viewpoints we can get all of the dimensions from a site: >>> sunrise.dimensions {0: , 1: , 2: , 3: , 4: , 5: , 6: , 8: , 9: , 10: , 11: , 12: } 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] The first unit dimension has a long name: Region >>> sunrise.dimensions[0].longname 'Region' We can assign any of the :class:`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 :class:`~pympx.Dimension` object has a ``.structures`` attribute, and this too behaves like a dictionary: >>> sunrise.dimensions[0].structures {'Unused': 'Real': 'All': 'XARUnits': 'Test':} from <_StructureGetter object at 2193462384344> >>> sunrise.dimensions[0].structures['XARUnits'] >>> sunrise.dimensions[0].structures['XARUnits'].longname '01. Global Monthly' Listing Elements ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Finally, a :class:`~pympx.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': , 'NA': , 'Europe': , 'UK': , 'BE&NL': , 'NL': , 'BL': , 'GER': , 'ITA': , 'SPA_POR': , 'SPN': , 'POR': , 'NORD': , 'SWE': , 'RON': , 'ASIAPAC': , 'TAI': , 'PHIL': , 'SK': , 'THA': , 'IND': , 'JAPAN': , 'MAL': , 'AUS': , 'HK': , 'APOTH': , 'SA': , 'CH': , 'BRA': , 'MEX': , 'CA': , 'ARG': , 'GRAND': , 'HOffice': , 'GMarkets': } from <_ElementsGetter object at 2129170569088> Examining a Dimension's Elements ------------------------------------------------------ Navigating Structures and Hierarchies ------------------------------------------------------ Often before navigating a hierarchy, especially when using an interactive environment like a Jupyter notebook, you may want to see the entirety of a structure. You can use the python :meth:`print()` function to show the whole of a structure, or any hierarchy or substructure (i.e. :class:`~pympx.StructureElement`). >>> 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 Traversing all of the Elements in a Structure or StructureElement ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ The simplest way of traversing a structure is to go through elements one by one, in the same order as the :meth:`print()` method would show the elements. This is known as **walking the tree** so the function is :meth:`~pympx.~Structure.walk()` In the example below we walk through the whole of the `XARUnits` structure: >>> for structure_element in sunrise.dimensions[0].structures['XARUnits'].walk(): ... print(structure_element.shortname,'\t\t', structure_element.longname) GMarkets Global Markets TMarkets Global Markets NA North America Europe Europe UK UK BE&NL Belgium & Holland BL Belgium NL Holland GER Germany ITA Italy SPA_POR Spain & Portugal SPN Spain POR Portugal NORD Nordic SWE Sweden RON Rest of Nordics ASIAPAC Asia Pacific TAI Taiwan PHIL Philippines SK South Korea THA Thailand IND Indonesia JAPAN Japan MAL Malaysia AUS Australia HK Hong Kong APOTH AsPac Other SA South America CH Chile BRA Brazil MEX Mexico CA Central America ARG Argentina GRAND Peru HOffice Head Office We can also walk a :class:`~pympx.StructureElement` in other orders, visiting either the leaf-most or root-most elements first. These methods are useful when we are programmatically changing the elements in the hierarchy. Here is the entire hierarchy: >>> eur = sunrise.dimensions[0].get('XARUnits.GMarkets/TMarkets/Europe') >>> print(eur) 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 And here is the walk. Note, we are only printing each StructureElement's short and long names, but we could be doing so much more. >>> for structure_element in eur.walk(): ... print(structure_element.shortname,'\t\t', structure_element.longname) Europe Europe UK UK BE&NL Belgium & Holland BL Belgium NL Holland GER Germany ITA Italy SPA_POR Spain & Portugal SPN Spain POR Portugal NORD Nordic SWE Sweden RON Rest of Nordics We have two other useful ways of walking a :class:`~pympx.StructureElement`; traversing from the root to the leaves, or from the leaves to the root. These are the :meth:`ascend` and :meth:`descend` methods: >>> for structure_element in eur.descend(): ... print(structure_element.level,'\t\t',structure_element.shortname,'\t\t', structure_element.longname) 0 Europe Europe 1 UK UK 1 BE&NL Belgium & Holland 1 GER Germany 1 ITA Italy 1 SPA_POR Spain & Portugal 1 NORD Nordic 2 BL Belgium 2 NL Holland 2 SPN Spain 2 POR Portugal 2 SWE Sweden 2 RON Rest of Nordics >>> for structure_element in eur.ascend(): ... print(structure_element.level,'\t\t',structure_element.shortname,'\t\t', structure_element.longname) 2 BL Belgium 2 NL Holland 2 SPN Spain 2 POR Portugal 2 SWE Sweden 2 RON Rest of Nordics 1 UK UK 1 BE&NL Belgium & Holland 1 GER Germany 1 ITA Italy 1 SPA_POR Spain & Portugal 1 NORD Nordic 0 Europe Europe Ways of finding a StructureElement ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Before a StructureElement can be manipulated it must be found. There are a number of ways of navigating to a :class:`~pympx.StructureElement` + Get the element using its path + Get all elements with a given shortcode Use get and get_elements ~~~~~~~~~~~~~~~~~~~~~~~~~ You can supply a path to the :meth:`~pympx.Dimension.get()` method, to retrieve a specific hierarchy node (:class:`~pympx.StructureElement`) from a given dimension >>> europe = sunrise.dimensions[0].get('XARUnits.GMarkets/TMarkets/Europe') >>> europe.path 'XARUnits.GMarkets/TMarkets/Europe' You can us the :meth:`~pympx.Structure.get_elements()` method to find all :class:`~pympx.StructureElement` s with a given shortcode in a :class:`~pympx.Structure` >>> europe_list = sunrise.dimensions[0].structures['XARUnits'].get_elements('Europe') >>> europe_list [] # Get the first StructureElement in the list and return some information about it >>> europe_list[0].path 'XARUnits.GMarkets/TMarkets/Europe' Retrieve children ~~~~~~~~~~~~~~~~~~~~~~~~~ You can interrogate the children of a :class:`~pympx.StructureElement` and the children of that child, and loop over the children >>> eur = sunrise.dimensions[0].structures['XARUnits'].hierarchies['GMarkets'].children['TMarkets'].children['Europe'] >>> eur.path 'XARUnits.GMarkets/TMarkets/Europe' >>> for ch in eur.children: ... print(ch.path) XARUnits.GMarkets/TMarkets/Europe/UK XARUnits.GMarkets/TMarkets/Europe/BE&NL XARUnits.GMarkets/TMarkets/Europe/GER XARUnits.GMarkets/TMarkets/Europe/ITA XARUnits.GMarkets/TMarkets/Europe/SPA_POR XARUnits.GMarkets/TMarkets/Europe/NORD >>> eur.children {'UK': 'BE&NL': 'GER': 'ITA': 'SPA_POR': 'NORD':} from <_StructureElementChildrenGetter object at 2193524748640> Find the parent ~~~~~~~~~~~~~~~~~~ Each :class:`~pympx.StructureElement` has a single parent, except for the root-most in a hierarchy. >>> eur = sunrise.dimensions[0](['XARUnits'].hierarchies['GMarkets'].children['TMarkets'].children['Europe'] >>> eur.parent >>> eur.parent.longname 'Global Markets' The root-most (top-level) StructureElement does not have a parent: >>> global_markets = sunrise.dimensions[0].structures['XARUnits'].hierarchies['GMarkets'] >>> global_markets.parent None >>> global_markets = sunrise.dimensions[0].structures['XARUnits'].hierarchies['GMarkets'] >>> global_markets.is_root True Find the parent's parents ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To get all of a :class:`~pympx.StructureElement`'s ancestors we can use the :func:`.ancestors` attribute Find the leaves ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ To get all of the elements from the outermost part of a :class:`~pympx.StructureElement` we can use the :func:`.leaves` attribute Creating and Editing Elements ----------------------------------- A powerful feature of the :class:`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 :meth:`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 :meth:`synchronise()` method to save the changes back to the :class:`~pympx.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:: ..\pympx\doc\examples\creating_time_elements_in_a_structure.ipynb 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 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. jupyter-execute:: from pympx import pympx as mpx .. notebook:: ..\pympx\doc\examples\setting_security_using_a_structure.ipynb Amending the Site Definition - Adding new Structures and Fields ---------------------------------------------------------------- Since the :py:attr:`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) in () ----> 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. .. include:: loading.rst .. notebook:: ..\pympx\doc\examples\import_dataframe_into_empower_viewpoint.ipynb Running Importer commands ------------------------------------ An :class:`Site` object has access to Empower Importer. It is usually better to run Importer commands through the :class:`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. + :meth:`Site.run_commands` + :meth:`Site.yield_commands` :meth:`run_commands` will run the commands and return all of the output at once. Always use :meth:`run_commands` when running commands without output, such as 'housekeep'. Also, if the data output from the commands is small, then use run_commands. :meth:`yield_commands` will run the commands and return a python generator object. Only by looping over the generator object will the commands actually run. :meth:`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: .. code-block:: c :emphasize-lines: 1,2,3 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 :attr:`~pympx.Site.importer.version` attribute >>> sunrise.importer.version [9, 5, 18, 1943]