1

I'm programming an application that queries SAP Business One databases, which are incredibly complex, riddled with cryptic table and column names left over from ancient times (including many interesting typos), and in some parts have quite bad database design.

To ease working with these, I use a large "view library" as I call it, which defines views upon views that bring a lot of clarity, eliminate repetition, and allow me to ultimately aggregate all data which my application needs in a specific screen into one or two views that the application needs to query.

This works very well and I'm happy with it.

(Yes really. The performance is usually great, and when it isn't it can usually be fixed without much hassle and is almost always revealed to be related to a bad join or sub-query somewhere that would have caused the issue regardless of whether views are being used or not. I've only ever had one issue related to using views per se, which was MS SQL Server failing to optimize queries against one of the most complex views --perhaps the most complex in fact-- which I was able to solve simply by turning the view into a procedure that materializes the results of a couple intermediate views first.)

Now in some new deployment variants of SAP Business One, I will lose the ability of creating arbitrary SQL views in the database. Bad news. I will however be allowed to run some read-only queries, via a limited subset of the SQL language...

This brought me to the question: is there a way to "flatten" an SQL view via MS SQL Server (any version), so that I automatically get a query that already has all nested views "resolved" to their definitions, recursively?

For instance, consider the following sequence of (highly simplified) view definitions:

create view myItems as
  select itemCode, itemName from Items

create view myCustomers as
  select customerCode, customerName from Customers

create view myOrders as
  select o.orderId, o.orderNumber, c.*
  from Orders o
  join myCustomers c on c.customerCode = o.customerCode

create view myOrderLines as
  select l.lineId, l.lineNumber, o.*, i.*
  from OrderLines l
  join myOrders o on o.orderId = l.orderId
  join myItems i on i.itemCode = l.itemCode

I want the ability to get the "flat version" of let's say myOrderLines, which would be this:

select l.lineId, l.lineNumber,
       o.orderId, o.orderNumber,
       c.customerCode, c.customerName,
       i.itemCode, i.itemName
from OrderLines l
join Orders o on o.orderId = l.orderId
join Customers c on c.customerCode = o.customerCode
join Items i on i.itemCode = l.itemCode

Is such a thing possible?

1
  • I've never seen such a product & I think it would be very complicated to produce, that's not to say this wouldn't be something very useful (even a million seller). In my experience nested views are often the source of bad performance & a tool to un-nest them would be very useful - on the other hand it is nice to know that there are some DBA skills which can't just be automated away. Someone else may know of such a tool - but I don't – Stephen Morris - Mo64 Jan 12 at 9:42
1

So I know this doesn't directly answer your question but I think are things to consider that may be of help:

Why do you lose the ability to create Views and would you be able to create a second database to house your Views instead? (Views can reference other databases besides the one they exist in.)

If the previous solution isn't possible, could you use a data synchronization feature like Replication or Availability Groups to copy the SAP database to another database where you would be able to create your Views (either on the same server or another server)?

After getting a better picture from your comments, you can still look into Replication to centralize the data into one database outside of SAP, that your product can work off of. Another feature in SQL Server useful for consolidating multiple sources of data into one destination is SSIS.

6
  • Interesting ideas. Unfortunately in our case both are rather unrealistic. The deployment I'm speaking of where I lose the ability to create views is "cloud"-based deployment where customers don't get access to the DB server at all, and the only way to execute queries (in a strict subset of SQL) is through a REST API. Synchronizing the data to another DB we have control over would incur an unreasonable per-customer cost and kind of defeat the purpose of that deployment type. The app I'm developing is meant as a "mass market" and "plug and play" type of solution for these deployments. – TaylanKammer Jan 13 at 5:23
  • @TaylanKammer I do understand it seems roundabout to have to "copy" the database to a second database but this is quite common of a solution when you are limited on access to the source database (usually moreso due to avoiding breaking the warranty included with such system, and centralizing multiple systems into one database). It's something I've done for other ERP and CRM systems (cloud or on-prem - didn't matter) in the past, since unfortunately when you're limited to access of the database by the vendor then you're limited on the solutions around that as well. I'm not sure I follow... – J.D. Jan 13 at 14:08
  • ...how it would "incur an unreasonable per-customer cost"?...if you're thinking because additional licensing to SAP would be warranted that's untrue. Synchronizing the SAP source database outside their system to another destination is outside the scope of SAP at that point & outside the licensing requirements. If they were able to enforce licensing for doing so the same would be true for every backup you take, or something as stupid as copy some data out of the database and sending it in an email for business purposes. Otherwise, I'm not sure what other "per-customer" cost you're referring to. – J.D. Jan 13 at 14:11
  • Well, someone has to host the additional database. The customer is not going to do it, otherwise they would have chosen an on-premise deployment in the first place. We don't have the infrastructure to do it for all customers who buy our application internationally. It could be off-loaded to third parties; not sure what that would cost, but it would break the simplistic deployment model of the application. It's kind of "plug-and-play": install the app, configure the connection to your SAP Business One cloud account (API URL, username, password), and start using it. That's a big selling point. – TaylanKammer Jan 13 at 16:46
  • @TaylanKammer Sorry I didn't gather from your original post that you were selling services to other clients with your own schema on top of SAP's effectively making you the middleman (I assumed your end users were internal). Could you elaborate on the SAP component of it?...does every client of yours get their own SAP database? If so, in order to keep your product scalable and better future proof, you may need to bite the bullet of replicating the data outside of SAP so you have better control, and unfortunately roll that into the costs to your clients (just as SAP is). If somehow your... – J.D. Jan 13 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.