AdventureWorksDW ETL packages

Where can i get the AdventureWorksDW ETL packages. I attched the AdventureWorks,AdventureWorksDW databases. Now i need ETL packages to transfer the data froam AdventureWorks to AdventureWorksDW.

If anybody knows about this please help me..............

Regards,

Hanu

If you installed the samples and tools when you installed SQL Server, look here: C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\AWDataWarehouseRefresh

If you did not, then look here: http://www.codeplex.com/MSFTISProdSamples/Release/ProjectReleases.aspx?ReleaseId=4039

|||

Thank you very much for your reply.

Regards

Hanu

sql

AdventureWorksDW

Hi everybody,

I'm searching for a full description of AdventureWorksDW and also its relationship with AdventureWorks database.

Tanx

Hello. I have not found a full documentation but perhaps Books On Line can tell you something.

A short description is that AdventureWorks is the OLTP-version(read transaction system version) of a fictive company that sells bicycles and related equipment on the internet.

AdventureWorksDW is the data warehouse structure of this company's business that include several starschemas /data marts. This structure is for analysis, not for entering transactions. After installing tools and samples, that is part of the SQL Server 2005 installation, you will also find an Aventure Works cube project built on top of the AdventureWorksDW database.

HTH

Thomas Ivarsson

AdventureWorks\AdventureWorksDW question

I thought that all data in AdventureWorksDW came from the OLTP AdventureWorks database. But....then I looked at DimTime table in "DW". I doubt that level of detail exists in the OLTP database.

Is it common to add new data and fields to the OLAP side that didn't exist in the OLTP side?

Pup

Dear barkingpup,

Can you plz give more details about you question ?

OLAP create time dimension based on the_date column of time dimension table, you have to select which time heirarchy you want.

|||

Hello Syed Mahmood Ali,

i think that barkingpup talked about the data in the AdventureWorks and the AdventureWorksDW. He think that AdventureWorksDW is a data mart(data warehouse) created from the AdventureWorks database which would be the OLTP. And his question is, if its the case, is it usual to add data in the data mart which are not in the OLTP. According to me, in the data mart, the data come from only the OLTP. Perhaps from different database but which set up the OLTP system. AdventureWorksDW is only an sample and the conception is quite different than AdventureWorks. So the needed data for the sample are differents too...

I hope that will answer the barkingpup's question.

|||i also gone throug adventuerwork and adventuerworkdw still doubt are adventuerwork is the only source for adventuerworkdw can anybody guide me for creating diagram for complet adventuerwokrdw datamart.|||

Hello. You build the Time dimension in SSIS(Integration Services) or in the Data Source View(by a named calculation) using TSQL Date functions on the Order or Invoice Date in the OLTP database.

One of the most useful us the TSQL Datepart function where you can derive month, quarters and years from a date column.

You will only need a single date column to build an entire time dimension.

HTH

Thomas Ivarsson

AdventureWorks\AdventureWorksDW question

I thought that all data in AdventureWorksDW came from the OLTP AdventureWorks database. But....then I looked at DimTime table in "DW". I doubt that level of detail exists in the OLTP database.

Is it common to add new data and fields to the OLAP side that didn't exist in the OLTP side?

Pup

Dear barkingpup,

Can you plz give more details about you question ?

OLAP create time dimension based on the_date column of time dimension table, you have to select which time heirarchy you want.

|||

Hello Syed Mahmood Ali,

i think that barkingpup talked about the data in the AdventureWorks and the AdventureWorksDW. He think that AdventureWorksDW is a data mart(data warehouse) created from the AdventureWorks database which would be the OLTP. And his question is, if its the case, is it usual to add data in the data mart which are not in the OLTP. According to me, in the data mart, the data come from only the OLTP. Perhaps from different database but which set up the OLTP system. AdventureWorksDW is only an sample and the conception is quite different than AdventureWorks. So the needed data for the sample are differents too...

I hope that will answer the barkingpup's question.

|||i also gone throug adventuerwork and adventuerworkdw still doubt are adventuerwork is the only source for adventuerworkdw can anybody guide me for creating diagram for complet adventuerwokrdw datamart.|||

Hello. You build the Time dimension in SSIS(Integration Services) or in the Data Source View(by a named calculation) using TSQL Date functions on the Order or Invoice Date in the OLTP database.

One of the most useful us the TSQL Datepart function where you can derive month, quarters and years from a date column.

You will only need a single date column to build an entire time dimension.

HTH

Thomas Ivarsson

AdventureWorks\AdventureWorksDW question

