Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jhixson
Starting Member
4 Posts |
 Posted - 2012-05-03 : 18:18:52
|
| Hello Everyone! I am new to the community, and new to SQL. I would like some input on a proposal I have for our Company.We currently have many, many sources of information and data. The parent Company owns several smaller companies: A mortgage Broker, a title Company, and an Insurance Agency. We share customers amongst these companies on a regular basis. Each company has it's own database hosted on the same server, owned by the parent company. Even things like Accounting and our Equipment Tracker are all SQL based, hosted on the same server.We use SQL Server 2008What I want to be able to do is Link these databases so that we can Generate Reports that span the entire parent company. For instance, Calulating the total Return on Investment from puchasing a lead for one company, assuming it has a certain percentage chance of being passed along to the other companies, generating certain revenue. But it should also take into account the payroll of all of the Staff in those companies, etc.My question is; Is this possible? How do we go about doing it? It really has the potential to generate extremely complex reports that would be nearly impossible to generate otherwise, but that could lead to massive growth.Can I make one giant linked database that draws all of it's needed information from the samller databases, and build all of the relationships into that larger database? That seems like the most obvious way to go about it, but I dont know if it is correct, or even possible. Then, what front end would I use for this database for Generating reports? Access?Please Help! Thank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-03 : 19:28:33
|
| From description it sounds like what you need is a unified reporting platform which will be a data warehouse.You can build it using any of DBMS including SQL Server. As you described, you will have logic written into this db to get required data from your source dbs for reporting. The tables will be mostly denormalised to increase the retrival efficiency and you can make use of Reporting tool like SSRS to create reports from these tables. The data loads can be done using sql server procedures as involved sources as well as db are all in sql server------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Jhixson
Starting Member
4 Posts |
Posted - 2012-05-04 : 11:01:10
|
| Thanks for the reply! It gives me a great atrting point to Study about how to proceed. Very much appreciate it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-04 : 18:35:24
|
welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-05-05 : 09:41:47
|
| Please do not go for TSQL based reporting solution.Go for SSAS based reporting.Build cubes define the dimensions and KPI's and build reports linked to them.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
|
|
|
|
|
Building Relational Databases