SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Large Chunks of Data


Large Chunks of Data

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63570 Visits: 19115
Comments posted to this topic are about the item Large Chunks of Data

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
david.beav
david.beav
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 21
Our CFO and financial analysts want all the raw data (flattened into 2-dimensional tables) to import into Excel and pivot to their heart's content. They don't know what they're looking for specifically, so they don't know what subset or rollup to ask us for.

Our data isn't so big that they would get million-record exports, but until Excel 2007 came out we were very badly stymied by the 65,000 row limit. 400,000-row exports are not abnormal.

We've tried but failed to find a web-based front-end pivot table environment that is as powerful and flexible as Excel.
ruadrauflessa
ruadrauflessa
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 27
Oh yeah... The age old problem of people not knowing what they want or need.

I have a whole bundle of statistical reports which was done on request but we still do give out "data dumps" in XLS format to the people that don't know what they want and/or need

Personally for me a report should have the least amount of data in it. It should be a summary of the data in your DB. And then based on whatever exceptions are drawn from that you can have your larger reports but based on very specific pieces of data rather than having something with thousands of records in it. With us I find mails going out with large reports clogging our e-mail system to the point where the report itself does not even want to send. Also from a management perspective you would like to know where to go and have a look rather than just thrashing around with raw data. I find the thrashing around in raw data to be kind of a blind exercise which consumes time and rarely comes to any results
Nakul Vachhrajani
Nakul Vachhrajani
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2274 Visits: 2145
Hello!

I would say a lot depends on the type of report - if you are doing an inventory report to cross-check the physical inventory stored at a remote location, the report can run into hundreds of pages!

I will talk about some personal experiences that I had (twice) about 10-15years ago. One was during the Y2K issue and the other during a data migration that we were doing in the good old systems of mainframe COBOL!

I remember doing table dumps on huge line printers of both old and new tables. Team members would face each other on a table, use a ruler and a marker and read aloud the before & after data to compare. Huge coffee pots later, the data would finally tally, and we would call the migration a success. (This is in the days when the servers were mainframes and workstations could be P1, 486 or even dumb-terminals).

The reason I remembered these incidents is because we used to deal with huge stacks of paper as thick as the one shown in the image of the line printer on the editorial. Those printers sure made a lot of noise, but were lightning fast!

In the current times, BI-based analytical data has started becoming more and more significant, and aggregated data is what is most often the requirement for display on a report. However, we do come across requirements where a schedule for the entire month is to be printed by a hotel for housekeeping purposes or by a company for delivery scheduling and production planning.
However, with the green revolution, the most important business requirement that we now get from our customers is to keep them restricted in a single page - you can use abbreviations, colours, etc - but as far as possible the report must not be more than an A4/letter page.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
P Jones
P Jones
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1247 Visits: 1517
Have you been reading my work? :-)
I spent some time yesterday writing an email to try to put off the business from taking a bunch of non-IT business people, sending them on an expensive SSRS course and expecting them to write all the business reports before April when we have half a dozen experienced and trained people in IT who can not only write the reports, but work with the sql database to create views, stored procs, optimised queries, indexes etc. to return the minimum of data efficiently across the network.
Reports I've seen created by business staff involve dragging large quantities of data across the network to get a one page result and they then complain that it is slow. Rolleyes
Nakul Vachhrajani
Nakul Vachhrajani
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2274 Visits: 2145
Hello!

Sorry for not mentioning this earlier, but in recent times, I have also seen a 98 page exceptions report for data coming over an interface over a period of 1 month (the odd thing is the system logs even success as an "exception" - basically, any data that it allows to pass through is recorded as an "exception" - wonder why?)

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Andrew Jackson
Andrew Jackson
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 1121
At the company that I used to work for, this one lady *demanded* more and more columns in this one report until we had to shrink the font size down to 6. After that, she still wanted more columns to be displayed. We said that only so many columns could fit onto a landscaped A4 page and still be legible. She replied and said "if Crystal Reports can't do this then we'll just have to change the tool."

Erm, OK.
ruadrauflessa
ruadrauflessa
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 27
AndrewJacksonZA (9/17/2010)
At the company that I used to work for, this one lady *demanded* more and more columns in this one report until we had to shrink the font size down to 6. After that, she still wanted more columns to be displayed. We said that only so many columns could fit onto a landscaped A4 page and still be legible. She replied and said "if Crystal Reports can't do this then we'll just have to change the tool."

Erm, OK.


Epic... Glad to see I am not the only one with such a client. Currently sitting with a report on font size 8 going over an A2 Landscape. Completely ridiculous.
skanker
skanker
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1157 Visits: 1385
Hi

Interesting question.

I think the answer to the ideal length of report is it depends!!!

For many managers a one page summary is just fine with a little data as possible. Too much data and there is the tendancy to miss information or worse still misunderstand. A nice graphic helps - though not too busy. This should ideally print on one page of A4.

However to get the most out SSRS I think that other reports need to more dynamic. There needs to be selectable parameters, user-defined sort orders and drill-downs to other data / reports. Here the print on one page idea can get kinda shaky.

At the other scale users may want caseload reports (Local Government speak here - customer reports for businesses) with rows of data that will most likely print off on multiple pages.

As for reports with millions of rows of data - are these really not extracts which are then manipulated in other software to produce reports. Or am I missing something?

On the subject of who is the best person to write reports - Business or ICT - I am not sure that Business people are necessarily bad at writing reports. I have seen some terrible reports written by very clever ICT superstars. Myself, I am an Information Officer - not sure if that puts me in the realms of Business or ICT (or neither). I create reports to suit the end users so that they get what they want (and more) and more importantly actually use them. I do not pretend to understand all the inner working of a SQL database, however I do know (sort of as I am constantly learning) how to manipulate large volums of data from SQL databases to return only the information needed for reports. Rightly or wrongly we use a reporting database to do this so as not to impact on the performance of the live databases (and fall foul of the software suppliers who would love to supply us with reports that do not quite work, but cost a premium).

Tim
Andrew Jackson
Andrew Jackson
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 1121
tim.kay (9/17/2010)A nice graphic helps - though not too busy.
Must... resist... must... fight it... must not post...

You mean like this:


C'mon, it's Friday! :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search