I thought that all data in AdventureWorksDW came from the OLTP AdventureWorks database. But....then I looked at DimTime table in "DW". I doubt that level of detail exists in the OLTP database.

Is it common to add new data and fields to the OLAP side that didn't exist in the OLTP side?

Pup

Dear barkingpup,

Can you plz give more details about you question ?

OLAP create time dimension based on the_date column of time dimension table, you have to select which time heirarchy you want.

|||

Hello Syed Mahmood Ali,

i think that barkingpup talked about the data in the AdventureWorks and the AdventureWorksDW. He think that AdventureWorksDW is a data mart(data warehouse) created from the AdventureWorks database which would be the OLTP. And his question is, if its the case, is it usual to add data in the data mart which are not in the OLTP. According to me, in the data mart, the data come from only the OLTP. Perhaps from different database but which set up the OLTP system. AdventureWorksDW is only an sample and the conception is quite different than AdventureWorks. So the needed data for the sample are differents too...

I hope that will answer the barkingpup's question.

|||i also gone throug adventuerwork and adventuerworkdw still doubt are adventuerwork is the only source for adventuerworkdw can anybody guide me for creating diagram for complet adventuerwokrdw datamart.|||

Hello. You build the Time dimension in SSIS(Integration Services) or in the Data Source View(by a named calculation) using TSQL Date functions on the Order or Invoice Date in the OLTP database.

One of the most useful us the TSQL Datepart function where you can derive month, quarters and years from a date column.

You will only need a single date column to build an entire time dimension.

HTH

Thomas Ivarsson

AdventureWorks, invalid object name error

Hi, I will start step by step:

1. a new web site with VS 2005.

2. I added a sqldatasouce and connect with AdventureWorks sample database ,which comes with sql server 2005 developer edition, selected by drop-down list. [ server name:(local) ]

3. Test connection. It is OK.

4. Saved as 'AdventureWorksConnectionString'.

5. Some columns are selected in the 'product' table.

6. At the end while testing query with 'test query' button it gives:

"There was an error executing the query. Please check the syntax of the command and if present, the types and values of parameters and ensure the are correct.

Invalid object name 'Product'. "

7. However when I choice NorthWind database sample I installed externally, there is no problem.Moreover, when I choice AWBuildVersion table in the AdventureWorks, and it's columns, there is also no problem.

8. I compared NorthWind and AdventureWorks security properties in the SQL server managment studio, but can't find any differences.

9. I have been searching all the web since two days.

10. Thanks.

can you paste the SQL command that fails...

|||

There are two problems.

The AdventureWorks database uses a "Schema.Table" naming convention that the SQLDataSource Wizard doesn't pickup.

Table Names are Case-Sensitive in the AdventureWorks database.

Workaround:

Instead of "Specify columns from table or view", select "Specify a custom SQL statement..."

On the next screen, use the "Query Builder" to generate the SQL statement. It will pickup the schema name.

PS: After I post this message, I'm going to move this thread to the SQL database forum. I think it's more appropriate there.

|||

Yeah, it works... Thank you very much... But,I couldn't figure out why this happened to me, because I have started to read"Addison Wesley ASP NET .2.0 Illustrated". In that book, it is theprocedure how to implement AdventureWorks database in Chapter 2, and does normention any info of "Schema.Table" and SQLDataSouce relation. Ihave tried some method to accomplish that with "Specify columns from tableor view" method:

Source Code of"Specify columns from table or view" method which gives error:

<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString%>"
ProviderName="<%$ConnectionStrings:AdventureWorksConnectionString.ProviderName %>"
SelectCommand="SELECT [ProductID], [Name], [ProductNumber], [ListPrice],[Color], [SafetyStockLevel], [StandardCost], [Size] FROM[Product]">
</asp:SqlDataSource>

Source Code of "Specify a custom SQL statement..."and applying "Query Builder" method which gives NO error:

<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString%>"
SelectCommand="SELECT ProductID, Name, ProductNumber, ListPrice, Color,SafetyStockLevel, StandardCost, Size FROMProduction.Product">
</asp:SqlDataSource>

What I could try as a beginner to get a validoutput is changing[Product] as Production.Product.Of course, it gives the same 'invalid object name' error.

As I told you before, AWBuildVersion tablegives no error with "Specify columns from table or view" method:

<asp:SqlDataSource ID="SqlDataSource3" runat="server"ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString%>"
ProviderName="<%$ ConnectionStrings:AdventureWorksConnectionString.ProviderName%>"
SelectCommand="SELECT [SystemInformationID], [Database Version] ASDatabase_Version, [VersionDate], [ModifiedDate] FROM [AWBuildVersion]">
</asp:SqlDataSource>

