Jun 23, 2013. Microsoft have various sample database available for free download for their SQL Server Product. And integrated view of all your business data as the foundation for all of your traditional reporting, online analytical processing (OLAP) analysis, Key Performance Indicator (KPI) scorecards, and data mining.

Applies to: SharePoint Server 2013 Enterprise Topic Last Modified: 2016-12-16 Summary: Configure the AdventureWorks sample data for use with Excel, Excel Services, and PerformancePoint Services business intelligence scenarios. The AdventureWorks sample data set provides a sample database, data warehouse, and OLAP cube. The subsequent articles in this section make use of this sample data to demonstrate Business Intelligence capabilities in Excel 2013, Excel Services, and PerformancePoint Services. This article describes how to install and configure the AdventureWorks sample data set and configure a Business Intelligence Center on your SharePoint Server 2013 farm.

Installing the AdventureWorks sample data set consists of downloading the sample data, attaching the sample databases in SQL Server Management Studio, and deploying the sample OLAP cube using the SQL Server Data Tools. Creating a Business Intelligence Center consists of creating a new site collection with the Business Intelligence Center template using the SharePoint Central Administration website. The procedures for completing both of these tasks, plus procedures for configuring the required user access and permissions are included in this article. Before starting, read the following information about permissions and software requirements: • To deploy the AdventureWorks sample data, you must be a SQL Server and Analysis Services administrator.

• To create a Business Intelligence Center, you must be a farm administrator on the SharePoint Server 2013 farm. The subsequent articles make use of Excel Services and PerformancePoint Services. It is assumed that these are configured on your farm.

For information about deploying Excel Services, see and. For information about deploying PerformancePoint Services, see. When using Excel Services or PerformancePoint Services, user access can be provided using Windows Authentication with Kerberos delegation, the Secure Store Service, or, with OLAP data sources, the EffectiveUserName feature. It is assumed that one or more of these options are configured on your farm.

For information about configuring Secure Store, see and. For information about configuring the EffectiveUserName feature for OLAP data sources, see. Note: Because this file was downloaded from the Internet, it may be blocked by Windows. Right-click the file, and then click Properties. Click the Unblock button if it is present, and then click OK. (If the Unblock button is not present, then the file is not blocked.) • Copy AdventureWorks2012_Data.mdf to your default database directory (normally Program Files Microsoft SQL Server MSSQL11.MSSQLSERVER MSSQL DATA) or other location as designated by your database administrator. • Open SQL Server Management Studio.

• Connect to the database engine. • Right-click Databases, and then click Attach. • On the Attach Databases dialog box, click Add. • Navigate to the location where you copied AdventureWorks2012_Data.mdf, select the file, and then click OK. • Under “AdventureWorks2012” database details, select the row where File Type is Log. • Click Remove. Use the following procedure to download and deploy the AdventureWorksDW2012 data warehouse database.

To deploy the AdventureWorksDW2012 data warehouse • Download. Note: Because this file was downloaded from the Internet, it may be blocked by Windows.

Right-click the file, and then click Properties. Click the Unblock button if it is present, and then click OK.

(If the Unblock button is not present, then the file is not blocked.) • Copy AdventureWorksDW2012_Data.mdf to your default database directory (normally Program Files Microsoft SQL Server MSSQL11.MSSQLSERVER MSSQL DATA) or other location as designated by your database administrator. • Open SQL Server Management Studio. • Connect to the database engine.

• Right-click Databases, and then click Attach. • On the Attach Databases dialog box, click Add. • Navigate to the location where you copied AdventureWorksDW2012_Data.mdf, select the file, and then click OK. • Under “AdventureWorksDW2012” database details, select the row where File Type is Log. • Click Remove.

Note: You can determine if the SQL Server Data Tools are installed by clicking Start, All Programs, and then Microsoft SQL Server 2012 on the computer running SQL Server. If the SQL Server Data Tools are installed, it will appear in the menu under Microsoft SQL Server 2012. To install the SQL Server Data Tools • On the SQL Server 2012 DVD, run setup.exe.

• In the SQL Server Installation Center, on the left pane, click Installation. • In the right pane, click New SQL Server stand-alone installation or add features to an existing installation.

• On the Setup Support Rules page, click OK. • On the Product Updates page click Next. • On the Setup Support Rules page, click Next. • On the Installation Type page, select the Add features to an existing instance of SQL Server 2012 option, and select the instance where you want to install the SQL Server Data Tools.

• Click Next. • On the Feature Selection page, select the SQL Server Data Tools check box, and then click Next. • On the Installation Rules page, click Next. • On the Disk Space Requirements page, click Next. • On the Error Reporting page, click Next. • On the Installation Configuration Rules page, click Next. • On the Ready to Install page, click Install.

• When the installation completes, click Close. Once the SQL Server Data Tools have been installed, the next step is to create a login for the account running Analysis Services if one does not already exist. If you do not know what account is running Analysis Services, use the following procedure to determine the account. To determine the Analysis Services service account • On the computer running Analysis Services, click Start, click All Programs, click Microsoft SQL Server 2012, click Configuration Tools, and then click SQL Server Configuration Manager. • In the left pane, click SQL Server Services.

• In the right pane, find the instance of Analysis Services that you will be using, and note the account listed in the Log On As column. This is the account for which you must add a logon in SQL Server. If you do not already have a SQL Server login for the account running Analysis Services, use the following procedure to create one. To add a login for the Analysis Services service account • Open SQL Server Management Studio. • Connect to the database engine.

• Expand Security. • Right-click Logins and click New Login. • In the Login name text box, type the name of the account running the Analysis Services service. Note: Because this file was downloaded from the Internet, it may be blocked by Windows. Right-click the file, and then click Properties. Click the Unblock button if it is present, and then click OK. (If the Unblock button is not present, then the file is not blocked.) • Unzip the file to a location on the computer running Analysis Services.

