Localizing a report model

Reports and report models can be localized at many levels. Following are some ideas around the current support in SQL Server 2008 for each type of localization.   –          Localized metadata at report design time o   Example: Spanish report author sees “Cliente” instead of “Customer” in model explorer o   Report models do not support multiple…


Considerations for a large report model

Customer report models that vary in size from a few to a few hundred entities. Little Northwind with its 10 or so entities comes in around 200K, but we’ve seen models a hundred times that size (over 20 MB). One of the key drivers of model size is the constraint that you cannot build a…


A better way to model inheritance

I’ve been playing around with a report model we use internally here in the SQL Server product group. At a conceptual level, the data being modeled makes heavy use of inheritance (EntityA “is a” EntityB), but in working with the model and with Report Builder, I’m finding some significant advantages to using the Role.ExpandInline property…


Creating a role to one of several related items

I call this the Primary Address problem, because a classic example is when you have a Customer table and an Address table, and each customer can have many addresses (Primary, Billing, Shipping, etc.), but no more than one of any given type. If you have a FK constraint defined, the report model wizard will automatically…


Evolving your report model over time

Many factors combine to make report models highly likely to change and evolve over time. Sometimes the underlying schema changes. Sometimes new stuff is added. Sometimes you just want to improve how the schema is presented to users. Report models are designed to accommodate just these kinds of changes. One of my earlier posts mentions…


Creating a report model that can be used against multiple databases

Sometimes it is useful to create a report model that can be used against multiple databases that have the same structure, but reside on different servers and/or have different schema qualifiers. Uploading a second copy of a report model and pointing it at a different database is certainly easy enough, but you can run into…


How to create a 'company' security filter for a hosted application

Several customers have asked how to restrict data visibility in a report model for a hosted application, where every table has a “CompanyID” column, and every user that accesses the system is associated (via some other table) with exactly one company. The straightforward solution is to create a security filter on each corresponding entity; the filter…


Collapsing a many-to-many relationship in a report model

Often a many-to-many relationship exists between two entities where the intermediate entity has nothing on it except the connecting roles. For example, it might be that an Employee can be assigned to many Regions, and each Region can have many Employees assigned to it, but there is nothing interesting about an Employee Region (the intermediate entity). In…


Report model denormalization: Why, How, and When

Relational databases are often heavily normalized to improve performance, reduce storage requirements, and ensure data consistency. While performance may be relevant to report execution, neither of these reasons is relevant when presenting the schema to an end-user at design time. Hence denormalization is often a major feature of a well-designed SQL Server 2005 report model….


What to do with IdentifyingAttributes and DefaultDetailAttributes?

SQL 2005 report models will ship with a curious and admittedly incomplete solution for handling “the information users typically want to see about an instance of entity X”. The problem is that there are many scenarios for which the answer to that question differs: Scenarios 1. Dropdown selection (applies to entities with a relatively small…