Sanity check for the dymanic Report code ex-developer wrote

I've been getting help cleaning up jQuery code related to the project I've inherited (LeakIM, Julian, Chris, you guys know who you are :))

Now, I'm moving to the C# and SQL code part of it. There's so much code. I really can't believe how much  code this guy wrote (my manager said the developer might have copied all this from another project from another job).

Anyway, I'll go step by step and I hope I can get some guidenace on how to proceed OR maybe I should keep the code as is....

1. We have a page that creates dynamic report; user enters a report name, selects columns, selects filters, selects groupby or orderby and selects when to send the report...schedules the report.

This is some screenshots of how the page looks like
1.png
2.png
3.png
2. Code gets all the info and keeps them in a Model. The "columns", "where" clause...the SQL statement is built in the C# code.

3. This is how the information is saved in the database. This is where I don't know if this is a good design or not. The columns, report parameters, the SQL statement and the report object itself are saved in the database. Indivdual options , for example, individual columns are not saved in separate rows per user per report.

sql.png
Questions

I need to wrap my head around this. I've never done a dynamic reports project.
1. Is it ok to save the data like that and then read it back?
2. Should I change the design to have a separate table for each tab (for report name, report columns, report parameters, etc)?
3. I think if I have a table for "report columns", the table will have multiple rows per report per user. Then, I have to read back the column names and concat them? so maybe having the column name saved in one field is ok?

4. This goes back to #2 and #3. If I have separate tables and fields for each tab...then I need to have a stored proc to read all the tables/fields and create the where clause and columns and etc to bring back results.

Any ideas would be great. I know this is a long question.
LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Your point 4 is the 64 million dollar question....

While it might be appealing to want to normalise by tab, or, idealistically to normalise by tab, it is going to create a lot more work for (probably) no material gain, in fact, more resource overhead : memory / locking / io / performance.

But I could easily argue that it is good design because the specific attributes of the report are being held individually, and, the design is easily extended to incorporate other aspects like datasource, security layers, encryption, deliverymethod etc.

And the tabs are aligned to the columns. The columns are only pertinent to the Report Object, so it is relative - and dont think you will gain anything in making them their own tables (instead of columns). Some will consider that heresy :)

The bottom line (for me) is the requirement is not a TNF database design exercise as such, it is a report generator. So, it needs to be considered (design wise)  in that light.

So, let me ask you a couple of questions...

1) Does it work
2) Are the users happy
3) Does it perform to expectations.
4) Is it important to change
5) Does it matter if it isnt changed

If you answer Y,Y,Y,N,N then move on to your next challenge - it sounds as if there might be challenges to consume your time :)

When it comes back to being top of the list, then I would start looking at more generic / more robust / more flexible reporting tools and use that as a benchmark instead of / before deciding to rewrite an internal bespoke/in-house system.

Cheers,
Mark Wills

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
Mark, thanks for taking the time to respond....I was about to bang my head to my soft cube wall over this :)

It does work. It's just hard to maintain and make changes. Way too much code That's why I started cleaning up the code and I cleaned up the jQuery code.

Let me ask you this...

When it comes back to being top of the list, then I would start looking at more generic / more robust / more flexible reporting tools and use that as a benchmark

I've googled and I only came across one http://demo.easyquerybuilder.com/asp-net-mvc
I want to see other designs and couldn't find any other. You have some examples? if not, it's ok.
Swatantra BhargavaTechnical Specialist/DeveloperCommented:
Hi Camillia,

There are some tools for reporting which you can check:

SSRS
RDLC file
Connect SQL DB to the Tableau

custom report SSRS, using RDLC file
In Tableau, you have set templates.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

CamilliaAuthor Commented:
I want to see the design of the database and how others save their data. We have SSRS here. We can't do that for this project. It has to be custom dynamic reports generator.

Mark's input is great. I'd wait and see if others have any input.
Scott PletcherSenior DBACommented:
Re:

#2. Is the SQL dynamically built every time?  If so, that's a big waste of resources.  The SQL should be generated and saved.  That would also allow the code to be custom-tweaked for best performance if needed.  A trigger or some other automatic mechanism could be used to identify when the code needs regenerated.

#3. No, it's not a good design, as you already know. It may be a practical design, and it might be the best than can be done in those circumstances, but it's not "good" to denormalize data that much.  
For example, with the everything-in-one-clob/blob format, you can't answer qs such as: We need to make a change to column ABCDE, how many custom reports will that affect?
CamilliaAuthor Commented:
@Scott
Is the SQL dynamically built every time?
1. User selects some columns and filters. Code saves the "ReportSQL".
2. User goes back and pulls up the report and code reads the "ReportSQL" and gets the data.
3. Now, if user goes back to the saved report and adds another column...code updates the "ReportSQL" column to the new query.

it's not a set SQL statement for all users.

#3. No, it's not a good design, as you already know. It may be a practical design, and it might be the best than can be done in those circumstances, but it's not "good" to denormalize data that much.
So, if no blob and no deep denormalization....then what's the other option?
Mark WillsTopic AdvisorCommented:
There are tons of tools. And, if you only found one, then there must be a lot more qualifiers that you have added to your search....

Bit like asking "how long is a piece of string", but then gain, I was referring to Reporting Tools, more so than Query Builders....

So, a list : https://www.capterra.com/reporting-software/ and look down the very bottom for even more lists....
https://www.softwareadvice.com/au/reporting-tools/#buyers-guide is a list, but importantly, a discussion and search down the bottom
And another variant : https://selecthub.com/enterprise-reporting-system/

As for Query builders, there are a few out there - some of them start with data modelling tools as well (guess if you can code, that could be bypassed), or, depending on language / platform, there are tools like : https://www.codeproject.com/Tips/1060170/User-Responsive-Web-Query-Builder-using-ASP-NET

But have to say, easyquerybuilder does look pretty good :)

Can I take it that you answered YYYNN ?

How much mantenance / coding is required on that querybuilder ? I would hate to think that it needs constant attention - assuming the YYYNN response....
Chinmay PatelChief Technology NinjaCommented:
Hi Camillia,

Thank you for letting me know. I dunno how I missed this.

I think I am more aligned to Marks' answer on this one. I have implemented spent more than a decade working with Dynamics CRM + .Net solutions and this design (more or less) is followed by Dynamics CRM's own report generator (it does use SSRS behind the scenes, but I could execute complex - within a limit - reports without SSRS if I wanted to just by using Dynamics CRM's engine which return in ASP.Net).

I will explain how it works a bit, then you can decided if it is something similar to your requirements and can decide if all the parameters are met - Functionality, Performance, Security and Maintenance (Give a shout if I missed something).

1. Dynamics has its own query language called FetchXML, see the sample below:
<fetch mapping='logical'>
   <entity name='account'> 
      <attribute name='accountid'/> 
      <attribute name='name'/> 
      <link-entity name='systemuser' to='owninguser'> 
         <filter type='and'> 
            <condition attribute='lastname' operator='ne' value='Cannon' /> 
          </filter> 
      </link-entity> 
   </entity> 
</fetch>

Open in new window

2. Notice Entity Name? that is your table name. attribute is your column. Link-Entity as you would have guessed is a join, filter is your where clause and condition/s are the actual conditions to fulfill where clause. If you see at a high level it is same as your existing code - maybe except the code quality and unnecessary stuff which S/he might have copied.
3. Sorting information is stored in the same FetchXml definition
4. The UI - i.e. how this reports are to be rendered is done by SSRS or Dynamics but I could very well build my own UI using ASP.Net.
5. Dynamics CRM adds (within reason) clustered indexes as per the requirements of a given query
6. I am sure Dynamics CRM reports engine does 1000 other things behind the scenes but more or less you would see that your design and Dynamics CRM's design is similar.
7. As long as compiling the qurey is concerned, I don't see the requirements unless and until your users are complaining about performance and even if that happens I will first check my .Net code and its performance before I start tuning my queries
8. Dynamics CRM does manage to figure out if a column is part of the query and does not allow you to delete as long as the definition is stored in the Dynamics database.

I hope this will help you to go ahead and figure out what really needs to be done.
PS: regarding the old code -  If Time and Resources permit - Use automated unit tests, figure out what code is just sitting there.
PPS: Dynamics CRM now falls under Dynamics 365 umbrella.

Regards,
Chinmay.
CamilliaAuthor Commented:
@Mark
Can I take it that you answered YYYNN ?
How much mantenance / coding is required on that querybuilder ? I would hate to think that it needs constant attention - assuming the YYYNN response....

My answer is YYYNN  with some code clean up. Now, I have to convince my manager. He's leaning towards redesign but the thing is...he doesn't know how the code works.  He says there are 2 other sections that need reporting but with what the ex-developer has done...we can reuse it with some clean up to make the code clean.

Let me look at your links.

@Chinmay Let me read. I'll post back.
CamilliaAuthor Commented:
I read your posts and looked at the links. I'm convinced to keep the same thing but clean up the speghatti code.
Just have to convince my manager.  I'll show him EasyQueryBuilder as well.

@scott
We need to make a change to column ABCDE, how many custom reports will that affect
this won't be a requirenment. Users just wants the report and export to Excel.

I'll keep this open to see if anyone else has other input.
CamilliaAuthor Commented:
My manager agreed to leave it as is and clean it up. He did add one requirenment but I'll open another question if I need help with it.
CamilliaAuthor Commented:
Very helpful. Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.