• In the Enterprise folder, double-click AdventureWorksDW2012Multidimensional-EE.sln. • If the Choose Default Environment Settings dialog box appears, choose the Business Intelligence Settings option, and then click Start Visual Studio. • In Visual Studio, at the top of the Solution Explorer window, right click AdventureWorksDW2012Multidimensional-EE and click Deploy. • Close Visual Studio without saving changes. Once the databases and the cube have been deployed, you must grant your users access to them.

The following access is required: • Users who will be creating reports or dashboards in the subsequent articles in this section must have db_datareader access to the AdventureWorks databases and Read access to the AdventureWorks cube. • If you are using the unattended service account with Excel Services or PerformancePoint Services, that account must have db_datareader access to the AdventureWorks databases and Read access to the AdventureWorks cube. • If you are using Secure Store to refresh data in Excel Services or PerformancePoint Services, the target application credentials must have db_datareader access to the AdventureWorks databases and Read access to the AdventureWorks cube. We recommend that you use an Active Directory group containing the users to whom you want to grant access.

Use the following procedure to grant access to the AdventureWorks databases. If you choose to grant access to each user individually instead of using an Active Directory group, you must create a separate login for each user. To grant access to the AdventureWorks databases • In SQL Server Management Studio, connect to the database engine.

• Expand Security. • Right-click Logins, and then click New Login. • Click Search. • If you are using an Active Directory group, click Object Types, select the Groups check box, and then click OK. • On the Select User or Group dialog box, type the name of the Active Directory group or user to whom you want to grant database access, and then click OK. • Under Select a page, click User Mapping. • Select the Map check box for AdventureWorks2012, and then select the db_datareader database role membership check box.

• Select the Map check box for AdventureWorksDW2012, and then select the db_datareader database role membership check box. Use the following procedure to grant access to the AdventureWorks OLAP cube. To grant access to the AdventureWorks OLAP cube • In SQL Server Management Studio, connect to Analysis Services. • Expand Databases, and then expand AdventureWorksDW2012Multidimensional-EE. Note: If you will be using Secure Store or an unattended service account to access the cube, include those credentials here. • On the Select Users or Groups dialog box, click OK. • In the Select a page pane, click Cubes.

• In the right pane, in the Access column, click select Read from the dropdown list for Adventure Works and Mined Customers. • In the right pane, in the Local Cube/Drillthrough Access column, click select Drillthrough from the dropdown list for Adventure Works and Mined Customers. The subsequent articles in this section rely on a Business Intelligence Center site being present. If you have an existing Business Intelligence Center, you can use it.

However, we recommend creating a new Business Intelligence Center that is not part of your production environment. Use the following procedure to create a Business Intelligence Center. To create a Business Intelligence Center • On the SharePoint Central Administration website, under Application Management, click Create site collections. • On the Create Site Collection page: • Type a title in the Title text box. • Type the URL that you want to use in the URL text box. • Under Select a template, choose the Enterprise tab, and then select Business Intelligence Center. • In the Primary Site Collection Administrator section, type a name for the primary site collection administrator in the User name text box.

• Optionally, type a name for the secondary site collection administrator. • Optionally, select a quota template. The following table describes the permissions available in a Business Intelligence Center.

Business Intelligence Center permissions Account Permissions Visitors Read Read permissions enable users to view information in the Business Intelligence Center. Members Contribute Contribute permissions enable users to view and create items, such as reports, and save them to this site. Designers Design Design permissions enable users to view, create, and publish items that include dashboards. Owners Full Control Full Control permissions enable users to view, create, and publish dashboard content, and to view or edit user permissions For the scenarios described in the subsequent articles in this section, users will need the following permissions. • Users publishing workbooks to the Business Intelligence Center from Excel require Contribute permissions and must be added to the Members group. • Users publishing dashboards from PerformancePoint Dashboard Designer require Design permissions and must be added to the Designers group. • Users who are only viewing reports or dashboards in the Business Intelligence Center but not publishing only require Read permission and can be added to the Visitors group.

Use the following procedure to configure permissions for the Business Intelligence Center. To set permissions in the Business Intelligence Center • In the Business Intelligence Center, click Share. • Type the names of the users or groups to whom you want to grant access. • Click Show Options.

• On the Select a group or permission level dropdown list, select the permission level that you want. • Click Share.

Problem In my previous tip, we explored the new PowerShell module SQLSERVER along with the new cmdlets to get error log information. In this tip we will explore a few more new cmdlets that allow us to read and write SQL Server database tables.

Solution With the release of Microsoft SQL Server 2016 Management Studio version 16.4.1, new cmdlets were introduced to read and write SQL Server database tables which we will cover in this tip. To find these new PowerShell commands, you can run the below PowerShell command. Write-SqlTableData [[-ServerInstance] ] -InputData [-ConnectionTimeout ] [-Credential ] [-DatabaseName ] [-Force] [-IgnoreProviderContext] [-Passthru] [-SchemaName ] [-SuppressProviderContextWarning] [-TableName ] [-Timeout ] [ ] Suppose we want to capture the output of a SQL query into a database table. If the specified object doesn't exist, we can specify the -force parameter to create the missing SQL Server object. You will still need sufficient permission to create the object. The below command runs a SELECT statement and writes the output to a table called testdatabases. Here we specified the -force parameter, so the table should be automatically created.

Download Your Smile Is Heavenly Mp3. Post a comment or let the author know this tip helped. All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published.

Required fields are marked with an asterisk (*). *Name *Email Notify for updates *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting. SQL tips: *Enter Code.