Pulling Data from an Analysis¶

In this tutorial, you will learn how to extract data from an OLAP table within an analysis. More specifically, you will learn:

How to connect to the EMS
How to get and Interact with a studio analysis object
How to get and interact with an OLAP table object
How to get and interact with data pool and data model objects
How to extract the data

Prerequisites¶

To follow this tutorial, you should have PyCelonis installed and should know how to perform basic interactions with PyCelonis objects. If you don't know how to do this, please complete the Celonis Basics tutorial first. Further, it would be helpful to already have the previously mentioned assets inside your EMS. Please refer to the Studio - Introduction and Data Integration - Data Export tutorials for an overview of working with each asset type.

1. Import PyCelonis and connect to Celonis API¶

In [1]:

Copied!

from pycelonis import get_celonis
celonis = get_celonis()
from pycelonis import get_celonis
celonis = get_celonis()

[2023-01-31 21:23:56,693] INFO: Initial connect successful! PyCelonis Version: 2.0.1

2. Get the analysis object, content, and data model ID¶

Option #1: Find objects by name¶

In [2]:

Copied!





space = celonis.apps.get_spaces().find('Space_Name_Here')
package = space.get_packages().find('Package_Name_Here')
analysis = package.get_analyses().find('Analysis_Name_Here')
content = package.get_analysis(analysis.id).get_content()
data_model_id = content.data_model_id
space = celonis.apps.get_spaces().find('Space_Name_Here')
package = space.get_packages().find('Package_Name_Here')
analysis = package.get_analyses().find('Analysis_Name_Here')
content = package.get_analysis(analysis.id).get_content()
data_model_id = content.data_model_id

Option #2: Find objects by ID (for Optimization)¶

In [ ]:

Copied!





space = celonis.apps.get_space('Space_ID_Here')
package = space.get_package('Package_ID_Here')
analysis = package.get_analysis('Analysis_ID_Here')
content = analysis.get_content()
data_model_id = content.data_model_id
space = celonis.apps.get_space('Space_ID_Here')
package = space.get_package('Package_ID_Here')
analysis = package.get_analysis('Analysis_ID_Here')
content = analysis.get_content()
data_model_id = content.data_model_id

3. Get the analysis sheet object¶

Option #1: Find analysis sheet by name¶

In [ ]:

Copied!

sheets = content.draft.document.components
sheet = sheets.find('Sheet_Name_Here')
sheets = content.draft.document.components
sheet = sheets.find('Sheet_Name_Here')

Option #2: Find Find Analysis Sheet by ID (for Optimization)¶

In [ ]:

Copied!

sheets = content.draft.document.components
sheet = sheets.find_by_id('Sheet_ID_Here')
sheets = content.draft.document.components
sheet = sheets.find_by_id('Sheet_ID_Here')

4. Get the OLAP table component and query¶

Option #1: Find table by title¶

In [ ]:

Copied!

components = sheet.components
component = components.find('Table_Title_Here')
components = sheet.components
component = components.find('Table_Title_Here')

Option #2: Find table by ID (for Optimization)¶

In [ ]:

Copied!

components = sheet.components
component = components.find_by_id('Component_ID_Here')
components = sheet.components
component = components.find_by_id('Component_ID_Here')

Get the table query¶

In [3]:

Copied!

query = component.get_query()
query
query = component.get_query()
query

Out[3]:

PQL(columns=[
	PQLColumn(name='#{CEL_O2C_ACTIVITY_MASTER_DATA.ACTIVITY_EN}', query='"CEL_O2C_ACTIVITY_MASTER_DATA"."ACTIVITY_EN"'),
	PQLColumn(name='#{CEL_O2C_ACTIVITY_MASTER_DATA.LEVEL}', query='"CEL_O2C_ACTIVITY_MASTER_DATA"."LEVEL"'),
	PQLColumn(name='#{CEL_O2C_ACTIVITY_MASTER_DATA.TIMESTAMP}', query='"CEL_O2C_ACTIVITY_MASTER_DATA"."TIMESTAMP"')
], filters=[], order_by_columns=[], distinct=False, limit=None, offset=None)

5. Get the data pool and data model objects¶

Option #1: Find data pool and data model objects by name¶

In [4]:

Copied!

data_pool = celonis.data_integration.get_data_pools().find('Data_Pool_Name_Here')
data_model = data_pool.get_data_model(data_model_id)
data_pool = celonis.data_integration.get_data_pools().find('Data_Pool_Name_Here')
data_model = data_pool.get_data_model(data_model_id)

Option #2: Find data pool and data model objects by ID (for Optimization)¶

In [ ]:

Copied!

# Use the data pool id (found in the data pool URL) to optimize your script
data_pool = celonis.data_integration.get_data_pool('Data_Pool_ID_Here')
data_model = data_pool.get_data_model(data_model_id)
# Use the data pool id (found in the data pool URL) to optimize your script
data_pool = celonis.data_integration.get_data_pool('Data_Pool_ID_Here')
data_model = data_pool.get_data_model(data_model_id)

6. Export the data using SaolaPy¶

In [5]:

Copied!

import pycelonis.pql as pql
from pycelonis.pql.saola_connector import AnalysisSaolaConnector

df = pql.DataFrame.from_pql(query, saola_connector=AnalysisSaolaConnector(data_model, analysis))
df.head()
import pycelonis.pql as pql
from pycelonis.pql.saola_connector import AnalysisSaolaConnector

