I'm currently using a mix of smart view and power query(sql) to load data into Excel models however my excel always crashes when smart view is used. I'm required to work in Excel but I'm know looking at finding a way to periodically load data from Essbase into my SQL server database and only use power query(sql) for all my models. What would be my best options in doing this? Being a Python enthusiast I found essbasepy.py however there isn't much documentation on it. Please help
There are a couple of ways to go. The most straightforward is to export all of the data from your Essbase database using column export, then designing a process to load the data into SQL Server (such as using the import functionality or BULK IMPORT, or SSIS...).
Another approach is to use the DataExport calc script command to export either to a file (that you then load into SQL) or directly to the relational database (DataExport can be configured to export data directly to relational).
In either case, you will need privileges that are greater than normal user privileges, and either approach involves Essbase automation that may require you to coordinate with the Essbase admin.