November 5, 2013 at 8:26 am
I am in a bit of a pickle on what to do here. Currently we have a View that is huge and abused. It was left joining tables all over so that we could send in the necessary criteria for specific report(s). I suggested that we needed to break apart this view into simple chunks for performance. The report(s) is designed to be generic and based on the dataset it will display specific data. Something similar to below:
Project --You can report from here
--Transaction --You can report from here
----Jobs --You can report from here
-----Detail --You can report from here
-------Shipments --You can report from here
---------Invoices --You can report from here
When you report from the specific area the dataset retrieves ids for what is involved. If you report from the project id it gets all the children. If you went from invoice it will only display things that were specific to the invoice id. There were over 40 tables in this view.
I suggested that we make the view into stored procedures to get the context we need, and then get the other dataset information we require like Company information, Sales dates, and Employee. It is a much more straight forward approach. The developers and I both agreed this was a good route. Problem becomes we will be introducing about 50 new stored procedures that will need to be maintained. That is not a bad thing, but I am not sure if there is a better way to do what we want. Something we can not use is a cube to get the information. This data is not posted and is live. Would a function work for something like this? I don't think it will because I would have to use case statements and the complexity would be there. A function is also not compiled I believe. It would help on the DAC side though. They would have one entry point vs 50. Any thoughts or questions are appreciated thanks in advance.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply