def update_dataset(self, data_frame, dataset_id, table_name,
update_policy):
"""
Update a previously created dataset with an Pandas Data Frame
:param data_frame: Pandas Data Frame to use to update an in-memory dataset
:param dataset_id: Identifier of the dataset to update, provided by create_dataset()
:param table_name: Name of the table to update within the dataset
:param update_policy: Update operation to perform. One of 'add' (inserts new, unique rows), 'update'
(updates data in existing rows and columns), 'upsert' (updates existing
data and inserts new rows), 'replace' (similar to truncate and load, replaces the existing data with new data)
"""
# warning for future deprecation / replacement by Datasets class
warnings.warn(
"This method will be deprecated. The Dataset constructor is preferred and supports multi-table data.",
DeprecationWarning)
# Replace any leading/trailing whitespace in df names, replace '.' with '_'
_df = data_frame.copy()
_df.columns = _df.columns.str.replace(".", "_")
_df.columns = _df.columns.str.strip()
# create dataset instance, add table, then publish the updates to the dataset
ds = Dataset(connection=self, dataset_id=dataset_id)
ds.add_table(name=table_name,
data_frame=_df,
update_policy=update_policy)
ds.update()
ds.publish()
def update(self, update_policy='upsert'):
"""Update single-table cube easily with the data frame stored in the Cube instance (cube.dataframe).
Before the update, make sure that the data frame has been modified
Args:
update_policy(str): Update operation to perform. One of 'add' (inserts new, unique rows), 'update' (updates
data in existing rows and columns), 'upsert' (updates existing data and inserts new rows), or 'replace'
(replaces the existing data with new data).
"""
if len(self._tables) > 1:
helper.exception_handler(
msg="""This feature works only for the single-table cubes.
\rTo update multi-table cube use Dataset class."""
)
else:
table_name = self._tables[0]["name"]
dataset = Dataset(self._connection, dataset_id=self._cube_id)
dataset.add_table(name=table_name,
data_frame=self.dataframe,
update_policy=update_policy)
dataset.update()
ds.add_table(name="Stores", data_frame=stores_df, update_policy="add")
ds.add_table(name="Sales", data_frame=sales_df, update_policy="add")
ds.create()
# when using `Dataset.add_table()`, Pandas data types are mapped to MicroStrategy data types
# by default numeric data is modeled as MSTR metrics and non-numeric as attributes
# you can set manually which columns treat as attributes and which as metrics
ds.add_table(name="Stores", data_frame=stores_df, update_policy="add",
to_attribute=["store_id"])
ds.add_table(name="Sales", data_frame=sales_df, update_policy="add",
to_attribute=["store_id"],
to_metric=["sales_fmt"])
# it is possible to update previously created dataset what looks really similar to creation
# you can use different update policies which are explained in the description of this script at the top
# by default `update()` is publishing data automatically, if you don't want to publish data, you have to
# set argument 'auto_publish` to False ; it is also possible to set chunksize for the update
dataset_id = "some_dataset_id"
ds = Dataset(connection=connection, dataset_id=dataset_id)
ds.add_table(name="Stores", data_frame=stores_df, update_policy="update")
ds.add_table(name="Sales", data_frame=sales_df, update_policy="upsert")
ds.update()
# finally it is possible to certify an existing dataset
ds.certify()
# Limitations
# Updating Datasets that were not created using the MicroStrategy REST API is not possible.
# This applies for example to Cubes created via MicroStrategy Web client.