If you come from the good old SAP Business Warehouse, you will remember the DB02 transaction. This transaction could show you all the tables in your system and how big they were. I showed an example on LinkedIn last year
In SAP Datasphere, you have something similar in the Space Management Monitor. It lists all tables and persistent views with their size and number of records.
This is nice, but not ideal, because it is limited to the space you have chosen. I have written a little Python program that shows you all the tables and persistent views in one overview. At the moment I always use Excel for the output, but you can modify this line and write it in the terminal or any other application.
The full source code is available on my GitHub repository. But I will explain how it works here. As you know, authentication is moved to a separate class, so I don't need it in all my programs again.
Start with the main function.
if __name__ == "__main__": parser = argparse.ArgumentParser(description="Get an overview of all tables over all spaces") parser.add_argument("-f", "--file", required=True, help="Path of parameter file") args = parser.parse_args() with open(args.file, 'r') as f: config = json.load(f) secrets_file = config["SETTINGS"]["secrets_file"] token_file = config["SETTINGS"]["token_file"] dsp_host = config["DATASPHERE"]["dsp_host"] # Now roll the dice and go to work print("Started...") get_database_tables() print("Ended")
The tool has only one parameter which I use to read a JSON file for all the necessary information like, secret, token, URL and so on. The whole logic is based on my function get_database_tables.
Get Database Tables
Here you have the OAuth first, and then I read the URL for all the spaces. The URL is https://xyz.eu10.hcs.cloud.sap/dwaas-core/api/v1/spaces
def get_database_tables(): header = utils.initializeGetOAuthSession(token_file, secrets_file) database_tables = [] url = utils.get_url(dsp_host, 'list_of_spaces') response = requests.get(url,headers=header) space_list = response.json()
So now I have a list of all the spaces in my tenant and I go through each space with this URL https://xyz.eu10.hcs.cloud.sap/dwaas-core/resources/spaces?tables=true&spaceids={spaceID}
There I have to change the SpaceID with each space, then loop through all the tables of my space and calculate all the key figures like used disk, used memory and records.
for spaceID in space_list: url = utils.get_url(dsp_host, 'space_tables').format(**{"spaceID": spaceID}) response = requests.get(url, headers=header) space_json = response.json() try: for table in space_json[spaceID]['tables']: tableName = table['tableName'] usedDisk = round((table['usedDisk'] / 1000 / 1000),2) # MB usedMemory = round(table['usedMemory'] / 1000 / 1000,2) records = table['recordCount'] database_tables.append((spaceID, tableName, usedDisk, usedMemory, records)) except KeyError: continue
Then I publish it in my Excel file.
df = pd.DataFrame(database_tables, columns=['Space', 'Table Name', 'Used Disk', 'Used Memory', 'Records']) df.to_excel(excel_file, sheet_name='Sheet1', index=False)
This is the result:
Conclusion
So now I have all the tables in one place and can see if something is wrong across the tenant. I hope you like this, and if you have any questions, use the comments below.
author.
Subscribe
- In my newsletter, you get informed about new topics
- You learn how to use Analysis Office
- You get tips and tricks about SAP BI topics
- You get the first 3 chapters of my e-book Analysis Office - The Comprehensive Guide for free
You want to know SAP Analysis Office in a perfect detail?
You want to know how to build an Excel Dashboard with your Query in Analysis Office?
You want to know how functions in SAP Analysis Office works?
Then you have to take a look into Analysis Office - The Comprehensive Guide. Either as a video course or as an e-book.
Write a comment