SSRS - Improvoing Performance

1. Avoid long running reports, by creating a report snapshot to run nightly or during low activity on your system.

2. Define pagination to hide overhead. for example, if a user requests a report that returns 1000 rows of data, you can use page breaking to render the initial page or results while the report server renders the rest of the pages in the background. without pagination, the user would have to wait until it rendered all 1000 rows.

3. Implement filters for performance.
If you use report-level filtering on data regions and data grouping rather than using query parameters as filters.

4. Enable drill down for detail.
Instead of giving users a report that shows all the data at once, design a summary report that
presents a subset of data and let usrs drill down to get more detail. using master detail
reports that take advantage of SSRS's navigation capabilities. Many parts of the report
controls have navigation property, which we use to create a hyperlink on the control for
passing the specific values.

Dimensional Vs ER Modeling

1. The relationship in a Dimensional model don't represent business rules instead they are navigational paths used to help write reports or create graphs. But whereas relationship in ER modeling represents business rules.
2. The primary goal of the ER modeling is to remove all non key data redundancy.
But Dimensional modeling controls data redundancy by confirming dimension and fact tables.
The table that has been confirmed can be used in more than one dimensional data model.

How to Create a Dimensional Model

1. Identity business process by business process and Each business process can be expressed
as a data mart
---- a modular , highly focused, richly detailed, incrementally designed componenet
of the datawarehouse.
Initially try to focus on the Single-Source data mart not on multiple source datamat.
example of single data marts are retail sales, purchase orders, shipments and payments.
example of multiple data mart is Customer profitability which combines revenue and costs that often come from sales and inventory databases.

2. Grain of the Fact table which is the level of detail that the table captures.