<div>
Without knowing more specifics about your data it's hard to come up with a clear plan but I think moving towards a more traditional multi-dimensional star schema would be a good first step. &nbsp;You may also find the need to generate some pre-aggregated tables like claim totals by day/month/etc.
</div>


<div>
&gt;Please give me some suggestions<br />
Either hire an experienced reporting developer or architect, or contract with a local consulting company to build a reporting data warehouse database, ETL jobs that feed it, and reports. <br />
<br />
There's no reason why a reporting function should bang against tables that could output 100 million rows, and I would be highly suspect that such processes would slow down other non-reporting functions using the same data. <br />
<br />
&gt;We are thinking to make two flat tables, pre-join all the tables or make a star schema data warehouse ...<br />
You're on the right track, but there is not enough information here to be actionable, and what you need is way more then the scope of a single EE question. <br />
<br />
Good luck.<br />
Jim
</div>


<div>
Hi Jim Horn,<br />
<br />
Thank you for replying!<br />
<br />
Please see the my last comment, does it give you any information you need?<br />
<br />
I'm thinking the problem is we have to run report directly in production server, and we don't have right to move data to any where, only have a small ref database we can create a table. They said no budget to hire anyone else, even though buy Analysis, Report, Integration Service engines, DBA said they are other databases which &nbsp;I've never heard. Anyway, we only can deal with it under such circumstances.
</div>


<div>
Thank you all your contributions, I'm having hard time to decide which suggestions I can apply. Since some of them are out of my knowledge especially DBA's parts ( I can't expect on DBA's collaboration though, but it's good to have some knowledge) so please give me some time to figure out what can really help. Sorry for the delay comments.
</div>


<div>
Hi All,<br />
<br />
I took part suggestions of each of you. <br />
Thank you all the answers. <br />
<br />
Rose
</div>


<div>
<div class="content wysiwyg-content">
Hi experts,<br />
<br />
Anyone can offer any solutions for huge data. Our claim data is huge even though only 2 years data. For SSRS report, some parameters one year results are over 100 million rows, takes forever to deliver. e.g. some reports are 40 columns, &nbsp;need to join 20 tables, very slow.<br />
<br />
We are thinking to make two flat tables, pre-join all the tables<br />
or make a star schema data warehouse ...<br />
<br />
Please give me some suggestions, if these solutions work or you have any better ones. <br />
<br />
Thank you in advance. <br />
<br />
Rose
</div>
</div>


Hi experts,

Anyone can offer any solutions for huge data. Our claim data is huge even though only 2 years data. For SSRS report, some parameters one year results are over 100 million rows, takes forever to deliver. e.g. some reports are 40 columns,  need to join 20 tables, very slow.

We are thinking to make two flat tables, pre-join all the tables
or make a star schema data warehouse ...

Please give me some suggestions, if these solutions work or you have any better ones. 

Thank you in advance. 

Rose

Looking for solutions for huge data Report

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.

SSIS

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.

SSRS

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.