df = pql.DataFrame.from_pql(query, saola_connector=AnalysisSaolaConnector(data_model, analysis))
df.head()

[2023-01-31 21:26:09,097] INFO: Successfully created data export with id 'c4624b81-48c9-406b-9ae6-c27b26fa5e44'
[2023-01-31 21:26:09,099] INFO: Wait for execution of data export with id 'c4624b81-48c9-406b-9ae6-c27b26fa5e44'

0it [00:00, ?it/s]

[2023-01-31 21:26:09,174] INFO: Export result chunks for data export with id 'c4624b81-48c9-406b-9ae6-c27b26fa5e44'

Out[5]:

	#{CEL_O2C_ACTIVITY_MASTER_DATA.ACTIVITY_EN}	#{CEL_O2C_ACTIVITY_MASTER_DATA.LEVEL}	#{CEL_O2C_ACTIVITY_MASTER_DATA.TIMESTAMP}
0	Pass Credit	Custom	CDHDR.UDATE + CDHDR.UTIME
1	Record Goods Issue	Header	VBFA.ERDAT + VBFA.ERZET
2	Change Plant	Item	CDHDR.UDATE + CDHDR.UTIME
3	Clear Invoice	Custom	BKPF.CPUDT + BKPF.CPUTM
4	Cancel Goods Issue	Header	VBFA.ERDAT + VBFA.ERZET
5	Create Intercompany credit memo	Custom	VBRP.ERDAT + VBRP.ERZET
6	Change Delivery Block	Header	CDHDR.UDATE + CDHDR.UTIME
7	Change Inco Terms (Part 2)	Header	None
8	Create Debit memo	Custom	VBRP.ERDAT + VBRP.ERZET
9	Change Inco Terms (Part 1)	Header	None
10	Create Shipment	Custom	LIPS.ERDAT + LIPS.ERZET
11	Clear Credit Memo	Custom	BKPF.CPUDT + BKPF.CPUTM
12	Change Price	Item	CDHDR.UDATE + CDHDR.UTIME
13	Change Route	Item	CDHDR.UDATE + CDHDR.UTIME
14	Create Sales Order Item	Item	VBAB.ERDAT + VBAB.ERZET
15	Change Shipping Type	Header	None
16	Create Picking	Header	LIKP.KODAT + LIKP.KOUHR
17	Create Purchase Order	Header	VBFA.ERDAT + VBFA.ERZET
18	Create Credit memo	Custom	VBRP.ERDAT + VBRP.ERZET
19	Cancel Reason for Rejection	Item	CDHDR.UDATE + CDHDR.UTIME
20	Create Invoice canceled	Custom	VBRP.ERDAT + VBRP.ERZET
21	Create Pro forma invoice	Custom	VBRP.ERDAT + VBRP.ERZET
22	Set Delivery Block	Header	CDHDR.UDATE + CDHDR.UTIME
23	Sales Order Released	Custom	None
24	Create Intercompany invoice	Custom	VBRP.ERDAT + VBRP.ERZET
25	Remove Delivery Block	Header	CDHDR.UDATE + CDHDR.UTIME
26	Create Returns delivery for order	Custom	LIPS.ERDAT + LIPS.ERZET
27	Change Material Availability Date	Custom	CDHDR.UDATE + CDHDR.UTIME
28	Set Reason for Rejection	Item	CDHDR.UDATE + CDHDR.UTIME
29	Change Requested Delivery Date	Custom	CDHDR.UDATE + CDHDR.UTIME
30	Create Quotation	Header	VBAB.ERDAT + VBAB.ERZET
31	Create Delivery	Custom	LIPS.ERDAT + LIPS.ERZET
32	Set Credit Hold	Custom	CDHDR.UDATE + CDHDR.UTIME
33	Release Credit Hold	Custom	CDHDR.UDATE + CDHDR.UTIME
34	Change Delivery Amount	Item	CDHDR.UDATE + CDHDR.UTIME
35	Change Material	Item	CDHDR.UDATE + CDHDR.UTIME
36	Material Availability Date passed	Custom	VBEP.MBDAT + VBEP.MBUHR
37	Change Confirmed Delivery Date	Custom	CDHDR.UDATE + CDHDR.UTIME
38	Change Reason for Rejection	Item	CDHDR.UDATE + CDHDR.UTIME
39	Create Sales Order	Custom	None
40	Create Invoice	Custom	VBRP.ERDAT + VBRP.ERZET
41	Change Confirmed Goods Issue Date	Custom	CDHDR.UDATE + CDHDR.UTIME
42	Change Requested Quantity	Custom	CDHDR.UDATE + CDHDR.UTIME
43	Record Return Goods Receipt	Custom	VBFA.ERDAT + VBFA.ERZET
44	Quotation Released	Custom	None
45	Change Payment Terms	Header	None
46	Create Credit memo canceled	Custom	VBRP.ERDAT + VBRP.ERZET
47	Change Requested Goods Issue Date	Custom	CDHDR.UDATE + CDHDR.UTIME
48	Change Confirmed Quantity	Custom	CDHDR.UDATE + CDHDR.UTIME

Conclusion¶

Congratulations! You have learned how to connect to the EMS, get and Interact with a studio analysis object, get and interact with an OLAP table object, get and interact with data pool and data model objects, and extract the data.