Greg Edwards-268690 wrote:
Maybe you should finish building the bridge instead of burning it. Not sure if your Report Person is part of IT or the Business. I would have to assume from your attitude, the Business.
Spending a month cleaning up a half baked ETL process and queries, only to have uncovered someone making extensive use of those tables, then “too bad”?
Perfect time to finish what you started, and work with the business. If they fix all their reports, do you see that as something that shows your value in providing solutions for them? Or drive them further into having their own ‘experts’ provide their reporting needs?
By spending a month on this already, it would appear someone recognizes value in what was being done. And keep in mind that value to the business drives the IT budget. I might venture that whoever let you spend a month “fixing” would welcome the chance to show the business the value in the follow up. A data mart (or start of a data warehouse) - 253 reports provide a lot of requirements for measures and dimensions - might be easily justified.
You might want to give a bit more thought to the big picture.
Hmmm. A lot of assumptions here?
This database/process was originally planned to be a data warehouse. The unfortunate thing was that the person who was leading this charge did not have the technical skills to do this work, and the development group already had other plans in place for reporting. This warehouse morphed into the set of flattened data tables that it is now.
The changes I made were basically a stop-gap to keep it alive. The sources of the data changed from on-prem to Azure and another one of the source OLTP databases is being eliminated. During this process, I uncovered a significant amount of things that were simply not correct. Half of this work was so that we can better understand what their needs are.
The future plans for this are PowerBI for the reports and dashboards, and an Azure data lake as the source. All of the reports are being built in PowerBI and run against this system.
Working with the group that is consuming this data (it's a statistical analysis group, certainly not technical) and the developers who are working on creating the future state of reporting, we have a well thought out and well designed plan for how it needs to be architected.
The small picture was painted by the folks who are creating the Power BI reports and dashboards. Myself, as well as others, have tried to point them in the right direction regarding scalability and best practices. We provide them monthly feedback on a formal basis, as well as almost daily interaction on all of the things they work on. This is the group that had to change 1500(!) SSRS reports 2 years ago when we changed our logo because they embedded the company logo directly into the reports. We had previously set up source control, Visual Studio projects, etc. for all of the SSRS reports as well as provide them with classes on best practices for SSRS. They still choose to use Report Builder, store the .rdl files on their local hard drives, hard-code values into queries, and so forth.
Because of the work I have done in relation to the future of reporting and BI, I received an individual award at the company's annual meeting. That goes with the award the GROUP won last year at the same meeting. Oh yeah, I also got a nice check to go along with that.
So, the answer is that we have looked at the big picture, it's ready to be painted, and the canvas is sitting on an easel. We will not start painting until early 2020.
Sorry that I was venting about the short-sightedness of others.