I have understood from your note theonly way to implement "Schema.Table" s is use the "QueryBuilder", right?

And for your consideration here is Stack Trace:

------

SqlException (0x80131904): Invalid object name 'Product'.]
  System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177
  System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
  System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
  System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2406
  System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
  System.Data.SqlClient.SqlDataReader.get_MetaData() +62
  System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +294
  System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1038
  System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
  System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +20
  System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +107
  System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +10
  System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +7
  System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
  System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140
  System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
  System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1657
  System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +13
  System.Web.UI.WebControls.DataBoundControl.PerformSelect() +140
  System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68
  System.Web.UI.WebControls.GridView.DataBind() +5
  System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61
  System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +67
  System.Web.UI.Control.EnsureChildControls() +97
  System.Web.UI.Control.PreRenderRecursiveInternal() +50
  System.Web.UI.Control.PreRenderRecursiveInternal() +171
  System.Web.UI.Control.PreRenderRecursiveInternal() +171
  System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5731

------

 Again, Thank you very much to help and save my time.

sql

AdventureWorks vs AdventureWorksDW

I am new to SQL Server.
There are 2 databases for AdventureWorks sample application, AdventureWorks and AdventureWorksDW.
Is the AdventureWorksDW database generated from AdventureWorks or it is design manually?
Can anyone explain to me how to produce the AdventureWorksDW database? Thanks.

You can download both databases from this link:

http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

In this forum, we make use of the Adventure Works OLAP database built off the AdventureWorksDW relational database. When you download the samples from the link above, you will be given a Visual Studio solution that will build the OLAP database for you. Read the README files that come with the download for details.

Good luck,
Bryan

Adventureworks version 631 error

I installed Sql Server 2005 Express Edition on an XP system thismorning and tried to attach Adventureworks, but got an error messagestating:

The database 'Adventureworks' cannot be opend because itis version 631. This server supports version 612 andearlier. A downgrade path is not supported.

Since both SqlServer 2005 Express Edition, Management Studio, and the Adventureworksdatabase have all be downloaded within the past three days, I amastonished that there is a compatibility issue. I have runAdventureworks on other systems without problems. Any suggestionson how to proceed?

Hi Moonshadow,

Where did you download that "Adventureworks" ?

Please make sure you are downloading the correct AdventureWorks database for SQL Server 2005. The CodePlex website contains two versions of the database, one for SQL Server 2008 and one for SQL Server 2005. If you are viewing the AdventureWorks database download release for SQL Server 2008 you must click "SQL Server 2005 SP2a" in the Releases box on the right hand side. You can also use the following link to download the SQL Server 2005 version directly at:http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004.

Detailed information, see:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1966875&SiteID=1

thanks

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

|||

Hi B.C. When I originally searched for the DB I was redirected tothe Codeplex site where the current default download version is for SqlServer 2008, hence version incompatibility. I tracked down andinstalled the 2005 version at Codeplex and all is well.

AdventureWorks Samples Not working

I've installed and deployed the AdventureWorks Sample reports to Report
Manager in RS 2005. When I click on any of the reports, the report just
spins away and never returns the report information. I eventually (10
minutes later) just stop the browser. I made sure I have permissions to all
the data sources as well. Has anyone else had this problem or know where I
can look to see where the report(s) are hanging?
System Information:
Windows XP Pro
IIS 5.x
SQL Server 2005 Std.
VS 2005 Pro
Reporting Services 2005Verify that the dataset(s) in a report are actually working.
"Michael" wrote:
> I've installed and deployed the AdventureWorks Sample reports to Report
> Manager in RS 2005. When I click on any of the reports, the report just
> spins away and never returns the report information. I eventually (10
> minutes later) just stop the browser. I made sure I have permissions to all
> the data sources as well. Has anyone else had this problem or know where I
> can look to see where the report(s) are hanging?
> System Information:
> Windows XP Pro
> IIS 5.x
> SQL Server 2005 Std.
> VS 2005 Pro
> Reporting Services 2005
>|||I'm pretty sure the dataset is working since when I preview in VS2005 I can
get the sample to work with no problem. Strangely also, when I use VS2005 to
deploy and open the Samples folder, I can click on a sample report and it
works as expected. Only when I go to http://localhost/reports/home.aspx ->
AdventureWorks Samples -> Sample report does it just spin away.
"Bing Bing Yu" wrote:
> Verify that the dataset(s) in a report are actually working.
>
> "Michael" wrote:
> > I've installed and deployed the AdventureWorks Sample reports to Report
> > Manager in RS 2005. When I click on any of the reports, the report just
> > spins away and never returns the report information. I eventually (10
> > minutes later) just stop the browser. I made sure I have permissions to all
> > the data sources as well. Has anyone else had this problem or know where I
> > can look to see where the report(s) are hanging?
> >
> > System Information:
> > Windows XP Pro
> > IIS 5.x
> > SQL Server 2005 Std.
> > VS 2005 Pro
> > Reporting Services 2005
> >
> >

AdventureWorks Sample Reports

Hi folks,

Newbie question. Anyone knows how to create dropdown selection boxes in a report just like in the Employee Sales Summary report? Cheers!

Moving thread to the Reporting Services Group|||

If you install the sample reports, you can just open them in report designer and investigate the Report - Report Parameter settings in the VS menu. The Employee Sales Summary report uses a query to determine the valid values list of the parameter - which results in the drop down being shown.

Alternatively, this MSDN topic should get you started: http://msdn2.microsoft.com/en-us/library/ms155917.aspx

-- Robert

Adventureworks sample reports

Newbie Q. Can I view Adventureworks sample reports with SSX? Storefront? Thanks.

Sorry - what is SSX?

Regarding Storefront - do you mean these folks: http://www.storefront.com/?

What is it you're trying to do? Do you mean can you deliver reports to be printed using a standard online printing solution? If so then yes it is possible. No we can't do it out of the box, but you can write a delivery extension to send report output to the online service provider if they expose an API for submitting requests programmatically. You can learn about delivery extensions here: http://msdn2.microsoft.com/library/ms154672.aspx

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Lukasz,

I have seen other posts refering to SQL Server Express using the acronym SSX.

I have been trying to run Adventureworks samples/reports and keep getting error messages that I don't have the application/version to load/run files with the extension .csproj or .rptproj. I have downloaded/installed SSX, Adventureworks samples, VBX, CSX, Visual Web Developer, SSNSX and the .Net Framework 2.0 SDK.

Storefront is a sample project included in the Adventureworks sample package. It is intended to illustrate a sample web page for the fictitous Adventureworks company.

I wish to be able to view sample reports included in the sample package.

I have been successful at connecting to the database. I can view database stucture/tables/links. I have not been able to view a report, form or any "front-end" views.

Being new to these programs and this level of programing, I don't know if I the programs listed above lack this functionality or if I haven't gone thru the correct procedures to run/view these samples.

Any help would be greatly appreciated.

Thanks in advance,

Randy

|||

Wow - was I ever off the mark .

Ok - here's what you're looking for. I don't know about csproj - I don't think the web developer allows you to compile C# projects. You might need to invest in a full edition of Visual Studio 2005 C# to be able to compile these.

In order to handle rptproj you'll need to download the evaluation edition of reporting services (http://www.microsoft.com/sql/downloads/trial-software.mspx) and install the report designer component.

SQL Server Express (also SSX/SSE, adnausium) doesn't come with Reporting Services yet - we're working on a SKU that provides this functionality as we speak. So you'll need the evaluation edition of reporting services.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

sql

AdventureWorks Sample Database

Hi guys, I was trying to download the latest version of AdventureWorks database from codeplex website and I got the following error massage during the installation.

The database 'AdventureWorks' cannot be opened because it is version 631. This server supports version 611 and earlier. A downgrade path is not supported.Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted. (.Net SqlClient Data Provider)

Does anybody has idea on this

Thx

Never mind. I got the answer from another post. Here is the link: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2183902&SiteID=1&mode=1

peace

AdventureWorks Report Samples Problem

Hi,
I am trying to deploy the AdventureWorks OLAP cube in C:\Program
Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services
Project but unfortunately the folder "AdventureWorks Analysis Services
Project" does not exist nor does the file Adventure Works DW.dwproj. I have
installed the samples using the Microsoft SQL Server 2005 Samples installer.
Where can I find this directory and project?
Kind regards,
TrevorHi Trevor,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to launch
AdventureWorks Analysis Services Project but cannot find it in the
identified folder. If I have misunderstood your concern, please feel free
to point it out.
Based on my knowledge, please perform the steps below to make sure you have
installed AdventureWorksDW sample datawarehouse and AdventureWorks Sample
OLAP.
- Open Add or remove Programs in Control Panel
- Select Microsoft SQL Server 2005, click Change
- Select Workstation Components under SQL Server 2005 common components,
click Next
- After System Configuration Check, click Next.
- Click Change Installed Components
- Expand Documentation, Samples and Sample Databases, Expand Sample
Databases, Expand AdventureWorksDW Sample Data Warehouse. Make sure
everything was selected and then click Next
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

AdventureWorks Owner

