Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Global BI Architecture


Global BI Architecture

Author
Message
ps_vbdev
ps_vbdev
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 704
Im in the process of starting to think about building a Data warehouse for out BI needs in the UK. It now looks like we will be branching our systems out to the US next year and possibly china in the future.

My initial thoughts are it would be a task to build on DW and pull all the information into that, also the resources this could also be an issue.

I would think creating one DW for each country/continent with local BI running of that, and possibly having a master DW that pulls info into this for a general overview would be the best approach. But being new to BI not sure if this architecture would be the best.

Am i on the right track with this thinking and/or have yous any suggestions.

Many thinks
gbritton1
gbritton1
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 840
Kinda hard to answer without knowing lots more.

e.g.

1. will most BI reporting be country-specific? (argues for separation)
2. what are the expected table sizes? (if small-ish, no value in separating by country)
3. would your proposed DWs be housed in the same physical server farm or locally in the countries they represent? (argues for separation)
4. How costly will the ETL to the "master" DW be?
ps_vbdev
ps_vbdev
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 704
gbritton1 (4/21/2014)
Kinda hard to answer without knowing lots more.

e.g.

1. will most BI reporting be country-specific? (argues for separation)
2. what are the expected table sizes? (if small-ish, no value in separating by country)
3. would your proposed DWs be housed in the same physical server farm or locally in the countries they represent? (argues for separation)
4. How costly will the ETL to the "master" DW be?



1. Mostly Yes, but Head office BI will look at entire operations
2. Table size is small in UK <2GB but expected US to be 10 fold in data, and china even more so assume that would grow very quickly if successful.
3. We are looking into Azure just now for our websites but we will most defiantly have a onsite data center hosting our warehouse applications etc
4. No idea just now, the UK DW hasten even been built, but looking to utilize SQL 2014 performance improvements as much as possible.


thanks for the reply.
gbritton1
gbritton1
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 840
I think I'd be inclined to start with one DW for all countries, build views to pull out data country-by-country or for all countries, then split up tables if/when growth starts impacting performance. At that time, you only have to change the views. Your application layer will continue to work.

Also, if you're in a rapid growth situation, remember to partition your tables by some scheme that makes the most business sense.
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1075 Visits: 2009
You can go with the one data warehouse approach. I would employ the use of schemas to denote where the information comes from. Example, china.orders will be the same as usa.orders architectually, just the source of the data is the difference. With these different schemas you could build them on different database files on your server.

----------------------------------------------------
How to post forum questions to get the best help
ps_vbdev
ps_vbdev
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 704
Thanks for the replays

and the one DW with schema separated tables seems to be coming up lots.

If this is employed the DW will reside in one location say datacenter UK. would my ETL workload have a large impack in gettign the date from US, China etc.
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1075 Visits: 2009
You can have two jobs running simultaneously and importing to the different tables. If the data resides on different machines you would need to connect via linked server objects. So if this is something that needs high availability for reporting, you should have all your data in a master warehouse. If performance is a concern there are remedies with upgrading hardware (ram, diskspace). SSIS is quite fast though at pulling in data and I would recommend looking into it if you haven't already.

----------------------------------------------------
How to post forum questions to get the best help
ps_vbdev
ps_vbdev
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 704
yes, ive been working with 2012 and now 2014 SSIS and impressed with the speed ive seen
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
Think of it in terms of a retail distribution network.

You will need a warehouse (data warehouse) where all the products (information) is collected and kept

You will need a store (data mart) where you can provide a limited number of products (infomation) to your customers (users).

I would push for a single enterprise wide data warehouse, but provide a materialized data mart (i.e. not dynamic views) for each user class - these classes may be geographical (UK, US, CN) functional (Finance, HR, Marketing) or level (operations, management, City reporting) and any combination of the above.

The actual technology stack needs an architect but the design principles would remain the same. I know that personally we are having real problems getting our US company to release data for storage outside the US for legal issues (federal and commercial).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search