Tag Info

Hot answers tagged

6

Row count isn't a great indicator of database size. I would not be worried about Oracle scaling to 2 billion rows. Relational databases can practically scale up into the many terabyte range. The ability to scale comes down to data model, hardware, and developer skill, user requirements, and budget. It's feasible to scale an Oracle (or SQL Server) data ...


5

I can think of three solutions - EAV, XML, and Sparse Columns. The latter is vendor-specific and may not be useful to you. Whichever method you choose, you may wish to consider storing the original request data in a raw format, in a table or flat file. It will make it easy to try new ways of storing the data, allow you to reload data if you discover a ...


5

EAV is not a bad design, per se, it is simply a design that requires a fair amount of forethought and can be wrought with performance issues as the quantity of data rises. It may be that for your system, it would work well. When I designed a system for storing query strings, I had no idea in advance what fields I would be interested in. I created a table ...


5

There was never a rule that bitmap indexes were only useful on columns that had relatively few distinct values. That was a myth that derived from the fact that bitmap indexes aren't appropriate for columns that are unique or mostly unique and that a lot of the columns that you would want to put bitmap indexes on happen to have relatively few distinct ...


4

You might want to decompose the support tickets fact table into transactions; User w on Date x moved ticket y to state z etc. This would facilitate metrics like "bounced tickets" etc. which service desk managers always seem to be keen on. This could be supplemented with the Accumulating Snapshot table you already have. Look here ...


3

Mondrian OLAP server http://mondrian.pentaho.com/ Pentaho Schema Workbench (for creating the cube schema) http://mondrian.pentaho.com/documentation/workbench.php Pentaho Aggregation Designer (for finding useful aggregations and building them) http://infocenter.pentaho.com/help/index.jsp?topic=%2Faggregation_designer_guide%2Fconcept_pad_overview.html ...


3

Here's my attempt. I will update this with the magic you use to determine things like Fiscal_MonthNumber and Fiscal_MonthName, because right now they're the only non-intuitive part of your question, and it's the only tangible information you actually didn't include. The "best" (read:most efficient) way to populate a calendar table, IMHO, is to use a set, ...


3

Date dimensions are pretty standard in a data warehouse, and are highly recommended by Kimball as most facts tie to a date. Typically, the key is an integer. It can be a meaningless surrogate key, or it can be a "smart" key where the integer is in the form yyyymmdd; e.g., the key for August 2, 2014 would be 20140802. Date dimensions provide a set of ...


2

To know how many tickets are active on a particular day, you need to know both when a ticket first became active and when it was closed. Your design already contains this data, so the count can be generated without adding any data. This can be done by calculating the number if days between the open and close and then joining that with a data set large ...


2

Almost always surrogate keys (your "Auto Incrementing primary key") are best in data warehouses. I have seen very few exceptions where this is not the case (but some do exist) - yours does not seem to be exceptional. To answer why would be repeating stuff you can find all around the web, by the giants in the field, for instance: ...


2

As Justin's said (and the links in his post prove), the cardinality rule is a myth. This aside, there's a good reason to use bitmap indexes on fact tables: separate bitmap indexes on can easily be combined by the optimizer to reduce the numbers of rows to access. This is very useful with fact tables with a large number of dimensions. While any single ...


2

As commented, arguments from both sides are valid. Let's call them "star" (the flattened schema of your DBA) and "EAV" (entity-attribute-value). The latter can serve as a hint. Details in this related answer: Is there a name for this database structure? Well, if your 500 metrics are of well known type and you don't invent new ones / drop old ones all the ...


2

Regarding why you would want to do this, imagine you want to see which words/short phrases in customer emails are associated with costly repairs, and you want to be able to analyze this using OLAP. It can be costly to tokenize/grammify many documents, so you might want to store the tokens/grams in a form which your OLAP server understands, ie columns. ...


1

Assuming you are using SQL Server, you could adapt this to work: CREATE TABLE [dbo].[IP2Location]( [ID] [int] NOT NULL CONSTRAINT PK_IP2Location PRIMARY KEY CLUSTERED IDENTITY(1,1) , [ip] [bigint] NULL, [country_code] [varchar](2) NULL, [country_name] [varchar](255) NULL, [region_name] [varchar](255) NULL, [city_name] [varchar](255) ...


1

We are familiar with the IBM toolset where I work. IBM's Cognos toolset can read star schema, especially since IBM recommends star schema for mart design.



Only top voted, non community-wiki answers of a minimum length are eligible