December 10, 2015 at 5:36 pm
Hi Guys,
I have a requirement to setup/build sql server instance for ODS (operational data store). Could you please shed some light on this.
How the sql server instance needs to be setup. (sql services that needs to be installed)
Initially the data has to be migrated from few oracle schemas.
Many thanks in advance.
December 11, 2015 at 7:08 am
There's nothing uniquely special about an ODS. It's a SQL Server database that will primarily be used for reporting. I can't think of any special configurations that I've had to do with the SQL Server instance in support of a reporting database. Your primary concern should be how you'll be loading the ODS. Is it loading in a continuous, near real-time, stream, or using some type of nightly load process. Your focus should be on streamlining this process.
One consideration for reporting systems that can prove helpful is if you can mark the database read only. However, that has an implication. You'll have to be able to remove all connections, change the data from read only to read/write, load the data, then remove connections and change it back to read only. This is only possible if you have an expected downtime window. Otherwise it's not really doable for most organizations.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2015 at 7:26 am
Generally speaking, an ODS consolidates data (typically by periodic bulk loads) from multiple sources into a common (typically normalized) schema while conform the meta data coding (ie: M = Male) and retaining history. It will also typically be indexed to support reporting. However, your BI team will need to define the use case for this ODS (operational data store) before you can decide the optimal hardware and server configuration, because they may have a different idea of what an ODS is. But generally speaking, you're talking about a server that will contain very large normalized tables and provide support for bulk loading and aggregate querying.
The Data Loading Performance Guide
https://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 11, 2015 at 7:57 am
I agree with both above. An ODS just tends to be a central data warehouse where you've consolidated data, cleaned/standardized, and then often feed other systems. I've seen a few companies that most most of their reporting to their ODS, or they use the ODS as a source for reporting systems to ensure they get cleaner/ more consistent views of data.
I would plan on lots of storage, and I'd think about how I can manage larger backups/restores as this grows.
December 11, 2015 at 8:42 am
Thanks Grant/Eric/Steve.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy