def test_cube_instance(self, mock_post): conn = microstrategy.Connection(base_url=BASE_URL, username=USERNAME, password=PASSWORD, project_name=PROJECT_NAME) mock_post.return_value.status_code = 200 response = cubes.cube_instance(conn, cube_id=CUBE_ID) self.assertEqual(response.status_code, 200)
def get_cube(self, cube_id, offset=0, limit=1000): """ Extracts the contents of a cube into a Pandas Data Frame :param cube_id: Unique ID of the cube you wish to extract information from. :param offset: (optional) To extract all data from the report, use 0 (default) :param limit: (optional) Used to control data extract behavior on datasets with a large number of rows. The default is 1000. As an example, if the dataset has 50,000 rows, get_cube() will incrementally extract all 50,000 rows in 1,000 row chunks. Depending on system resources, a higher limit (e.g. 10,000) may reduce the total time required to extract the entire dataset :return: Pandas Data Frame containing the cube contents """ # warning for future deprecation / replacement by Cube class warnings.warn( "This method will be deprecated. The Cube constructor is preferred and supports multi-table data.", DeprecationWarning) response = cubes.cube_instance(connection=self, cube_id=cube_id, offset=offset, limit=limit) if not response.ok: msg = "Error getting cube contents." self.__response_handler(response=response, msg=msg) else: json_response = response.json() instance_id = json_response['instanceId'] # Gets the pagination totals from the response object pagination = json_response['result']['data']['paging'] # If there are more rows to fetch, fetch them if pagination['current'] != pagination['total']: # initialize a list to capture slices from each query, and append the first request's result to the list table_data = [parsejson(response=json_response)] # Fetch add'l rows from this object instance from the intelligence server for _offset in range(limit, pagination['total'], limit): response = cubes.cube_instance_id(connection=self, cube_id=cube_id, instance_id=instance_id, offset=_offset, limit=limit) table_data.append(parsejson(response=response.json())) return pd.concat(table_data) else: return parsejson(response=json_response)
def __create_cube_instance(self, limit): inst_pbar = tqdm(desc='Initializing an instance of a cube. Please wait...', bar_format='{desc}', leave=False, ncols=280, disable=(not self._progress_bar)) # Request a new instance, set instance id response = cubes.cube_instance( connection=self._connection, cube_id=self._id, body=self._filter._filter_body(), offset=0, limit=self._initial_limit, ) inst_pbar.close() return response
def generate_cube_instance(self, filter=None): res = cubes.cube_instance(connection=self.connection, cube_id=self.cube_id) self.instance_id = res.json()['instanceId'] res_json = res.json() if filter == 'attr_el': for i, row in enumerate(res_json['definition']['grid']['rows']): row['elements'] = row['elements'][0:1] res_json['definition']['grid']['rows'][i] = row res_json['data']['headers']['rows'] = res_json['data']['headers']['rows'][0:1] res_json['data']['metricValues']['raw'] = res_json['data']['metricValues']['raw'][0:1] res_json['data']['metricValues']['formatted'] = res_json['data']['metricValues']['formatted'][0:1] res_json['data']['metricValues']['extras'] = res_json['data']['metricValues']['extras'][0:1] return res_json
def to_dataframe(self, limit=None, multi_df=False): """Extract contents of a cube into a Pandas Data Frame. Previously `microstrategy.Connection.get_cube()`. Args: limit (None or int, optional): Used to control data extract behavior. By default (None) the limit is calculated automatically, based on an optimized physical size of one chunk. Setting limit manually will force the number of rows per chunk. Depending on system resources, a higher limit (e.g. 50,000) may reduce the total time required to extract the entire dataset. multi_df (bool, optional): If True, return a list of data frames resembling the table structure of the cube. If False (default), returns one data frame. Returns: Pandas Data Frame containing the cube contents """ inst_pbar = tqdm(desc='Initializing an instance of a cube. Please wait...', bar_format='{desc}', leave=False, ncols=280, disable=(not self.progress_bar)) if limit: self._initial_limit = limit # Request a new instance, set instance id res = cubes.cube_instance(connection=self._connection, cube_id=self._cube_id, body=self._filter.filter_body(), offset=self.__OFFSET, limit=self._initial_limit) inst_pbar.close() # Gets the pagination totals and instance_id from the response object _instance = res.json() _instance_id = _instance['instanceId'] _pagination = _instance['data']['paging'] # initialize parser and process first response p = Parser(response=_instance, parse_cube=True) p.parse(response=_instance) # If there are more rows to fetch, fetch them if _pagination['current'] != _pagination['total']: if not limit: limit = max(1000, int((self._initial_limit * self._size_limit) / len(res.content))) # Count the number of additional iterations it_total = int((_pagination['total']-self._initial_limit)/limit) + ((_pagination['total']-self._initial_limit) % limit != 0) if self.parallel and it_total > 1: threads = helper.get_parallel_number(it_total) with FuturesSession(executor=ThreadPoolExecutor(max_workers=threads)) as session: fetch_pbar = tqdm(desc="Downloading", total=it_total+1, disable=(not self.progress_bar)) future = self.__fetch_chunks_future(session, _pagination, _instance_id, limit) fetch_pbar.update() for i, f in enumerate(future, start=1): response = f.result() if not response.ok: current_offset = self._initial_limit+(i-1)*limit response = cubes.cube_instance_id(connection=self._connection, cube_id=self._cube_id, instance_id=_instance_id, offset=current_offset, limit=limit) fetch_pbar.update() fetch_pbar.set_postfix(rows=str(min(self._initial_limit+i*limit, _pagination['total']))) p.parse(response.json()) fetch_pbar.close() else: self.__fetch_chunks(p, _pagination, it_total, _instance_id, limit) # return parsed data as a data frame self._dataframe = p.dataframe # split dataframe to dataframes matching tables in Cube if multi_df: # save the multitable_definition response to the instance self.__multitable_definition() # split dataframe to dataframes matching tables in Cube self._dataframes = [self._dataframe[columns].copy() for _, columns in self._table_definition.items()] return self._dataframes else: return self._dataframe
def to_dataframe(self, limit=25000, progress_bar=True, multi_df=False): """ Extract contents of a cube into a Pandas Data Frame. Previously `microstrategy.Connection.get_cube()`. Args: limit (int, optional): Used to control data extract behavior on datasets with a large number of rows. The default is 25000. As an example, if the dataset has 50,000 rows, get_cube() will incrementally extract all 50,000 rows in 1,000 row chunks. Depending on system resources, a higher limit (e.g. 10,000) may reduce the total time required to extract the entire dataset. progress_bar(bool, optional): If True (default), show the upload progress bar. multi_df (bool, optional): If True (default), return a list of df resembling the table structure of the cube. If False, returns one df. Returns: Pandas Data Frame containing the cube contents """ inst_pbar = tqdm( desc='Connecting to MicroStrategy I-Server. Please wait...', bar_format='{desc}', leave=False, ncols=310) # Request a new instance, set instance id res = cubes.cube_instance(connection=self._connection, cube_id=self._cube_id, body=self._filter.filter_body(), offset=self.__OFFSET, limit=limit) inst_pbar.close() if not res.ok: msg = "Error getting cube contents." self.__response_handler(response=res, msg=msg) else: _instance = res.json() _instance_id = _instance['instanceId'] # Gets the pagination totals from the response object _pagination = _instance['result']['data']['paging'] # If there are more rows to fetch, fetch them if _pagination['current'] != _pagination['total']: # initialize a list to capture slices from each query, and append the first request's result to the list table_data = [parsejson(response=_instance)] # Count the number of iterations it_total = int(_pagination['total'] / limit) + (_pagination['total'] % limit != 0) # Fetch add'l rows from this object instance from the intelligence server with tqdm(total=it_total, disable=(not progress_bar)) as fetch_pbar: if progress_bar: fetch_pbar.update() fetch_pbar.set_description("Downloading") fetch_pbar.set_postfix(rows=limit) for _offset in range(limit, _pagination['total'], limit): if progress_bar: fetch_pbar.update() fetch_pbar.set_description("Downloading") fetch_pbar.set_postfix( rows=min(_offset + limit, _pagination['total'])) response = cubes.cube_instance_id( connection=self._connection, cube_id=self._cube_id, instance_id=_instance_id, offset=_offset, limit=limit) table_data.append(parsejson(response=response.json())) # concatenate and return the list of result data as a data frame self._dataframe = pd.concat(table_data).reset_index(drop=True) else: # otherwise parse the first result and return it as a dataframe self._dataframe = parsejson(response=_instance) # split dataframe to dataframes matching tables in Cube _tables = self.multitable_definition() if multi_df: self._dataframes = [ self._dataframe[columns].copy() for _, columns in _tables.items() ] return self._dataframes else: return self._dataframe