Hi,

I've installed AdventureWorks, attached it to SQL Express and wanted to see its Database Diagrams. When I click on the Database Diagrams, I get this error message:

TITLE: Microsoft SQL Server Management Studio Express

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

--

I really see that in properties for this database there's no owner, while if I do

sp_helpdb 'AdventureWorks'

It shows that the owner is 'IDB-SERV\Michael' , which is my user.

I further tried to set this user as an owner in AdventureWorks properties-files window (where it showed no owner), but it says :

TITLE: Microsoft SQL Server Management Studio Express

Set owner failed for Database 'AdventureWorks'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Set+owner+Database&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred in SMO. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

The login 'IDB-SERV\Michael' does not exist on this server.

Are all these bugs, or did I miss something?

Please, help

Thanx a lot !!!!

Moisha, here are a few threads hitting the same error.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112297&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=228352&SiteID=1

They both seem to be solving the problem using sp_dbcmptlevel.

Take a look at the threads and see if it applies to your environment.

Thanks,
Sam Lester (MSFT)
|||

Moisha,

The quick solution for me - which I distilled from the links posted here - was to execute the sp_dbcmptlevel procedure. I did not need to do anything else.

EXEC sp_dbcmptlevel 'nameOfDatabase', '90';

Sunshine is my life. Clean air is my right.

AdventureWorks OLAP Cubes samples missing

Hi there,

I am setting up a demo with Scorecard Manager 2005. After days of struggle, the installation seems to be fine, I can publish all the two dimensional scorecards. But the sample AdventureWorks OLAP cubes do not seem to be accessible or they don't exist - to be more exact, Scorecard Manager is able to log on to the SQL Server database called AdventureWorksDW, but cannot find any cubes.

I downloaded the latest SQL Server 2005 samples here and followed all the instructions to the letter. I did not get any errors while deoplying the AdventureWorks solution in the Business Intelligence Development Studio. One concern related to the samples is that the AdventureWorksAS sample is not available. I thought that the DW database is used there, am I wrong?

I think that I'm missing some key element in deploying the sample OLAP cubes. Is there a way to check that they are made correctly? What application do I best use to peform this check?

I am running Server 2003, SP1, SQL Server 2005 SP1 with Analysis/Reporting/Integration services installed, SharePoint Server 2003 SP2 (all applications are running fine so far on it).

Thank you very much, Radu

PS: I just discovered and error posted by the Event Viewer. It seems that the system admin does not have authority to view ADOMD sources. How can I debug this error? This is a demo setup on a virtual PC so I gave all authorities to Admin from the initial setup and the AdventureWorksDW database exists (the Scorecard Manager app. does find the database, but cannot see any cubes and this is when the error shows up in the Event Viewer).

Here are the error details:

Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Either the user, NT AUTHORITY\SYSTEM, does not have access to the Adventure Works DW database, or the database does not exist.
at Microsoft.AnalysisServices.AdomdClient.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection+IXmlaClientProviderEx.Discover(String requestType, IDictionary restrictions, Boolean throwOnErrors, Boolean sendNamespaceCompatibility)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetProperty(String propName)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.SetProperty(String propertyName, String propValue)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ChangeDatabase(String database)
at Microsoft.PerformanceManagement.Scorecards.Server.OlapUtils.GetCubeNames(String databaseName)
at Microsoft.PerformanceManagement.Scorecards.Server.PmServer.GetCubeNames(DataSource dataSource)

I figured it out: there seems to be a mistake in the Scorecard Manager 2005 OLAP cubes samples' settings (the Adventure Works DW samples available here). If you open a scorecard's Data Source (in the Scorecard Builder), you need to erase any Database Roles. The samples come with 'All Users' set, but trying 'Reader' only for example, which is what is needed based on installation instructions, does not work.

Thanks to Ian Tien's site; I downloaded his Adventure Works DW samples, which are based on the 10 Analysis Services' tutorial cubes, and looked at his scorecard's configuration.

I guess this message is more for a Scorecard Manager 2005 board, but I couldn't find one on this MSDN forum site. But Analysis Services are inseparable from Scorecard, so I hope it helps someone.

adventureworks installation

I downloaded and installed the .msi from codeplex. After installation I open SQL Server Management Studio but don't see AdventureWorksDB in the list of databases. How is this possible? Should I not be seeing this new database? Do I have to do something else?

From within the sql server studio, right click on the databases entry. The task you are looking for is "attach database".

Grab the .mdf file for the adventure works db. ( It may try to grab a .log file that is in the wrong directory, if so, delete the reference to it on the screen. It will create another log file in the same directory as the .mdf file.)

sql

AdventureWorks Install

I am using sqlserver 2005 express edition and have downloaded the
AdventureWorksDB.msi. I have installed it and it created 3 files:
AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
no tables appear under system tables. Is there something I am missing here?
No, it's normal in SQL Server 2005. System tables are hidden and you can't
see or modify them directly.
Regards
Pawel Potasinski
Uytkownik "Richard" <rich_a_wms@.yahoo.com> napisa w wiadomoci
news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>I am using sqlserver 2005 express edition and have downloaded the
> AdventureWorksDB.msi. I have installed it and it created 3 files:
> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
> AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
> no tables appear under system tables. Is there something I am missing
> here?
>
>
|||I have another DB (storeDB) on the server and I am able to see all the
tables under Databases > System Databases > storeDB > Tables > System
Tables, but I am unable to see the System Tables under AdventureWorks. I am
going through the tutorial and it's asking me to create the following query:
SELECT * FROM Production.Product;
GO
and I receive this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Production.Product'.
I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
2005.
Any help would be appreciated.
Regards, Richard
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...

> No, it's normal in SQL Server 2005. System tables are hidden and you can't
> see or modify them directly.
> --
> Regards
> Pawel Potasinski
>
> Uytkownik "Richard" <rich_a_wms@.yahoo.com> napisa w wiadomoci
> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>
|||On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
[vbcol=seagreen]
> I have another DB (storeDB) on the server and I am able to see all the
> tables under Databases > System Databases > storeDB > Tables > System
> Tables, but I am unable to see the System Tables under AdventureWorks. I am
> going through the tutorial and it's asking me to create the following query:
> SELECT * FROM Production.Product;
> GO
> and I receive this error message:
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Production.Product'.
> I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
> 2005.
> Any help would be appreciated.
> Regards, Richard
> "Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
> news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
Looks like a permissions issue. If you are just testing, make your account
you log in as a Server Role of Sysadmin (God-level rights).
Chuck Lathrope
www.sqlwebpedia.com
|||Thanks, I changed permissions, but I still can't see the tables. The strange
thing is that there are 3 files in the Data folder under the MSSQL folder
for AdventureWorks: AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~
164MB) and AdventureWorks_log.idf (~1MB). The other DB's on the server only
have 2 files (.mdf and .idf) for each database. There are no _Data.mdf
files.
Regards, Richard
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:rugaxfto4xox$.1xm17ldm6wy7j$.dlg@.40tude.net.. .
> On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
>
> Looks like a permissions issue. If you are just testing, make your account
> you log in as a Server Role of Sysadmin (God-level rights).
> Chuck Lathrope
> www.sqlwebpedia.com

AdventureWorks Install

