Sanity check for the dymanic Report code ex-developer wrote

Camillia
Camillia used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
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
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/Developer

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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 DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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?
@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 Advisor, Page Editor
Distinguished Expert 2018
Commented:
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 Ninja
Distinguished Expert 2018
Commented:
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.
@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.
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.
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.
Very helpful. Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial