Data sets and data sources in MSBI Reports

Reporting is an integral part in MSBI. Once you create reports, you will probably want to develop more than one as part of the same document. For instance, you may be asked to produce a summary report that allows the user to pick an item that will automatically open another report showing more information about that item.

Though these reports show different data, they are highly likely to use the same database. You can create a project-level database link and then use the same link to create data sets in all of your reports to avoid duplicating the connection details and keeping it tidy and manageable. In this article let us look at briefly about SSRS Data sources and datasets in MSBI reporting.

To get in depth knowledge on Microsoft business intelligence, enrich your skills on msbi online training professionals

MSBI Data Sources Vs Datasets

 You need to understand the difference between MSBI data sources and datasets is important.

MSBI Data Sources

A data source includes information of the database server to which you will be linking, the login to use and the database to use.

MSBI Datasets

 A dataset includes the particular query used to retrieve data for a specific report.

You'll definitely want to produce more than one as part of the same project once you build reports. For example, you may be asked to produce a summary report which allows the user to pick an item which will automatically open another report showing more details about that item.

Although these reports show different data, it is highly probable that they will use the same database. To avoid duplicating the link details and keeping things smooth and manageable, you should build a database connection at the project level, and then use the same connection to construct databases in all your reports.

Creating a MSBI Reports Dataset

1. Right-click Software Explorer Shared Data Sources and pick Add New Data Source

2. Data source for Adventure Works characters.

3. Leave the SQL Server Class on Microsoft and click Edit ... Defining a list of links.

4. Type the name of the SQL Server containing the Adventure Works 2012 database when you open the Link Properties window. If you do it on a development machine, you can describe your local SQL Server as the 'local host.'

5. Specify the normal login information for this site, and then select the Adventure Works folder from the drop-down list.

6. To confirm all is fine, click on Check Link and then press Yes

7. Check the link string you have generated and then click OK again.

Notice that inside Solution Explorer a new, shared data source has been created.

Shared Data Sources

A data source resembles a data address. It contains the string for link. It may include the server name and stored credentials for access to a SQL Server database, or even anything as basic as a text file location. In a study, you can use multiple data sources if the data is coming from more than one location. Get more skills from Msbi online course

Data sources can be stored in two ways. It may be sharing at the project level or embedding them into the individual study. My advice is always to share the sources of data, especially if you have dozens or hundreds of reports pointing to the same database. By developing common data sources, you or the DBA would have less  to handle until the reports are released on the Web Portal.

Creating a MSBI Reports Data source

1. Right-click Shared Data Sources in the Application Explorer, and pick Add New Data Source.

2.  Title: Adventure Works data source.

3. Leave the Microsoft SQL Server Class, and press Edit ... to define a list of connections.

4. When opening the Link Properties window type the SQL Server name that contains the Adventure Works2012 database. You can define your local SQL Server as 'local host' if you do everything on a development machine.

5. Specify the normal login information for this site and then from the drop-down list pick the Adventure

Works folder.

6. Click on Test Link to confirm all is good, and then press Yes 7. Review the link string you created, then click OK again.

Notice that a new shared data source has been developed inside Solution Explorer.    

    

Shared Datasets

 A dataset is the query that executes while viewing the text. The type of query depends on the source of that data. For example, the query would be written in T-SQL while dealing with the SQL Server databases, or you can have the option of calling stored procedures. While there is a Shared Datasets folder, the dataset should be contained in the report most of the time, and not shared. The justification for embedding data sets is that often queries aren't repeated. For example, there are exceptions to the parameter lists which are repeated in several papers. When a dataset is shared then when you deploy the project it will be written. You probably won't want tens or hundreds of reports to clutter the Web Portal with every dataset.

Before you can add a dataset the report must first exist. Rather than creating a dataset, let us create a new blank report first

.

Creating a New MSBI Report

You should take care not to turn off the Report Wizard while making a new blank report. To build a new, blank study, follow these steps.

●        Right-click the Files folder to select Add New Object. Make sure to NOT select Add New Post, as this will activate Post Wizard.

●        Add new object, do not add new report You can see the Add New object dialog displayed. Select Report, and assign a name to the article. Be sure to give the report a meaningful name when working on reports for your company, which will make sense for the people who run the project.

●        Add New Element dialog to build the report. Then click OK.

●        You can now see it in the Solution Explorer.

●        Display the new report in Design view

●        The new report may be available. If not, then double-click that to open it.

Report Layout in MSBI

 Now you've got a new report. You can see the Report Data window on the left hand side of the panel. Make sure that the report canvas is selected and type CTRL+ALT+D.) It shows the Report Data window. In this window everything is unique to the document that you are editing. All through these papers you will learn about the different directories.

 Right-click the Data Sources folder in the Data Summary window and pick Add Data Source. This data source will point to a common connection, the source of data you've generated before. Offer it a tag, and press Use reference shared source data. From the list pick the data source for Adventure Works2017. Note that you can assign them the same name but I'm naming them differently for comparison purposes.

 Click on OK to establish the source of the data.

You can see it in the window once you build the data source. Remember that it has a little arrow on the icon showing it is pointing to a common source of data,

You now have everything you need to build an embedded data package.

Embedded data set

It makes sense to embed most data sets into the reports rather than distribute them. Shared datasets end up being published as reusable Report Parts for advanced users to create ad-hoc reports. For this, most datasets aren't needed and sharing them would clutter the folder and make it harder for end users to find what they need. In the later article on parameters, you'll learn when it makes sense to construct mutual datasets.

Make sure your database pointing to the base of shared data is in place and follow these steps to build a dataset.

Fill in a name which will identify your question. Select Use a dataset built into my paper. A drop down box appears for the source of the data. Select the source of the data that you just created.

Conclusion:

I hope you reach a conclusion about Datasets and data sources in MSBI. You can learn more through Msbi online training Hyderabad.