I am using sqlserver 2005 express edition and have downloaded the
AdventureWorksDB.msi. I have installed it and it created 3 files:
AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
no tables appear under system tables. Is there something I am missing here?No, it's normal in SQL Server 2005. System tables are hidden and you can't
see or modify them directly.
--
Regards
Pawel Potasinski
U¿ytkownik "Richard" <rich_a_wms@.yahoo.com> napisa³ w wiadomo¶ci
news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>I am using sqlserver 2005 express edition and have downloaded the
> AdventureWorksDB.msi. I have installed it and it created 3 files:
> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
> AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
> no tables appear under system tables. Is there something I am missing
> here?
>
>|||I have another DB (storeDB) on the server and I am able to see all the
tables under Databases > System Databases > storeDB > Tables > System
Tables, but I am unable to see the System Tables under AdventureWorks. I am
going through the tutorial and it's asking me to create the following query:
SELECT * FROM Production.Product;
GO
and I receive this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Production.Product'.
I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
2005.
Any help would be appreciated.
Regards, Richard
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
> No, it's normal in SQL Server 2005. System tables are hidden and you can't
> see or modify them directly.
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik "Richard" <rich_a_wms@.yahoo.com> napisa³ w wiadomo¶ci
> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>>I am using sqlserver 2005 express edition and have downloaded the
>> AdventureWorksDB.msi. I have installed it and it created 3 files:
>> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
>> AdventureWorks_log.idf (~1MB). When I added the database to the
>> sqlserver,
>> no tables appear under system tables. Is there something I am missing
>> here?
>>
>|||On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
> I have another DB (storeDB) on the server and I am able to see all the
> tables under Databases > System Databases > storeDB > Tables > System
> Tables, but I am unable to see the System Tables under AdventureWorks. I am
> going through the tutorial and it's asking me to create the following query:
> SELECT * FROM Production.Product;
> GO
> and I receive this error message:
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Production.Product'.
> I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
> 2005.
> Any help would be appreciated.
> Regards, Richard
> "Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
> news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
>> No, it's normal in SQL Server 2005. System tables are hidden and you can't
>> see or modify them directly.
>> --
>> Regards
>> Pawel Potasinski
>>
>> U¿ytkownik "Richard" <rich_a_wms@.yahoo.com> napisa³ w wiadomo¶ci
>> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>>I am using sqlserver 2005 express edition and have downloaded the
>> AdventureWorksDB.msi. I have installed it and it created 3 files:
>> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
>> AdventureWorks_log.idf (~1MB). When I added the database to the
>> sqlserver,
>> no tables appear under system tables. Is there something I am missing
>> here?
>>
>>
Looks like a permissions issue. If you are just testing, make your account
you log in as a Server Role of Sysadmin (God-level rights).
Chuck Lathrope
www.sqlwebpedia.com|||Thanks, I changed permissions, but I still can't see the tables. The strange
thing is that there are 3 files in the Data folder under the MSSQL folder
for AdventureWorks: AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~
164MB) and AdventureWorks_log.idf (~1MB). The other DB's on the server only
have 2 files (.mdf and .idf) for each database. There are no _Data.mdf
files.
Regards, Richard
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:rugaxfto4xox$.1xm17ldm6wy7j$.dlg@.40tude.net...
> On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
>> I have another DB (storeDB) on the server and I am able to see all the
>> tables under Databases > System Databases > storeDB > Tables > System
>> Tables, but I am unable to see the System Tables under AdventureWorks. I
>> am
>> going through the tutorial and it's asking me to create the following
>> query:
>> SELECT * FROM Production.Product;
>> GO
>> and I receive this error message:
>> Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'Production.Product'.
>> I have a connection the AdventureWorks DB. I'm obviously new to SQL
>> Server
>> 2005.
>> Any help would be appreciated.
>> Regards, Richard
>> "Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
>> news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
>> No, it's normal in SQL Server 2005. System tables are hidden and you
>> can't
>> see or modify them directly.
>> --
>> Regards
>> Pawel Potasinski
>>
>> U¿ytkownik "Richard" <rich_a_wms@.yahoo.com> napisa³ w wiadomo¶ci
>> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>>I am using sqlserver 2005 express edition and have downloaded the
>> AdventureWorksDB.msi. I have installed it and it created 3 files:
>> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
>> AdventureWorks_log.idf (~1MB). When I added the database to the
>> sqlserver,
>> no tables appear under system tables. Is there something I am missing
>> here?
>>
>>
> Looks like a permissions issue. If you are just testing, make your account
> you log in as a Server Role of Sysadmin (God-level rights).
> Chuck Lathrope
> www.sqlwebpedia.com

AdventureWorks Install

I am using sqlserver 2005 express edition and have downloaded the
AdventureWorksDB.msi. I have installed it and it created 3 files:
AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
no tables appear under system tables. Is there something I am missing here?No, it's normal in SQL Server 2005. System tables are hidden and you can't
see or modify them directly.
Regards
Pawel Potasinski
Uytkownik "Richard" <rich_a_wms@.yahoo.com> napisa w wiadomoci
news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>I am using sqlserver 2005 express edition and have downloaded the
> AdventureWorksDB.msi. I have installed it and it created 3 files:
> AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~ 164MB) and
> AdventureWorks_log.idf (~1MB). When I added the database to the sqlserver,
> no tables appear under system tables. Is there something I am missing
> here?
>
>|||I have another DB (storeDB) on the server and I am able to see all the
tables under Databases > System Databases > storeDB > Tables > System
Tables, but I am unable to see the System Tables under AdventureWorks. I am
going through the tutorial and it's asking me to create the following query:
SELECT * FROM Production.Product;
GO
and I receive this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Production.Product'.
I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
2005.
Any help would be appreciated.
Regards, Richard
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...

> No, it's normal in SQL Server 2005. System tables are hidden and you can't
> see or modify them directly.
> --
> Regards
> Pawel Potasinski
>
> Uytkownik "Richard" <rich_a_wms@.yahoo.com> napisa w wiadomoci
> news:u4xAciayHHA.748@.TK2MSFTNGP04.phx.gbl...
>|||On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
[vbcol=seagreen]
> I have another DB (storeDB) on the server and I am able to see all the
> tables under Databases > System Databases > storeDB > Tables > System
> Tables, but I am unable to see the System Tables under AdventureWorks. I a
m
> going through the tutorial and it's asking me to create the following quer
y:
> SELECT * FROM Production.Product;
> GO
> and I receive this error message:
> Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Production.Product'.
> I have a connection the AdventureWorks DB. I'm obviously new to SQL Server
> 2005.
> Any help would be appreciated.
> Regards, Richard
> "Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
> news:OLCmDdcyHHA.1776@.TK2MSFTNGP03.phx.gbl...
>
Looks like a permissions issue. If you are just testing, make your account
you log in as a Server Role of Sysadmin (God-level rights).
Chuck Lathrope
www.sqlwebpedia.com|||Thanks, I changed permissions, but I still can't see the tables. The strange
thing is that there are 3 files in the Data folder under the MSSQL folder
for AdventureWorks: AdventureWorks.mdf (~ 2MB), AdventureWorks_Data.mdf (~
164MB) and AdventureWorks_log.idf (~1MB). The other DB's on the server only
have 2 files (.mdf and .idf) for each database. There are no _Data.mdf
files.
Regards, Richard
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:rugaxfto4xox$.1xm17ldm6wy7j$.dlg@.40tude.net...
> On Thu, 19 Jul 2007 12:53:50 -0700, Richard wrote:
>
> Looks like a permissions issue. If you are just testing, make your account
> you log in as a Server Role of Sysadmin (God-level rights).
> Chuck Lathrope
> www.sqlwebpedia.com

AdventureWorks inserting records to JobCandidates example

Hi
re: AdventureWorks example
Are there source code to how the
HumanResources.JobCandidate
table was created?
(I would like to look at the sample code for INSERTing)
thanks
joyceWhat exactly do you want to know about?
Thanks
Michael
PS: I don't think we have sample code for specifically this table...
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169588888.525240.259000@.j27g2000cwj.googlegroups.com...
> Hi
> re: AdventureWorks example
> Are there source code to how the
> HumanResources.JobCandidate
> table was created?
> (I would like to look at the sample code for INSERTing)
> thanks
> joyce
>|||Actually, what I am really about is namespace...
So I thought I should take a look at how the JobCandidates table was
created in AdventureWorks.
I understand targetnamespace, and
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
I don't really understand default namespace such as
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume
"
What is the purpose of the def ns?
And also, what is the purpose of the schema collection? And how come a
schema collection can contain multiple schema.
And what is the advantage of typed vs untyped xml in sql server. Also,
does Sql server support any other types of schemas?
I'm still slowly going through the books online material, but it'd be
nice if all these questions are all answered at once.
Thank you, this is a great usenet group,
Joyce
On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> What exactly do you want to know about?
> Thanks
> Michael
> PS: I don't think we have sample code for specifically this table...
> "joyce chan" <joycesc...@.fastmail.fm> wrote in messagenews:1169588888.5252
40.259000@.j27g2000cwj.googlegroups.com...
>
>
>
>
>|||See inline below.
Best regards
Michael
"joyce chan" <joyceschan@.fastmail.fm> wrote in message
news:1169736748.697916.238950@.s48g2000cws.googlegroups.com...
> Actually, what I am really about is namespace...
> So I thought I should take a look at how the JobCandidates table was
> created in AdventureWorks.
> I understand targetnamespace, and
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> I don't really understand default namespace such as
> xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resu
me"
> What is the purpose of the def ns?
Default namespaces are just a syntactic convenience in that you do not have
to write a prefix (this is true for XML documents and XQuery, but not XPath
1.0 where no support for default namespace exists, you have to define a
prefix for the query).
Namespaces per se are just scoping mechanisms to scope a name to a space
that can have different semantics associated than the same name in a
different namespace... e.g., river:bank vs finance:bank.

> And also, what is the purpose of the schema collection? And how come a
> schema collection can contain multiple schema.
A schema collection is a meta data object in SQL Server that allows you to
combine multiple schemas in a logical unit to constrain and type an XML data
type instance with. It also introduces a way that allows you to have more
than one version of the same schema within your database. Note that a single
schema defines structural and some semantic constraints for names within a
namespace (including the non-namespace). However not every document having
data with namespaces needs to have a schema.

> And what is the advantage of typed vs untyped xml in sql server. Also,
> does Sql server support any other types of schemas?
Untyped XML gives you the advantage of complete flexibity of your structure
and no additional validation cost.
Typed XML gives you semantical assurances over the structure of the data,
provides error detection at compile time in your queries (using static
typing), and can be used for some storage and query optimizations.
SQL Server has only limited support for DTDs and other wise does not support
other types of schemas.

> I'm still slowly going through the books online material, but it'd be
> nice if all these questions are all answered at once.
> Thank you, this is a great usenet group,
> Joyce
> On Jan 24, 8:05 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>