guidelines to script Yellowfin DB

Nitin Parihar shared this question 1 year ago
Answered

I am looking for suggestions and best practices around scripting Yellowfin DB.

as we move from one env to other we usually backup the yf DB and restore. we are trying to move away from this towards a more automated approach. we basically would like to script the out of box yf DB that has all our base reports.

are there any known issues around scripting the Yellowfin db and then recreating it?

any tools you would suggest?

are other enterprises doing this?


Thanks

Nitin

Comments (5)

photo
1

Hi Nitin,

I don't see the difference between backing up a Yellowfin database and scripting one. For example, you can backup a MySQL database by using the mysqldump utility, and the end result is that you have an SQL script of the MySQL database. The same with a PostgreSQL database, you can back it up with pg_dump, and the end result is an SQL script of the database.

1) So with that in mind, I don't know of any known issues around scripting/backing up a Yellowfin db and recreating/restoring it.

2) Most databases have their own tools for generating a backup script, however you can also do it with some 3rd party DB query tools.

3) it is very common.


Apologies if it turns out that I have misunderstood your idea about scripting a database, and if that is the case then please let me know in more detail what you mean by it.


regards,

David

photo
1

Hello Dave,

Thanks for your response. I am team mate of Nitin who posted this query.

We use both MS SQL Server and Oracle YF databases (v7.3). Generally this is how we create scripts of the database. Please let us know if you see any issues with this approach. From your comment above, I think this should be okay.

1) We use a third-party tool to generate SQL scripts for all the DB Schema objects - table, views, stored procs, functions etc from the YF DB

2) We use a third-party tool to generate SQL insert script for all the tables in the data bases. This script will include one insert statement for each row.

a) Are there any tables which has data types like BLOB that cannot be scripted out?

b) Are there any tables, whose data need not be scripted because it contains only transaction/session data and not useful to be reinserted in the target database? If there are such table that can be skipped, please let us know? This will reduce the number of "insert" scripts that we need to generate

3) The standard Yellowfin installer seems to create the database on the fly during installation. Does this process use SQL scripts? If so, can those script be shared so we can refer while we script out our customized YF reports DB?

Thank You

Prashanth

photo
1

Hi Prashanth,

I don't see any issues with your approach.

a) there is one column that has a blob, GeoPackLevelEntityGeometry.Polygon

b) yes, definitely you do not need the Event and EventArchive table, and also the ActiveSession table. Even the ReportInstance table you don't need because that just holds the results of past executions of reports.

3) No, the standard Yellowfin installer doesn't use SQL scripts, it uses an xml version of the schema (see attached). You can find this in the installer in the location:

com.hof.dataload.yellowfinschema.xml


regards,

David

photo
1

Thanks, Dave.

This is good info for us to proceed further. We will get in touch on this thread if we face any issues.

photo
1

OK, we'll be interested to hear how you get on. Good luck!

photo