Reddit mentions of The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling

The Data Warehouse Toolkit by Ralph Kimball

I did a project early in my career with a senior developer who didn't know shit about data warehouses and a lot of the performance issues and getting crapped on by this ignoramus could have been avoided had we both read this book. Instinctively I wanted to denormalize to improve performance. But I was "wrong".

Anyway if you do any kind of data analytics, I would highly recommend you read this book. Star Schema is bae.

I am currently working in a data warehousing and business intelligence role at a bank. Aside from the basics of ETL, SQL and OLAP, I would recommend having at least a basic understanding of financial accounting. I have also found it useful to read The Data Warehousing Toolkit as well as some other Kimball books.

For entry-level work, there are two recommendations of related skill that have served me quite well to get my foot in the door and show added value: Excel and reporting.

Every institution needs reports developed and it amazes me how rare it is to find well-built reports that clearly communicate their intended information. Being able to follow a few simple guidelines for effective layout and design go a long way. Edward Tufte wrote the definitive work regarding this, but I use Stephen Few's work for more up-to-date examples.

Excel has proven itself very useful for quick ad-hoc analysis and manipulations. Also, it is a mainstay application for most financial services companies and being fluent in functions, pivot charts and VBA is quite useful.

The Data Warehouse Toolkit by Kimball and Ross is a pretty good resource. He goes a bit overboard with the complexity in some of his industry examples, but he's probably used to implementing with large teams of programmers. The concepts and methods are great though and should be in the toolkit of anyone developing reporting systems.

http://philip.greenspun.com/sql/data-warehousing.html : A very good introduction to data warehousing and the star schema in particular.

http://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1210883123&sr=8-1 : the second thing you should read -- more detail and specific examples of gotchas and schema design for particular business scenarios.

http://blog.oaktonsoftware.com/2007_06_01_archive.html -- the third thing to read. this gets into at least the terminology for some of the weirdo stuff you have to deal with in the OLAP world -- snapshot values in a supposed cube with rollups, for e.g.

If you don't have it or are new to Data warehousing. I'd recommend Ralph Kimball's - The Data Warehouse Toolkit. Its been my bible of Business Intelligence.

Also, subscribe to /r/BusinessIntelligence

For most time-series analysis I prefer to build star-schema models and use a real time-dimension.

Your typical time dimension contains about 30-40 attributes, has a granularity of hourly or daily, and rolls up hierarchically to days, weeks, months, quarters, years, etc. This dimension has a single surrogate key that you include with all of your facts to make joins easy. Other non-key attributes might include day of week, weekend/weekday flags, holiday flags, ansi vs iso weeks, etc.

You invest the time once to build a nice model, get performance benefits with large data sets, and and development benefits with whatever technology you're working with: SQL, python, ruby, etc.

EDIT: this technique is common to data warehousing. Any media on this topic should provide a basic overview. A few specific things to check out include:

• The best book is probably Ralph Kimball's Data Warehouse Toolkit.
  
• [Stackoverflow](http://stackoverflow.com/questions/2507289/time-and-date-dimension-in-data-warehouse
  
• wikipedia
  
• Kimball also has a forum
  
  You should also be able to find some basic source code. I found one project on sourceforge here. No idea if it's any good or not. It isn't hard code to write - just code that nobody should really have to write these days - it's been implemented thousands of times. I can't share my existing solution since it belongs to my employer.

I'm no expert in database certification so I won't comment on them, but they sound expensive. I'm sure you could go a long way in improving your skills by working through some free resources and classic texts.

A nice tutorial on fundamentals is:
http://philip.greenspun.com/sql/

A classic introductory to intermediate text is the following. It can get you amazingly far because even advanced topics are explained well:
http://pages.cs.wisc.edu/~dbbook/

Don't get thrown off by the publication year. The fundamentals of relational databases have barely changed for decades.

An excellent in-depth look at database theory is presented in:
http://www.amazon.com/Foundations-Databases-The-Logical-Level/dp/0201537710

For data warehousing and analytical querying (beyond Ramakrishnan et al) this is a great resource:
http://www.amazon.com/The-Data-Warehouse-Toolkit-Dimensional/dp/0471200247

Source: I'm a graduate student in databases.

https://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247

The is a highly recommended book for the Data Warehouse industry. Hope you enjoy it and good luck.

ETL is the process of populating a data warehouse with data from operational systems. While both involve transferring/updating data, your issue isn't really about ETL. There might be some lessons about copying/updating data in the ETL field, though.

Kimball's books are great; I'd add this one to your reading list. Probably a better starting point on data warehouses than the ETL one.

IMO your problem is hardly database related, even if data stored in a db are involved. It's a pure SW development/programming task outside of the realm of database administration.

Start off by looking for off-the-shelf solutions, i.e. check with your POS supplier if they already support this.

Failing that, you need to build your own software for pushing updates to the remote locations. A service installed on every POS that periodically polls the central server for pricing info is probably your best bet (perhaps not ideal, but should be a serviceable solution for the short run). I'd send a JSON document with every local SKU and expect one back containing current prices -- or ask for changes since last update if you have a lot of products. Make sure nothing stupid happens when a SKU isn't found or the request fails.

Make sure you understand every relevant piece of the POS db's schema. Will updating the base price do, or do you need to consider discounts, currency, taxes and whatnot? You also need to be sure you're asking the right server for pricing info (proper authentication, e.g. something PKI based), and that you have instrumentation in place to notice if a remote location isn't asking for price data.

Awesome! I kind of fell into the job. I was initially hired as a web developer, and didn't even know what BI was, and then got recruited by one of the BI managers and fell in love. To me, it is one of the few places in IT where what you create will directly impact the choices a business will make.

Most of what I do is ETL work (taking data from multiple systems, and loading them into a single warehouse), with a few cubes (multidimensional data analaysis) and SSRS report models (logical data model built on top of a relational data store used for ad hoc report creation). I also do a bit of report design, and lots of InfoPath 2010 + SharePoint 2010 custom development.

We use the entire Microsoft BI stack here, so SQL Server Integration (SSIS), Analysis (SSAS), and Reporting Services (SSRS). Microsoft is definitely up and coming in the BI world, but you might want to try to familiarize yourself with Oracle BI, Business Objects, or Cognos. Unfortunately, most of these tools are very expensive and not easy to get up and running. I would suggest you familiarize yourself with the concepts, and then you will be able to use any tool to apply them.

For data warehousing, check out the Kimball books:

Here and here and here

For reporting, get good with data visualizations, anything by Few or Tufte, like:

Here and here

For integration, check these out:

Here and here

Also, if you're interested in Microsoft BI (SSIS, SSAS, SSRS) check out this site. It has some awesome videos around SSAS that are easy to follow along with.

Also, check out the MSDN BI Blog: http://blogs.msdn.com/b/bi/

Currently at work, but if you have more questions, feel free to shoot me a message!

If you want to stay in the SQL Server world (T-SQL/SSIS/SSRS/SSAS), I highly recommend reading T-SQL Fundamentals and The Data Warehouse Toolkit (and other Ralph Kimball books) over and over again. These were invaluable to me in my SQL days, as many SQL interviews tend to be less reliant on puzzle games and more reliant upon pure knowledge of the engine you're dealing with. Knowing the SQL Server engine really well will help you with DBA knowledge, also (which tends to focus on optimization and disaster recovery aspects).

Other than that, what do you want to do, specifically? All interviews are going to be different for different jobs.

The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)


Design Patterns: Elements of Reusable Object-Oriented Software

Good stuff.

Buy and study this. https://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247