SSRS--Interview Basic Questions
1). What is SSRS (Sql Server Reporting Services) ?
Ans:
Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources.Which allow you to create tabular, matrix, rectangle, sub-report,,charts, Gauge, indicator reports in managed environment. Like
a) Development Tool
b) Configuration Tool
c) Administration Tool
d) Report Viewing Tool
2) What is report Manager?Ans:
Report Manager is a Web-based report access and management tool that you use to administer a single report server instance from a remote location over an HTTP connection. You can also use Report Manager for its report viewer and navigation features. You can use Report Manager to perform the following tasks:
3) Different Types of Roles provided by SSRS ?Ans:
1) Browsers
2) Content Manager
3) My Reports
4) Publishers
5) Report Builder
4) Describe the different processing mode by SSRS ?Ans: Two types:
Ans:
rdl(RDL stands for Report Definition language). This is an XML file which stores query information, data source information, etc. which are required to generate report.
11) Explain the Architecture of SSRS ?
Ans:
It is a multi tired, included with application , server, data layers. This Architecture is scalable and modular. A single installation can be used accross multiple computers .
It include following components -
1) Report Manager
2) Report Designer
3) browsers type supported by Reporting Services
4) Report Server
5) Report Server and Command line utilities
6) Report Server Database
7) Data Sources
SSRS is an optional package which you can select to install while installing SQL Server. SSRS in turn is made up of number of components. The simplest diagram I could find that describes these components and their deployment was fromTechNet
As you can see in above diagram when you install SSRS it creates Report Server Databases in your SQL Server Instance. These databases are ReportServerDB and ReportServerTempDB which are used to store report configurations and other things including Caching, Session, etc. that improvise the overall performance. You have an option of installing other components like Report Manager and Report Server on the same machine where SQL Server instance is running or you can install them on a different server (typical enterprise setup). An important thing to note here is if you opt for latter you would end up paying for 2 or more SQL Server licenses.
As it turns out there are 3 distinguish components of SQL Server Reporting Services:
1) Report Server: It’s an overloaded term. Largely used to indicate a set of components that allow interaction with Report Server database. SSRS provides Web Services (.asmx) which allows LOB applications to interact with Report Server database directly (http://computername/ReportServer/reportservicexxxx.asmx where xxxx is version of SSRS). SSRS 2005 created virtual directories for Report Server & Report Manager (discussed next), but SSRS 2008 leverages the OS level HTTP listener making SSRS independent of IIS. This allows bundling of Report Server & Report Manager within a windows Service ReportingServicesService.exe. The name of this Windows Service is ReportServer. ReportingServicesService.exe functionality also includes report processing, scheduling (auto generated reports), subscriptions (mailers), etc.
2) Report Manager: An ASP.NET web based application (http://computername/Reports) that in turn interacts with Report Server Web Services. As the name indicates Report Manager allows you to manage reports in terms configuring security access, organizing them into folders (non of these folders map to physical directories but are stored as details in Report Server Database), subscribing to them, etc. One can also create reports (see next point as to how) and deploy them to Report Server Database using Report Manager. This is handy for some restricted user / production scenarios, though most developers prefer to do the deploy reports from BI studio. As discussed earlier with SSRS 2008 this component is bundled with ReportServer windows service.
3) Report Designer: There would be few guys in your team whom you may want to designate as Report Designers. Report Designers can design reports using VS.NET Business Intelligence projects (Report Server Project). Report Designers create data sources (normally a shared data source (.rds) that’s used across a set of reports), create the dataset (using queries / stored procedures on top of data source), define relevant report parameters (mapped to datasets for value retrieval via Report data window), field formats (using properties window with pervasive VB expressions – e.g. formatting a textbox to display currency decimals) and create layouts (e.g. Grouping). Once they are done with designing their reports (.rdl files – described later) they can test (preview) them and publish them via Report Server (this is done by providing the Report Server URL in project properties and SSRS there creates a specific folder for your project). Once published these reports are available for end user consumption. Advance scenarios like interacting with Excel may require a third party product like OfficeWriter.
There are few other important aspects of SSRS which one should be familiar with.
Report Builder is a another tool which is targeted at business users who want to generate custom reports on fly. Report Builder is a ClickOnce application, intuitive and easy to use but doesn’t support all the options available with VS.NET. It’s also possible to install ReportBuilder as a standalone application.
Report Model is the base for report creation with Report Builder. It’s a simplified view of relational database targeted at business users for ad hoc report creation. Report models are created using BI Development Studio (Report Model Project – .smdl files). A report model is built on top of a Data Source View (.dsv) that defines a logical model based on one or more data sources. Models generated mainly consist of entities (relational tables), fields (attributes of a relational table) and roles (entity relationships – 1-1, 1-*, *-1). Models also contain other attributes like aggregate values that would help ease the reporting for end users. Post creation report model has to be deployed in similar way as reports. You can also use Report Model as a data source for generating reports via Report Designer. While it’s easy to deploy Report model from BI development studio, to deploy report model manually e.g. in production requires you to merge the .smdl and .dsv files.
RDL – this is another term you would run into while talking about SSRS. RDL stands for Report Definition language. This is an XML file which stores query information, data source information, etc. which are required to generate report. There is another type of report definition – RDLC (Report Definition Language Client-side) which don’t store any of above configurations. RDLC is a client side component (VS.NET Application Projects) to which you can pass data (e.g. via DataSet) coming from any of data sources. RDLC can be useful for scenarios like implementing custom pagination (SSRS 2005 pagination by default is client side pagination).
SSRS Security is primarily windows based. When a user accesses the Report Manager Application or ASMX Web Services he has to authenticate with a valid domain username / password. On successful login SSRS determines the role of the user (custom or built-in ones like Browser / Content Manager, etc.) and displays only those reports / folders to which user has access. There are few variations in the security implementation I have come across that don’t rely on Windows Authentication. Some projects tend to control role like content manager for pushing reports (.rdl files) to production with help of rs.exe. All users have an implicit role of a Browser and application layer security determines which reports the user should have access to. In case you want go ahead and roll out your custom authentication that flows security all the way down, SSRS allows you that too. If you are generating reports by connecting to remote data sources for accessing images, etc. you might have to configure Unattended Execution Account.
If u want some more details regarding this follow this link http://msdn.microsoft.com/en-us/library/bb522673.aspx
Ans:
Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources.Which allow you to create tabular, matrix, rectangle, sub-report,,charts, Gauge, indicator reports in managed environment. Like
a) Development Tool
b) Configuration Tool
c) Administration Tool
d) Report Viewing Tool
2) What is report Manager?Ans:
Report Manager is a Web-based report access and management tool that you use to administer a single report server instance from a remote location over an HTTP connection. You can also use Report Manager for its report viewer and navigation features. You can use Report Manager to perform the following tasks:
- View, search, print, and subscribe to reports.
- Create, secure, and maintain the folder hierarchy to organize items on the server.
- Configure role-based security that determines access to items and operations.
- Configure report execution properties, report history, and report parameters.
- Create report models that connect to and retrieve data from a Microsoft SQL Server Analysis Services data source or from a SQL Server relational data source.
- Set model item security to allow access to specific entities in the model, or map entities to predefined clickthrough reports that you create in advance.
- Create shared schedules and shared data sources to make schedules and data source connections more manageable.
- Create data-driven subscriptions that roll out reports to a large recipient list.
- Create linked reports to reuse and repurpose an existing report in different ways.
- Launch Report Builder to create reports that you can save and run on the report server.
3) Different Types of Roles provided by SSRS ?Ans:
1) Browsers
2) Content Manager
3) My Reports
4) Publishers
5) Report Builder
4) Describe the different processing mode by SSRS ?Ans: Two types:
- Local Processing Mode :- Process Reports In The Client Machine.
- Remote Processing Mode :- Render Server Report That Are Processed On SSRS Report Server.
Ans:
- RSConfig.exe- Configuration of connection properties between report server to repository database.
- RSkeyMgmt.exe- Management of encryption key via cmd line
- RS.exe- Scripting of Report deployment
Ans:
- Management Endpoint
- Execution Endpoint
- SharePoint Proxy Endpoint
Ans: To encrypt the credentials, connection string can be taken backup and restore when required from configuration manager.
8) Explain Reporting Lifecycle ?
Ans:
- Report Designing- It is done in V.S Report Designer.
- Report Processing- Bringing the report definition with data from the report datasource. It performs all grouping, sorting & filtering calculation.
- Report Rendering- Report rendering start by passing the report instance to a specific rendering extension (HTML or pdf formats) As a final step, the report is rendered to a specific output document.
Ans:
- Expand the server or utilizing the report service of the another database.
- Replication of data continuously: - Using no-lock, the issue of locking can will be resolved and the performance of the query can be improved. This can be done by using dirty read at the time of duplicating the data is unavailable.
Ans:
rdl(RDL stands for Report Definition language). This is an XML file which stores query information, data source information, etc. which are required to generate report.
11) Explain the Architecture of SSRS ?
Ans:
It is a multi tired, included with application , server, data layers. This Architecture is scalable and modular. A single installation can be used accross multiple computers .
It include following components -
1) Report Manager
2) Report Designer
3) browsers type supported by Reporting Services
4) Report Server
5) Report Server and Command line utilities
6) Report Server Database
7) Data Sources
SSRS is an optional package which you can select to install while installing SQL Server. SSRS in turn is made up of number of components. The simplest diagram I could find that describes these components and their deployment was fromTechNet
As you can see in above diagram when you install SSRS it creates Report Server Databases in your SQL Server Instance. These databases are ReportServerDB and ReportServerTempDB which are used to store report configurations and other things including Caching, Session, etc. that improvise the overall performance. You have an option of installing other components like Report Manager and Report Server on the same machine where SQL Server instance is running or you can install them on a different server (typical enterprise setup). An important thing to note here is if you opt for latter you would end up paying for 2 or more SQL Server licenses.
As it turns out there are 3 distinguish components of SQL Server Reporting Services:
1) Report Server: It’s an overloaded term. Largely used to indicate a set of components that allow interaction with Report Server database. SSRS provides Web Services (.asmx) which allows LOB applications to interact with Report Server database directly (http://computername/ReportServer/reportservicexxxx.asmx where xxxx is version of SSRS). SSRS 2005 created virtual directories for Report Server & Report Manager (discussed next), but SSRS 2008 leverages the OS level HTTP listener making SSRS independent of IIS. This allows bundling of Report Server & Report Manager within a windows Service ReportingServicesService.exe. The name of this Windows Service is ReportServer. ReportingServicesService.exe functionality also includes report processing, scheduling (auto generated reports), subscriptions (mailers), etc.
2) Report Manager: An ASP.NET web based application (http://computername/Reports) that in turn interacts with Report Server Web Services. As the name indicates Report Manager allows you to manage reports in terms configuring security access, organizing them into folders (non of these folders map to physical directories but are stored as details in Report Server Database), subscribing to them, etc. One can also create reports (see next point as to how) and deploy them to Report Server Database using Report Manager. This is handy for some restricted user / production scenarios, though most developers prefer to do the deploy reports from BI studio. As discussed earlier with SSRS 2008 this component is bundled with ReportServer windows service.
3) Report Designer: There would be few guys in your team whom you may want to designate as Report Designers. Report Designers can design reports using VS.NET Business Intelligence projects (Report Server Project). Report Designers create data sources (normally a shared data source (.rds) that’s used across a set of reports), create the dataset (using queries / stored procedures on top of data source), define relevant report parameters (mapped to datasets for value retrieval via Report data window), field formats (using properties window with pervasive VB expressions – e.g. formatting a textbox to display currency decimals) and create layouts (e.g. Grouping). Once they are done with designing their reports (.rdl files – described later) they can test (preview) them and publish them via Report Server (this is done by providing the Report Server URL in project properties and SSRS there creates a specific folder for your project). Once published these reports are available for end user consumption. Advance scenarios like interacting with Excel may require a third party product like OfficeWriter.
There are few other important aspects of SSRS which one should be familiar with.
Report Builder is a another tool which is targeted at business users who want to generate custom reports on fly. Report Builder is a ClickOnce application, intuitive and easy to use but doesn’t support all the options available with VS.NET. It’s also possible to install ReportBuilder as a standalone application.
Report Model is the base for report creation with Report Builder. It’s a simplified view of relational database targeted at business users for ad hoc report creation. Report models are created using BI Development Studio (Report Model Project – .smdl files). A report model is built on top of a Data Source View (.dsv) that defines a logical model based on one or more data sources. Models generated mainly consist of entities (relational tables), fields (attributes of a relational table) and roles (entity relationships – 1-1, 1-*, *-1). Models also contain other attributes like aggregate values that would help ease the reporting for end users. Post creation report model has to be deployed in similar way as reports. You can also use Report Model as a data source for generating reports via Report Designer. While it’s easy to deploy Report model from BI development studio, to deploy report model manually e.g. in production requires you to merge the .smdl and .dsv files.
RDL – this is another term you would run into while talking about SSRS. RDL stands for Report Definition language. This is an XML file which stores query information, data source information, etc. which are required to generate report. There is another type of report definition – RDLC (Report Definition Language Client-side) which don’t store any of above configurations. RDLC is a client side component (VS.NET Application Projects) to which you can pass data (e.g. via DataSet) coming from any of data sources. RDLC can be useful for scenarios like implementing custom pagination (SSRS 2005 pagination by default is client side pagination).
SSRS Security is primarily windows based. When a user accesses the Report Manager Application or ASMX Web Services he has to authenticate with a valid domain username / password. On successful login SSRS determines the role of the user (custom or built-in ones like Browser / Content Manager, etc.) and displays only those reports / folders to which user has access. There are few variations in the security implementation I have come across that don’t rely on Windows Authentication. Some projects tend to control role like content manager for pushing reports (.rdl files) to production with help of rs.exe. All users have an implicit role of a Browser and application layer security determines which reports the user should have access to. In case you want go ahead and roll out your custom authentication that flows security all the way down, SSRS allows you that too. If you are generating reports by connecting to remote data sources for accessing images, etc. you might have to configure Unattended Execution Account.
If u want some more details regarding this follow this link http://msdn.microsoft.com/en-us/library/bb522673.aspx
Drill Down Report- Means navigate the summary level to detail level in the same report.
Drill Through Report- Navigation from one report to another report.
Linked Report- It is a report server item that provides an access point to an existing report.Conceptually it is similar to a program shortcut that you can use to run a program or open file.
What is a subscription in SSRS?
- A subscription in SSRS is a way to automatically deliver report snapshots, rendered reports, or report links to users or groups at scheduled times.
What are the different types of subscriptions in SSRS?
- SSRS supports several types of subscriptions, including:
- Standard subscriptions: Deliver reports via email or a file share.
- Data-driven subscriptions: Deliver reports to a dynamic list of recipients based on query results.
- Cached reports: Cache reports on a schedule to improve performance.
- Snapshot subscriptions: Deliver report snapshots to users at scheduled times.
- SSRS supports several types of subscriptions, including:
How do you create a subscription in SSRS?
- To create a subscription in SSRS:
- Open the report in Report Manager or Report Builder.
- Click on the "Subscriptions" tab.
- Click "New Subscription" and follow the wizard to set up the subscription options, including delivery method, schedule, and parameters.
- To create a subscription in SSRS:
What delivery options are available for subscriptions in SSRS?
- SSRS supports various delivery options for subscriptions, including:
- Email: Deliver reports as attachments or embedded in the body of an email.
- File share: Save reports to a file share location.
- SharePoint library: Publish reports to a SharePoint library.
- Web service: Send reports to a custom application via a web service.
- Custom delivery extension: Implement custom delivery options using custom delivery extensions.
- SSRS supports various delivery options for subscriptions, including:
What is a data-driven subscription in SSRS?
- A data-driven subscription in SSRS allows you to dynamically determine the recipients and delivery options of a report subscription based on the results of a query.
How do you configure a data-driven subscription in SSRS?
- To configure a data-driven subscription in SSRS:
- Create a dataset that retrieves the subscription parameters, such as recipients and delivery options.
- Configure the subscription to use the dataset as the recipient query.
- Map dataset fields to subscription parameters, such as email addresses and delivery formats.
- Schedule the subscription to run at regular intervals.
- To configure a data-driven subscription in SSRS:
What are the benefits of using data-driven subscriptions in SSRS?
- Data-driven subscriptions offer several benefits, including:
- Dynamic recipient selection based on query results.
- Flexibility to handle changing recipient lists without manual intervention.
- Ability to customize delivery options for different recipients or groups.
- Data-driven subscriptions offer several benefits, including:
How do you troubleshoot subscription delivery failures in SSRS?
- To troubleshoot subscription delivery failures in SSRS:
- Check the SSRS execution log for errors or warnings related to the subscription.
- Verify the subscription settings, including delivery method, schedule, and parameters.
- Test the subscription manually to ensure it runs successfully.
- Check email server settings and permissions if using email delivery.
- Review data-driven subscription query results for any issues with recipient selection.
- To troubleshoot subscription delivery failures in SSRS:
Can you schedule a subscription to run on specific weekdays only?
- Yes, you can schedule a subscription to run on specific weekdays by configuring the subscription schedule with the desired weekdays and times.
How do you manage and monitor subscriptions in SSRS?
- Subscriptions in SSRS can be managed and monitored using the Report Server web portal or SQL Server Management Studio (SSMS). You can view, modify, delete, and troubleshoot subscriptions through these interfaces, as well as monitor subscription status and history.
What is the Report Server Execution Log? How can it be useful in subscription management?
- The Report Server Execution Log is a database that stores information about the execution of SSRS reports, including subscription executions. It can be useful in subscription management for troubleshooting subscription delivery failures, analyzing usage patterns, and auditing report access.
Can you explain how caching affects subscriptions in SSRS?
- Caching in SSRS allows reports to be stored in memory or disk for faster access. When a subscription is configured to use cached reports, it delivers the cached version of the report at the scheduled time. However, it's important to consider cache expiration settings to ensure that subscribers receive up-to-date data.
How do you handle large-scale subscriptions in SSRS?
- Large-scale subscriptions in SSRS, where reports are delivered to a large number of recipients, can pose performance and scalability challenges. Techniques for handling large-scale subscriptions include optimizing report performance, distributing subscriptions across multiple schedules, and using caching and snapshotting strategically.
What are the limitations of using email delivery for SSRS subscriptions?
- Email delivery for SSRS subscriptions has limitations related to email server capacity, message size restrictions, and security considerations. Additionally, emails may be subject to spam filters or blocked by email providers, affecting deliverability.
How can you secure sensitive data in SSRS subscriptions?
- SSRS provides several options for securing sensitive data in subscriptions, including:
- Encrypting email delivery using SSL/TLS.
- Implementing data-driven subscriptions with secure parameter handling.
- Restricting access to reports and subscriptions based on user roles and permissions.
- SSRS provides several options for securing sensitive data in subscriptions, including:
Can you automate the management of SSRS subscriptions?
- Yes, the SSRS web service API and PowerShell scripting can be used to automate the management of SSRS subscriptions. These tools allow you to create, modify, and delete subscriptions programmatically, as well as monitor subscription status and history.
What is the difference between standard and data-driven subscriptions in terms of scalability and flexibility?
- Standard subscriptions in SSRS are suitable for delivering reports to a fixed set of recipients on a predefined schedule. They are less flexible than data-driven subscriptions, which can dynamically determine recipients and delivery options based on query results. Data-driven subscriptions offer greater scalability and flexibility for managing large or dynamic recipient lists.
How do you implement conditional subscriptions in SSRS?
- Conditional subscriptions in SSRS involve delivering reports to different recipients or with different parameters based on certain conditions. This can be achieved using data-driven subscriptions with conditional logic in the query or by creating multiple subscriptions with different schedules or parameters.
What are some best practices for optimizing SSRS subscriptions for performance and scalability?
- Some best practices for optimizing SSRS subscriptions include:
- Limiting the number of concurrent subscriptions to avoid overloading the report server.
- Using caching and snapshotting to improve performance for frequently accessed reports.
- Optimizing report queries and rendering settings to minimize execution time.
- Monitoring subscription usage and performance regularly to identify bottlenecks and areas for improvement.
- Some best practices for optimizing SSRS subscriptions include:
How do you handle multi-language subscriptions in SSRS for international users?
- Multi-language subscriptions in SSRS involve delivering reports in different languages based on user preferences or recipient attributes. This can be achieved using localization features in SSRS, such as parameterized reports with language-specific datasets or using expressions to dynamically generate localized content.
Comments
Post a Comment