Global BI Architecture

Question

Post reply

Global BI Architecture

ps_vbdev

SSCarpal Tunnel

Points: 4060
More actions
April 21, 2014 at 4:26 am

#396631

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

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 SSCarpal Tunnel Points: 4060 More actions · Answer 2

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 SSCertifiable Points: 6522 More actions · Answer 3

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 One Orange Chip Points: 27764 More actions · Answer 4

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.

----------------------------------------------------

ps_vbdev SSCarpal Tunnel Points: 4060 More actions · Answer 5

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 One Orange Chip Points: 27764 More actions · Answer 6

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.

----------------------------------------------------

ps_vbdev SSCarpal Tunnel Points: 4060 More actions · Answer 7

yes, ive been working with 2012 and now 2014 SSIS and impressed with the speed ive seen

aaron.reese SSChampion Points: 13498 More actions · Answer 8

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