Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Sounds like your data belongs in an OLAP (On-Line Analytical Processing) database. The way you're describing levels, slices, and performance concerns seems to lend itself to OLAP. It's probably modeled fine (not sure though), but you need a different tool to boost performance.</p> <p>I currently manage a system like this. We have a standard relational database for input, and then copy the pertinent data for reporting to an OLAP server. Our combo is Microsoft SQL Server (input, raw data), Microsoft Analysis Services (pre-calculates then stores the analytical data to increase speed), and Microsoft Excel/Access Pivot Tables and/or Tableau for reporting.</p> <p>OLAP servers: <a href="http://en.wikipedia.org/wiki/Comparison_of_OLAP_Servers" rel="nofollow">http://en.wikipedia.org/wiki/Comparison_of_OLAP_Servers</a></p> <p>Combining relational and OLAP: <a href="http://en.wikipedia.org/wiki/HOLAP" rel="nofollow">http://en.wikipedia.org/wiki/HOLAP</a></p> <p>Tableau: <a href="http://www.tableausoftware.com/" rel="nofollow">http://www.tableausoftware.com/</a></p> <p>*Tableau is a superb product, and can probably replace an OLAP server if your data isn't terribly large (even then it can handle a lot of data). It will make local copies as necessary to improve performance. I strongly advise giving it a look.</p> <p>If I've misunderstood the issue you're having, then by all means please ignore this answer :\</p> <p><strong>UPDATE:</strong> After more discussion, an Object DB might be a solution as well. Your data sounds multi-dimensional in nature, one way or the other, but I think the difference would be whether you're doing analytic aggregate calculations and retrieval (SUMs, AVGs), or just storing and fetching categorical or relational data (shopping cart items, or friends of a family member).</p> <p>ODBMS info: <a href="http://en.wikipedia.org/wiki/Object_database" rel="nofollow">http://en.wikipedia.org/wiki/Object_database</a></p> <p>InterSystem's Cache is one Object Database I know of that sounds like a more appropriate fit based on what you've said.</p> <p><a href="http://www.intersystems.com/cache/" rel="nofollow">http://www.intersystems.com/cache/</a></p> <p>If conversion to a different system isn't feasible (entirely understandable), then you might have to look at normalization and the types of data your queries are processing in order to gain further improvements in speed. In fact, that's probably a good first step before jumping to a different type of system (sorry I didn't get to this sooner).</p> <p>In my case, I know on MS SQL that a switch we did from having some core queries use a <code>VARCHAR</code> field to using an <code>INTEGER</code> field made a huge difference in speed. Text data is one of the THE MOST expensive types of data to process. So for instance, if you have a query doing a lot of <code>INNER JOIN</code>s on text fields, you might consider normalizing to the point where you're using <code>INTEGER</code> IDs that link to the text data.</p> <p>An example of high normalization could be using ID numbers for a person's First or Last Name. Most DB designs store these names directly and don't attempt to reduce duplication, but you could normalize to the point where Last Name and/or First Name have their own tables (or one table to hold both First and Last names) and IDs for each unique name.</p> <p>The point in your case would be more for performance than de-duplication of data, but something like switching from <code>VARCHAR</code> to <code>INTEGER</code> might have huge gains. I'd try it with a single field first, measure the before and after cases, and make your decision carefully from there.</p> <p>And of course, in general you should be sure to have appropriate indexes on your data.</p> <p>Hope that helps.</p>
    singulars
    1. This table or related slice is empty.
    1. PODatabase Structure for hierarchical data with horizontal slices
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBrett Rossier
    1. USBrett Rossier
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODatabase Structure for hierarchical data with horizontal slices
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI hadn't looked at OLAP before, but I suspect since we have high read volumes and well-known queries this wouldn't be the direction to go in (I thought I'd said this in the question, but clearly I hadn't)
      singulars
      1. PO
      1. USGlenjamin
    2. CONo, I think you explained it fine, I'm just offering an alternative that I think might fit well to your use case. Re-reading your question, I'm not sure on some details. One question is, do you have aggregate data (SUMs, AVGs, etc) that these queries are updating/fetching, or are they fetching things like categorical or relational data (items in a shopping cart, or friends of a family member, etc)? Your use case sounds like multi-dimensional data one way or the other, but the difference could be OLAP DB vs Object DB (ex:InterSystems Cache). Updating my answer with a few more links.
      singulars
      1. PO
      1. USBrett Rossier
    3. COAdded some more detail and other solutions to my answer.
      singulars
      1. PO
      1. USBrett Rossier
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload