ETL from Sql Server to another Sql Server DB

[Former Member]

I have SAP '97 with Sql Server 7 as the DB.

I want to export my DB to another Sql Server instance (v 2016) so that I can build reports using Power BI.

But I hear that the DB is likely obfuscated & I won't be able to easily understand the schema. Is there any way to export the DB unobfuscated?

[clas_hortien]

Hello,

is this your productive database or just a test system, something you want to play with in Power BI ?

There is no direct way from SQL Server 7 to SQL 2016, you have to go multiple steps. You might be able to restore or attach the SQL 7 database to either SQL Server 2000 or SQL Server 2005. Once you converted the DB into the new format (by the restore or attach) you then can attach the 2000/2005 DB to SQL 2016 in the second step.

I haven't heard about any obfuscation of the database schema, but it is not easy to understand the schema even if you can access all the tables and objects in the DB. A modern SAP System has more than 60.000 tables.

Best regards and good luck

Clas Hortien

[leslie_moser]

Dear John,

I'm writing a little more than just the answer you need because other people will read this question.

SAP requires licensing for 3rd party apps to connect to an SAP system and read the data. There's been a lot of public discussion about this recently, you can google the phrase "indirect access" and you'll read more than enough. If this system you spoke of were an actual production SAP system and you simply connected Power BI on your own, you would most likely violate your licensing agreement. For any question regarding accessing the data in your SAP system, a customer must always talk to their Sales Rep to be certain they don't mistakenly violate their licensing agreement. We in development don't know the exact rules for every single scenario.

This system that you are speaking of is a demo system though so the rules are a bit different. I think you could connect Power BI as a Proof of Concept type test, but truthfully it would never make sense to do a PoC on SAP's demo data. You would always want to do a PoC on your own data. However, the most important answer here is that the Power BI connector is for BW systems and the demo system have is our OLTP application and not SAP BW which would be an OLAP system. I doubt that the Power BI connector will work to such an old system as you have. And even if you could somehow hack the connector and make it work, the demo system has a schema that isn't an OLAP style so the entire test makes no sense.

If you are currently licensed by SAP and want to test an SAP BW system, then you should install an IDES system ("Internet Demonstration and Evaluation System). There is at least a NW 7.0 BW system available per SAP Note 1013702, this is a 10 year newer product than the one you have and is mostly the same as the current version. I think Power BI will connect to this system, but you'd have to check the Power BI documentation.

Best regards,

Leslie Moser
P&I Development Manager for NW on SQL Server