Hot answers tagged

10

UPDATE: for a more generic example of creating and populating a calendar or dimension table, see this tip: Creating a date dimension or calendar table in SQL Server For the specific question at hand, 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 ...


9

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 ...


7

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 ...


7

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 ...


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 see a tweet as an event happening, so I would model it as a new fact table FactTweet. More specifically as a factless fact. The dimensions for FactTweet would be DimDate, DimCar (if you can relate a tweet to a car), DimAuthor and I would probably keep URL and Description as degenerated dimensions. You could potentially add the sentiment of the tweet as ...


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 values....


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 http://www.kimballgroup.com/...


4

Checking whether the dates are contiguous You don't say which DBMS you're using here, but you're using SSAS so I'm guessing SQL Server. If you're on a recent enough version, using LAG and LEAD in window functions can be really handy for this kind of task. You can order the rows by the start date or by an incremental ID if you have one, and then use these to ...


3

The validity depends on how users want to look at the data. You are looking at it as just a transaction fact. Other types of fact tables include periodic snapshots and accumulating snapshots. If you want to see all the times that someone corrected a row to help decrease erroneous entries, the effective dates may be appropriate so it's clear that the ...


3

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: http://www.kimballgroup.com/...


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 ...


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

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

I will typically have my business key in the fact table so I can easily track back to the source system for any questions. I usually will put a unique constraint on it to ensure the granularity of the fact table is the same as the business key.


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.


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) ...



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