Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Global BI Architecture Expand / Collapse
Author
Message
Posted Monday, April 21, 2014 4:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:11 AM
Points: 73, Visits: 423
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
Post #1563419
Posted Monday, April 21, 2014 6:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 287, Visits: 625
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?
Post #1563450
Posted Monday, April 21, 2014 6:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:11 AM
Points: 73, Visits: 423
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.
Post #1563455
Posted Monday, April 21, 2014 7:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 287, Visits: 625
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.
Post #1563471
Posted Tuesday, April 22, 2014 10:04 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:48 PM
Points: 321, Visits: 825
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.
Post #1563940
Posted Tuesday, April 22, 2014 10:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:11 AM
Points: 73, Visits: 423
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.
Post #1563944
Posted Tuesday, April 22, 2014 2:11 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:48 PM
Points: 321, Visits: 825
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.
Post #1564022
Posted Thursday, April 24, 2014 2:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:11 AM
Points: 73, Visits: 423
yes, ive been working with 2012 and now 2014 SSIS and impressed with the speed ive seen
Post #1564582
Posted Thursday, April 24, 2014 10:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:34 AM
Points: 386, Visits: 624
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).

Post #1564766
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse