SQL Adding a User account: adventure

Showing posts with label adventure. Show all posts

Thursday, March 22, 2012

AdventureWorks DB question

Hi, I have been looking at the Adventure works DB diagram and I am confused about the relationship that employees, customers and individuals has with contact i.e. a 1-1, 1-many or many-many relationship. Also why is it broken down like that.

Thanks,

Nadim.

Hi Nadim,

AdventureWorksDB is designed to show a complex data schema in a nomalized structure. AdventureWorks is probably a bit overcomplex if you're just learning about normalization, but once you understand the concepts, it becomes more clear.

Note: If you are new to normalization, you should search for a few topics about it to get the basics. You might also find the Northwind or Pubs sample databases to be a bit more understandable. You can download either from http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en. They are SQL 2000 samples, but they will still work in 2005.

On to the description...

If you look at the fields in each of the tables, you'll see that the Contact table contains the base information about a person, e.g. first name, last name, phone, etc. The Contact table is linked to both the Employee and Individual tables. Both Employees and Individuals are types of people that have special properties in addition to the base person information stored in the Contact table. So an Employee has a ManagerID and LoginID, but an Individual doesn't. Because of the relationship between Contact and Employee, there is no need to store the first name, last name, etc. in the Employee table, it is retrieved through a join.

If I wanted to get a list of Employee names, with Titles and Hire Dates, I'd need to write a query that uses both the Contact and Employee table, as follows:

SELECT C.FirstName, C.LastName, E.Title, E.HireDate
FROM Person.Contact C INNER JOIN HumanResources.Employee E
ON C.ContactID = E.ContactID

The relationship with Individual is similar, only it extends through Individual onto Customer. Each Customer has an Individual associated with it, and that Individual, is a person who has Contact information. I'll be honest, I don't really know why they split Customer and Individual, it could have something to do with the way they wanted to use the data being stored.

Hopefully that brings a little light to the subject.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

AdventureWorks Database not showing up management studio express

After installing the Adventure works database samples they do NOT show up in Management Studio Express?

I have done a clean install of SQL Server Express 2005 SP1 with Advanced services.

The database samples only install as physical files, they are not attached to the SQL Server process. If you want to use them, go in the SSMS and right click on the Database node, choose All Tasks and click Attach database.. Locate the MDF File and the LDF File and click Apply (or OK). SQL Server will attach the databases to the SQL Server process and you can access the data.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||I found this not quite accurat. The Attach is in the Object Explorer window under the sqlexpress datebase, right click Databases (nt Database) then click Attach. The window which opens should show the AdventureWorks database without having to browse. This required action is not mentioned in the tutorial.

AdventureWorks Database not showing up management studio express

After installing the Adventure works database samples they do NOT show up in Management Studio Express?

I have done a clean install of SQL Server Express 2005 SP1 with Advanced services.

HTH, Jens Suessmeyer.

Tuesday, March 20, 2012

Adventure Works?

I'm curious; what do people here think about AdventureWorks? Do you use it
often when you need to demonstrate something?
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.comPlease ignore; wrong newsgroup!
I'm probably getting old...
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uChZMmiFGHA.1180@.TK2MSFTNGP09.phx.gbl...
> I'm curious; what do people here think about AdventureWorks? Do you use it
> often when you need to demonstrate something?
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
>|||How about, for a nickname: Itzik "SQL Geezer" Ben-Gan. I think it has a
nice ring to it.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:u4l1qoiFGHA.1088@.tk2msftngp13.phx.gbl...
> Please ignore; wrong newsgroup!
> I'm probably getting old...
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:uChZMmiFGHA.1180@.TK2MSFTNGP09.phx.gbl...
>|||There is no "probably" about it Itzik. It's inevitable<g>.
Andrew J. Kelly SQL MVP
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:u4l1qoiFGHA.1088@.tk2msftngp13.phx.gbl...
> Please ignore; wrong newsgroup!
> I'm probably getting old...
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:uChZMmiFGHA.1180@.TK2MSFTNGP09.phx.gbl...
>|||Well, having Aslan nicknamed "the top geezer", I guess I'm in a good group.
I guess this makes you Mr. Beaver, then, doesn't it? ;)
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OCIhyCjFGHA.516@.TK2MSFTNGP15.phx.gbl...
> How about, for a nickname: Itzik "SQL Geezer" Ben-Gan. I think it has a
> nice ring to it.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:u4l1qoiFGHA.1088@.tk2msftngp13.phx.gbl...
>|||Hmmm, I have only a vague idea of what you're talking about -- I read that
book years ago and haven't made it out to see the movie yet -- so I'll get
back to you on this soon :)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:ObDagajFGHA.532@.TK2MSFTNGP15.phx.gbl...
> Well, having Aslan nicknamed "the top geezer", I guess I'm in a good
> group. I guess this makes you Mr. Beaver, then, doesn't it? ;)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:OCIhyCjFGHA.516@.TK2MSFTNGP15.phx.gbl...
>|||While I suspect, based on the responses from Adam & Andrew, this was
supposed to be posted to the MVP newsgroups, I'll answer anyway. I find
AdventureWorks (and pubs before it) pretty useless. I only ever look at
them (if I haven't already dropped them) if some Microsoft whitepaper or
MS Press book I'm reading has some example that specifically refers to them.
Any T-SQL code example I give I write from scratch, complete with CREATE
statements & tidy up DROP statements at the end of the script (if I
create any permanent objects). Setting up the schema & data for the
demo helps to get your head fully around the question you're trying to
answer and only takes a few extra minutes. I guess if I was writing a
book (like just about everyone else in the MSSQL world it seems) then
some consistency with the schema & data used in the examples would be
good but 99% of the code examples I give are direct responses to one-off
ad-hoc questions.
BTW Itzik, don't worry about getting old, it happens to all of us.
Anyway, from where I sit you're still one of the sharpest tools in the
shed; I don't think premature senility is setting in just yet.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
PS. C.S. Lewis is underrated. He was the king of allegories - The
Chronicles are timeless (if you'll pardon the play on words).
Itzik Ben-Gan wrote:

>I'm curious; what do people here think about AdventureWorks? Do you use it
>often when you need to demonstrate something?
>
>

Adventure Works.sln DEPLOY ERROR first time

After opening the Adventure Works.sln.

I build.

I deploy.

I get the following error ...

Build started: Project: Adventure Works DW, Configuration: Development

Started Building Analysis Services project: Incremental ....

Build complete -- 0 errors, 0 warnings

Deploy started: Project: Adventure Works DW, Configuration: Development

Performing an incremental deployment of the 'Adventure Works DW' database to the 'localhost' server.

Generating deployment script...

Add Database Adventure Works DW

Process Database Adventure Works DW

Done

Sending deployment script to the server...

Error -1056308996 : The attribute Destination Currency does not have any members.

Error -1056308996 : The attribute Parent Organization Key0 does not have any members.

Deploy complete -- 2 errors, 0 warnings

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

What am I doing wrong?

What do I do next?

AIM

The only thing I could think of is that maybe you are deploying the enterprise sample verison against a standard server....|||

I am using SQL Server Enterprise 2005 180-Eval (and I am choosing the Enterprise folder and not the Standard folder).

My (MSSQLSERVER) Analysis Services is started. Hmmm mm. Maybe its the Data connection? Maybe its not finding my AdventureWorksDW local database? Maybe I have to define this and not using the default? Maybe its the order. I did build. Next, I did deploy. Hmmm mm, maybe I a missing something?

Any ideas?

AIMDBA

|||

When I choose "Process" I get the following result. ...

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Database 'Adventure Works DW' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:01
Processing Dimension 'Destination Currency' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
Processing Dimension Attribute 'Destination Currency' completed successfully. 1 rows have been read.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[DimDestinationCurrency].[CurrencyName] AS [DimDestinationCurrencyCurrencyName0_0]
FROM
(

SELECT CurrencyKey, CurrencyAlternateKey, CurrencyName
FROM DimCurrency
WHERE (CurrencyKey IN
(SELECT DISTINCT CurrencyKey
FROM FactCurrencyRate))
)
AS [DimDestinationCurrency]
Processing Dimension Attribute 'Destination Currency Code' completed successfully. 1 rows have been read.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[DimDestinationCurrency].[CurrencyKey] AS [DimDestinationCurrencyCurrencyKey0_0],[DimDestinationCurrency].[CurrencyAlternateKey] AS [DimDestinationCurrencyCurrencyAlternateKey0_1],[DimDestinationCurrency].[CurrencyName] AS [DimDestinationCurrencyCurrencyName0_2]
FROM
(

SELECT CurrencyKey, CurrencyAlternateKey, CurrencyName
FROM DimCurrency
WHERE (CurrencyKey IN
(SELECT DISTINCT CurrencyKey
FROM FactCurrencyRate))
)
AS [DimDestinationCurrency]
Processing Dimension 'Organization' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
Processing Dimension Attribute 'Currency Code' completed successfully. 1 rows have been read.
Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:-01
SQL queries 1
SELECT
DISTINCT
[dbo_DimOrganization].[CurrencyKey] AS [dbo_DimOrganizationCurrencyKey0_0],[dbo_DimOrganization].[CurrencyAlternateKey] AS [dbo_DimOrganizationCurrencyAlternateKey0_1]
FROM
(

SELECT o.OrganizationKey, o.ParentOrganizationKey, o.PercentageOfOwnership, o.OrganizationName, o.CurrencyKey, c.CurrencyAlternateKey
FROM DimOrganization AS o INNER JOIN
DimCurrency AS c ON o.CurrencyKey = c.CurrencyKey
)
AS [dbo_DimOrganization]
Processing Dimension Attribute 'Organization' completed successfully. 1 rows have been read.
Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_DimOrganization].[OrganizationKey] AS [dbo_DimOrganizationOrganizationKey0_0],[dbo_DimOrganization].[OrganizationName] AS [dbo_DimOrganizationOrganizationName0_1],[dbo_DimOrganization].[PercentageOfOwnership] AS [dbo_DimOrganizationPercentageOfOwnership0_2],[dbo_DimOrganization].[CurrencyKey] AS [dbo_DimOrganizationCurrencyKey0_3],[dbo_DimOrganization].[ParentOrganizationKey] AS [dbo_DimOrganizationParentOrganizationKey0_4]
FROM
(

Any ideas?

Thanks.

AIMDBA

Adventure Works DW.sln Starting to write data of the 'Internet ~1 ~MG' partition. Hangs Foreve

Hello,

I am trying to process the sample solution "Adventure Works DW.sln",

whenever I get to the point

...

Starting to write data of the 'Internet ~1 ~MG' partition.

...

This processing step NEVER finishes.

Does anyone have a 'way' to make this 'finish' processing?

If I Exit out of Visual Studio and try again, sometimes I will get the error.

Error 5 Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database. 0 0

Does anyone have any ideas?

Thanks,

Andre Mikulec

AIMDBA

Andre_Mikulec@.Hotmail.com

Also,

When I do individual object processing ...

Processing Dimension 'Clustered Customers.'

Processing Dimension 'Attribute Name'

Started reading data for the 'Attribute Name' attribute.

This <Started reading data for the 'Attribute Name' attribute.> hangs forever and never finishes.

Does anyone have any ideas?

Thanks.

AIMDBA

Andre_Mikulec@.Hotmail.com

|||

Moving to DM forum.

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

|||

I was just going through my solution explorer.

I have noticed thate the cube or mining structure 'Customer Clusters' (the one that hangs and does not finish) does IS NOT SEEN IN MY Solution Explorer.

How can this not be?

Does anyone have any idea on how I could fix.

This problem.

I have installed the SQL Server Developer's Edition using my company's MSDN subscription at work.

I installed and deployed the Analysis Services sample solution "Adventure Works DW" just fine without any problems.

This is weird.

Does anyone have any idea on how to fix this.

I have installed and re-installed the AdventureworksBI.msi on my non-company problem host.

The same problem occurs. My old settings in OLAP seem that they are being kept.

I know that the security in the OLAP directories is strange.

Does anyone have any ideas on how to proceed?

Note, I applied Visual Studio Service Pack 1.

Now, I get the same error, but at a different spot.

Processing Measure Group '~CaseDetail ~MG'.
Start time 5/26/2007 2:14:58 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/26/2007 2:14:58 AM
SQL queries 1 ( *** BEFORE VISUAL STUDIO SERVICE PACK 1 IT HUNG HERE ***)
SELECT DATAID ( [Adventure Works].[$Customer].[Customer] )
AS [tomer0_0], KEY ( [Adventure Works].[$Customer].[Number of Cars Owned],0 )
AS [tomer0_1], KEY ( [Adventure Works].[$Customer].[Number of Children At Home],0 )
AS [tomer0_2], KEY ( [Adventure Works].[$Customer].[Total Children],0 )
AS [tomer0_3]
FROM [Adventure Works].[$Customer]
GROUP BY [tomer0_0],[tomer0_1],[tomer0_2],[tomer0_3],[tomer0_0]
ORDER BY DATAID ( [Adventure Works].[$Customer].[Customer] )
ASC
Processing Dimension 'Subcategory Basket Analysis'.
Start time 5/26/2007 2:15:02 AM
Processing Dimension Attribute '(All)' completed successfully.
Start time: 5/26/2007 2:15:02 AM; End time: 5/26/2007 2:15:02 AM; Duration: 0:00:00
Processing Dimension Attribute 'Attribute Name'.
Start time 5/26/2007 2:15:02 AM ( *** AFTER VISUAL STUDIO SERVICE PACK 1 IT HANGS HERE ***)

Does anyone have any ideas on how to proceed?

Thank you very much.

AIM

Andre_Mikulec@.Hotmail.com

Adventure Works DW.sln error

When I am trying to process the dimension "Clustered Customers"

I get an error of the following words, ...

OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..

SELECT 1 AS [dbo_FactInternetSalesReason0_0],[SalesReasonKey] AS [dbo_FactInternetSalesReasonSalesReasonKey0_1]
FROM [dbo].[FactInternetSalesReason]
)
AS [dbo_FactInternetSalesReason]
Processing Mining Structure 'Customer Mining'.
Start time 5/6/2007 1:45:40 AM
Processing Cube 'Customer Clusters ~MC'.
Start time 5/6/2007 1:45:40 AM
Processing Measure Group '~CaseDetail ~MG'.
Start time 5/6/2007 1:45:41 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/6/2007 1:45:41 AM
Processing Measure Group 'Internet ~1 ~MG'.
Start time 5/6/2007 1:45:41 AM
Processing Partition 'Internet ~1 ~MG'.
Start time 5/6/2007 1:45:41 AM
SQL queries 1
SELECT DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
AS [Sales0_0], AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )
AS [Sales0_1], DATAID ( [Adventure Works].[Internet Sales].[$Product.Subcategory] )
AS [Sales0_2]
FROM [Adventure Works].[Internet Sales]
GROUP BY [Sales0_0],[Sales0_0],[Sales0_2]
ORDER BY DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..

Any ideas what could be causing this error occurred while reading from the file 'Compressed stream'.. problem?

Thanks.

AIMDBA

Andre_Mikulec

Moving to DM forum.

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

|||I wonder if you have a valid certificate for your SQL Server? See: http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx|||

Donald Farmer,

Thanks.

Certificate for SQL Server 2005
http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

Microsoft SQL Server 2005 can use Secure Sockets Layer (SSL) to encrypt data transmitted across a network between an instance of SQL Server and a client application. Certificate is used to encrypt the data transmission. Certificates are stored locally for the users on the computer

...
If the server can not find a valid certificate, a self-signed certificate will be issued by the server to encrypt data communication between the server and client. The login packet will always be encrypted. Whether the data communication is encrypted depends on the configuration on both server and client.

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).
...

recommendation by...
http://www.beyeblogs.com/donaldfarmer/

Donald Farmer, Microsoft

O.K. HERE IS WHAT I DID BEFORE I SAW THE DATA MINING PROCESSING ERROR ...

After, I did the SQL Server 2005 Developers Edition Install, I would follow with an SQL Server Express install.

From the end of the SQL Server 2005 Express Reporting Services install, I would get the following message. (I am not sure about the SQL Server 2005 Developer Edition Reporting Services install. I did not keep notes.)

"
A Secure Socket Layer (SSL) certificate is not installed on this computer, proceeding will result in a potentially unsecure deployment
"

According to the article reference, ...

"
If the server can not find a valid certificate, a self-signed certificate will be issued by the server to encrypt data communication between the server and client. The login packet will always be encrypted. Whether the data communication is encrypted depends on the configuration on both server and client.

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).
"

I am not fully sure, what the above means, "in order to enforce," and "should" are in the same sentence.

Nontheless, this is the 'best lead' on this mystery so far. I will see if SQL Server 2005 has some 'install logs' on both the 'computer that the 'Data Mining' processing does not work, and the computer that the 'Data Mining' processsing does work.

I will post back, with progress.

Thanks.

AIMDBA

|||

Note, I applied Visual Studio Service Pack 1.

Now, I get the same error, but at a different spot.

Note 2, about the possible 'no digital certificate problem,' I seem to have one and only one digital cerificate in my Certificates (Local Computer) \ Personal \ Certicates Store. This is untrusted. This has all the required extended attributes. This digital certificate shows up in the Digital Certificate for Protocols for <instance> Properties. However, this selection is CLEAR. This digital certificate is NOT SELECTED.

No Certificate thumbprints are in the registry.

In my Certificates (Local Computer) \Trusted Root Certificate Authority, I have 100 certificates or more.

I will look some more.

Does anyone, have any more input on these errors?

Thanks.

AIMDBA

|||

Now,

I have done the following.

I have removed the useless invalid 'Personal Certificate' from the personal area.

This may not have been the problem anyway.

I just want to be sure.

I rebooted the machine.

Next, I tried to 'fully' reprocess the Adventure Works DW analysis services database.

Now, my new error is the following.

Processing Partition 'Internet ~1 ~MG'.
Start time 5/26/2007 3:36:08 AM
SQL queries 1
SELECT DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
AS [Sales0_0], AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )
AS [Sales0_1], DATAID ( [Adventure Works].[Internet Sales].[$Product.Subcategory] )
AS [Sales0_2]
FROM [Adventure Works].[Internet Sales]
GROUP BY [Sales0_0],[Sales0_0],[Sales0_2]
ORDER BY DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..

Does anyone have any idea about this error?

Thanks.

AIMDBA

|||

Now,

I tried to process Adventure Works DW in Management Studio (instead of Business Intelligence Studio.

Now the process gets hung indefinitely at the following ...

Processing Mining Structure 'Customer Mining'.
Start time 5/26/2007 3:58:33 AM
Processing Cube 'Customer Clusters ~MC'.
Start time 5/26/2007 3:58:33 AM
Processing Measure Group 'Internet ~1 ~MG'.
Start time 5/26/2007 3:58:34 AM
Processing Partition 'Internet ~1 ~MG'.
Start time 5/26/2007 3:58:34 AM
Processing Measure Group '~CaseDetail ~MG'.
Start time 5/26/2007 3:58:34 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/26/2007 3:58:34 AM (HANGS HERE INDEFINITELY)

Any Ideas?

AIMDBA

|||

Next,

I went to Process in Management Studio, for performance reasons, I changed the processing from Parallel to Sequential-Many Transactions. (I wanted a slower and safer process.)

The error message really spilled its guts this time.

SEE BELOW, what does the file, line, and function, do? ... "

OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')"

What does this error mean?

Processing Cube 'Customer Clusters ~MC' failed.
Start time: 5/26/2007 4:16:25 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:02
Processing Measure Group '~CaseDetail ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
Processing Partition '~CaseDetail ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
SQL queries 1
SELECT DATAID ( [Adventure Works].[$Customer].[Customer] )
AS [tomer0_0], KEY ( [Adventure Works].[$Customer].[Number of Cars Owned],0 )
AS [tomer0_1], KEY ( [Adventure Works].[$Customer].[Number of Children At Home],0 )
AS [tomer0_2], KEY ( [Adventure Works].[$Customer].[Total Children],0 )
AS [tomer0_3]
FROM [Adventure Works].[$Customer]
GROUP BY [tomer0_0],[tomer0_1],[tomer0_2],[tomer0_3],[tomer0_0]
ORDER BY DATAID ( [Adventure Works].[$Customer].[Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')..
Processing Measure Group 'Internet ~1 ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
Processing Partition 'Internet ~1 ~MG' failed.
Start time: 5/26/2007 4:16:26 AM; End time: 5/26/2007 4:16:27 AM; Duration: 0:00:01
SQL queries 1
SELECT DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
AS [Sales0_0], AGGREGATE ( [Adventure Works].[Internet Sales].[Internet Sales Amount] )
AS [Sales0_1], DATAID ( [Adventure Works].[Internet Sales].[$Product.Subcategory] )
AS [Sales0_2]
FROM [Adventure Works].[Internet Sales]
GROUP BY [Sales0_0],[Sales0_0],[Sales0_2]
ORDER BY DATAID ( [Adventure Works].[Internet Sales].[$Customer.Customer] )
ASC
Error Messages 1
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..
Errors and Warnings from Response
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'..
Errors in the OLAP storage engine: An error occurred while processing the 'Internet ~1 ~MG' partition of the 'Internet ~1 ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')..
Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database.

Any ideas?

Thanks.

AIMDBA

|||

About the Digital Certificate possible problem,

I used the configuration manager to make the SQL Server service run under ./Administrator
I used the configuration manager to make the SQL Server service run under ./Administrator

I used SSL Diagnostics to create and install a 'correct' digital certificate according the recommended articles.

I used the configuration manager to install 'this' digital certificate for on SQL Server.

I set 'Force Encryption' on at both the Server and the Client.

I rebooted the host.

I logged on as Administrator.

When I process Adventure Works DW ... I still ended up getting the same error.

Error 1 Internal error: The operation terminated unsuccessfully. 0 0
Error 2 Internal error: The operation terminated unsuccessfully. 0 0
Error 3 OLE DB error: OLE DB or ODBC error: The following system error occurred: The token supplied to the function is invalid .; File system error: The following error occurred while reading from the file 'Compressed stream'.. 0 0
Error 4 Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database. 0 0
Error 5 Internal error: The operation terminated unsuccessfully. 0 0

When I try to View Designer of Customer Mining.dmm, the tab never loads, it just hangs forever.

I checked on the XML with XML Notepad 2007, the XML loads without error.

Is it possible, that I may need a better build of SQL Server 2005 Developer's Edition? I am using amazon.com: SQL Server Developer's Edition + SP2 + Business Intelligence Sample Databases (AdventureWorksBI) (SP2)

On another machine where Adventure Works DW will process ...I have the following: SQL Server 2005 Enterprise Components + MSDN: SQL Server Developer's Edition + SP2 + Business Intelligence Sample Databases (AdventureWorksBI) (SP2).

Any Ideas?

AIMDBA

|||

Thanks for the additional info.

I think we've reached the point where a support analyst would really need to drill deeper. Could you contact MS product support with this issue? They will talk you the process of providing a minidump and other information that would help to identify the issue here.

|||

We experienced similar problems:

"File sytem error: The following error occured while reading from the file 'Compressed stream'."

It was not reproduceable but once it happened the only solution was to reboot the client.

I suspect it was due to a firewall (Integrity Checker) installed on the clients. We switched to using msmdpump.dll to communicate with the server and it seems this solved the problem.

Hth,

Tom

|||

Tom VdP,

I do use a firewall. I will check out msmdpump.dll.

Thanks

AIMDBA

Adventure Works DW.sln error

When I am trying to process the dimension "Clustered Customers"

I get an error of the following words, ...

Any ideas what could be causing this error occurred while reading from the file 'Compressed stream'.. problem?

Thanks.

AIMDBA

Andre_Mikulec

Moving to DM forum.

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

|||I wonder if you have a valid certificate for your SQL Server? See: http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx|||

Donald Farmer,

Thanks.

Certificate for SQL Server 2005
http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).
...

recommendation by...
http://www.beyeblogs.com/donaldfarmer/

Donald Farmer, Microsoft

O.K. HERE IS WHAT I DID BEFORE I SAW THE DATA MINING PROCESSING ERROR ...

After, I did the SQL Server 2005 Developers Edition Install, I would follow with an SQL Server Express install.

"
A Secure Socket Layer (SSL) certificate is not installed on this computer, proceeding will result in a potentially unsecure deployment
"

According to the article reference, ...

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).
"

I am not fully sure, what the above means, "in order to enforce," and "should" are in the same sentence.

I will post back, with progress.

Thanks.

AIMDBA

|||

Note, I applied Visual Studio Service Pack 1.

Now, I get the same error, but at a different spot.

No Certificate thumbprints are in the registry.

In my Certificates (Local Computer) \Trusted Root Certificate Authority, I have 100 certificates or more.

I will look some more.

Does anyone, have any more input on these errors?

Thanks.

AIMDBA

|||

Now,

I have done the following.

I have removed the useless invalid 'Personal Certificate' from the personal area.

This may not have been the problem anyway.

I just want to be sure.

I rebooted the machine.

Next, I tried to 'fully' reprocess the Adventure Works DW analysis services database.

Now, my new error is the following.

Does anyone have any idea about this error?

Thanks.

AIMDBA

|||

Now,

I tried to process Adventure Works DW in Management Studio (instead of Business Intelligence Studio.

Now the process gets hung indefinitely at the following ...

Any Ideas?

AIMDBA

|||

Next,

I went to Process in Management Studio, for performance reasons, I changed the processing from Parallel to Sequential-Many Transactions. (I wanted a slower and safer process.)

The error message really spilled its guts this time.

SEE BELOW, what does the file, line, and function, do? ... "

OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')"

What does this error mean?

Any ideas?

Thanks.

AIMDBA

|||

About the Digital Certificate possible problem,

I used the configuration manager to make the SQL Server service run under ./Administrator
I used the configuration manager to make the SQL Server service run under ./Administrator

I used SSL Diagnostics to create and install a 'correct' digital certificate according the recommended articles.

I used the configuration manager to install 'this' digital certificate for on SQL Server.

I set 'Force Encryption' on at both the Server and the Client.

I rebooted the host.

I logged on as Administrator.

When I process Adventure Works DW ... I still ended up getting the same error.

When I try to View Designer of Customer Mining.dmm, the tab never loads, it just hangs forever.

I checked on the XML with XML Notepad 2007, the XML loads without error.

Any Ideas?

AIMDBA

|||

Thanks for the additional info.

|||

We experienced similar problems:

"File sytem error: The following error occured while reading from the file 'Compressed stream'."

It was not reproduceable but once it happened the only solution was to reboot the client.

I suspect it was due to a firewall (Integrity Checker) installed on the clients. We switched to using msmdpump.dll to communicate with the server and it seems this solved the problem.

Hth,

Tom

|||

Tom VdP,

I do use a firewall. I will check out msmdpump.dll.

Thanks

AIMDBA

Adventure Works DW.sln error

When I am trying to process the dimension "Clustered Customers"

I get an error of the following words, ...

Any ideas what could be causing this error occurred while reading from the file 'Compressed stream'.. problem?

Thanks.

AIMDBA

Andre_Mikulec

Moving to DM forum.

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

|||I wonder if you have a valid certificate for your SQL Server? See: http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx|||

Donald Farmer,

Thanks.

Certificate for SQL Server 2005
http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).
...

recommendation by...
http://www.beyeblogs.com/donaldfarmer/

Donald Farmer, Microsoft

O.K. HERE IS WHAT I DID BEFORE I SAW THE DATA MINING PROCESSING ERROR ...

After, I did the SQL Server 2005 Developers Edition Install, I would follow with an SQL Server Express install.

"
A Secure Socket Layer (SSL) certificate is not installed on this computer, proceeding will result in a potentially unsecure deployment
"

According to the article reference, ...

In order for the client to enforce encryption, the certificate used by the server should also signed by a trusted certificate authority (from the client’s point of view).
"

I am not fully sure, what the above means, "in order to enforce," and "should" are in the same sentence.

I will post back, with progress.

Thanks.

AIMDBA

|||

Note, I applied Visual Studio Service Pack 1.

Now, I get the same error, but at a different spot.

No Certificate thumbprints are in the registry.

In my Certificates (Local Computer) \Trusted Root Certificate Authority, I have 100 certificates or more.

I will look some more.

Does anyone, have any more input on these errors?

Thanks.

AIMDBA

|||

Now,

I have done the following.

I have removed the useless invalid 'Personal Certificate' from the personal area.

This may not have been the problem anyway.

I just want to be sure.

I rebooted the machine.

Next, I tried to 'fully' reprocess the Adventure Works DW analysis services database.

Now, my new error is the following.

Does anyone have any idea about this error?

Thanks.

AIMDBA

|||

Now,

I tried to process Adventure Works DW in Management Studio (instead of Business Intelligence Studio.

Now the process gets hung indefinitely at the following ...

Any Ideas?

AIMDBA

|||

Next,

I went to Process in Management Studio, for performance reasons, I changed the processing from Parallel to Sequential-Many Transactions. (I wanted a slower and safer process.)

The error message really spilled its guts this time.

SEE BELOW, what does the file, line, and function, do? ... "

OLE DB error: OLE DB or ODBC error: Internal error: An unexpected error occurred (file 'pcxmlacommon.cpp', line 43, function 'PCFault::RaiseError')"

What does this error mean?

Any ideas?

Thanks.

AIMDBA

|||

About the Digital Certificate possible problem,

I used the configuration manager to make the SQL Server service run under ./Administrator
I used the configuration manager to make the SQL Server service run under ./Administrator

I used SSL Diagnostics to create and install a 'correct' digital certificate according the recommended articles.

I used the configuration manager to install 'this' digital certificate for on SQL Server.

I set 'Force Encryption' on at both the Server and the Client.

I rebooted the host.

I logged on as Administrator.

When I process Adventure Works DW ... I still ended up getting the same error.

When I try to View Designer of Customer Mining.dmm, the tab never loads, it just hangs forever.

I checked on the XML with XML Notepad 2007, the XML loads without error.

Any Ideas?

AIMDBA

|||

Thanks for the additional info.

|||

We experienced similar problems:

"File sytem error: The following error occured while reading from the file 'Compressed stream'."

It was not reproduceable but once it happened the only solution was to reboot the client.

I suspect it was due to a firewall (Integrity Checker) installed on the clients. We switched to using msmdpump.dll to communicate with the server and it seems this solved the problem.

Hth,

Tom

|||

Tom VdP,

I do use a firewall. I will check out msmdpump.dll.

Thanks

AIMDBA

Adventure Works DW databases INSTALLATION

In SQL Server April 2006 Books Online (BOL)

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/instsql9/html/8c4c2af8-f2ed-4559-b5d2-984a9f7ce5ca.htm

... near the bottom, the following is said ...

To deploy the Adventure Works DW analysis services project

1. Make sure that the AdventureWorksDW and Adventure Works DW databases have been installed.

Where do I install/find the "Adventure Works DW database?" I can not find this other database anywhere?

Thanks.

AIM

During product installation make sure you install Samples.

After product is installed go to the Start->Programs->Microsoft SQL Server 2005->Documentation and Tutorials->Samples and run Setup for "Microsoft SQL Server 2005 Samples (English)"

You AdventureWorksDW Analysis Services sample project should be installed into C:\Program Files\Microsoft SQL Server\90\Tools\Samples. There you would also find relational database for it.

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

|||

Hi,

We can also install the default database in another way right from the very beginning. When we are installing the MSAS2005 After selecting the components that we want to install and click on the Advance button. Select "Entire feature will be installed on local hard drive" for the "Books Online and Samples" category.

Your AdventureWorksDW Analysis Services sample project should be installed into C:\Program Files\Microsoft SQL Server\90\Tools\Samples

Please let me know if I am wrong

Thanks

|||

I have been sending forum questions about setting up the Analysis services samples.

I finally figured this out: "Adventure Works DB" database and "AdventureWorksDW" are the same.

In the Analysis Services sample solution "Adventure Works DW," I was recieving Process errors on cubes. I right clicked the solution. I chose properties. I changed the database from Adventure Works DW (that does not exist) AdventureWorksDW. Next, I processed and did not get any errors.

This is from the latest April Samples collection. Maybe someone can tell the people at Microsoft to make this fix.

AIMDBA

sql

Adventure Works DW databases INSTALLATION

In SQL Server April 2006 Books Online (BOL)

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/instsql9/html/8c4c2af8-f2ed-4559-b5d2-984a9f7ce5ca.htm

... near the bottom, the following is said ...

To deploy the Adventure Works DW analysis services project

1. Make sure that the AdventureWorksDW and Adventure Works DW databases have been installed.

Where do I install/find the "Adventure Works DW database?" I can not find this other database anywhere?

Thanks.

AIM

During product installation make sure you install Samples.

After product is installed go to the Start->Programs->Microsoft SQL Server 2005->Documentation and Tutorials->Samples and run Setup for "Microsoft SQL Server 2005 Samples (English)"

You AdventureWorksDW Analysis Services sample project should be installed into C:\Program Files\Microsoft SQL Server\90\Tools\Samples. There you would also find relational database for it.

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

|||

Hi,

Your AdventureWorksDW Analysis Services sample project should be installed into C:\Program Files\Microsoft SQL Server\90\Tools\Samples

Please let me know if I am wrong

Thanks

|||

I have been sending forum questions about setting up the Analysis services samples.

I finally figured this out: "Adventure Works DB" database and "AdventureWorksDW" are the same.

This is from the latest April Samples collection. Maybe someone can tell the people at Microsoft to make this fix.

AIMDBA

Adventure Works DB Samples with Express 2005 SQL Server

Downloaded the file for the Adventure Works DB Sample. Which folder do I need to install it in to have show up in SQL Server Express 2005?

You can put it in any folder you want to (normally in the usual data folder you are using, by default this is located somehwere in the programe files , microsoft SQL Server directory. But you have to attach the database using the sp_attachdb procedure or use the GUI to attach the database, right click on the server --> attach database and you are done.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||You can attach DB's in the Management Studio Express then?|||Should be able to, right click on the Databases folder, if it is not listed try All Tasks|||did you get this to work ?I have attached the database and still could not get the report designer towork with the database.

Adventure Works DB

Hi,
I need Adventure Works Database (OLTP) for SQL Server 2000. Are there any
links available to download it?
Thanks in advance,
Leila
Hi, Leila
Did you search for it on http://download.microsoft.com ?
Here it is:
http://www.microsoft.com/downloads/d...DisplayLang=en
Razvan
|||Great :-)
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1134899730.713536.16340@.f14g2000cwb.googlegro ups.com...
> Hi, Leila
> Did you search for it on http://download.microsoft.com ?
> Here it is:
>
http://www.microsoft.com/downloads/d...c23-2356-436e-
94a8-2bfb66f0abdc&DisplayLang=en
> Razvan
>

Adventure Works DB

Hi,
I need Adventure Works Database (OLTP) for SQL Server 2000. Are there any
links available to download it?
Thanks in advance,
LeilaHi, Leila
Did you search for it on http://download.microsoft.com ?
Here it is:
http://www.microsoft.com/downloads/...&DisplayLang=en
Razvan|||Great :-)
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1134899730.713536.16340@.f14g2000cwb.googlegroups.com...
> Hi, Leila
> Did you search for it on http://download.microsoft.com ?
> Here it is:
>
http://www.microsoft.com/downloads/...9c23-2356-436e-
94a8-2bfb66f0abdc&DisplayLang=en
> Razvan
>

Adventure Works DB

Hi,
I need Adventure Works Database (OLTP) for SQL Server 2000. Are there any
links available to download it?
Thanks in advance,
LeilaHi, Leila
Did you search for it on http://download.microsoft.com ?
Here it is:
http://www.microsoft.com/downloads/details.aspx?FamilyID=487c9c23-2356-436e-94a8-2bfb66f0abdc&DisplayLang=en
Razvan|||Great :-)
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1134899730.713536.16340@.f14g2000cwb.googlegroups.com...
> Hi, Leila
> Did you search for it on http://download.microsoft.com ?
> Here it is:
>
http://www.microsoft.com/downloads/details.aspx?FamilyID=487c9c23-2356-436e-
94a8-2bfb66f0abdc&DisplayLang=en
> Razvan
>sql

Adventure Works DB

Hi,
I need Adventure Works Database (OLTP) for SQL Server 2000. Are there any
links available to download it?
Thanks in advance,
LeilaHi, Leila
Did you search for it on http://download.microsoft.com ?
Here it is:
http://www.microsoft.com/downloads/...&DisplayLang=en
Razvan|||Great :-)
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1134899730.713536.16340@.f14g2000cwb.googlegroups.com...
> Hi, Leila
> Did you search for it on http://download.microsoft.com ?
> Here it is:
>
http://www.microsoft.com/downloads/...9c23-2356-436e-
94a8-2bfb66f0abdc&DisplayLang=en
> Razvan
>

Adventure works databases

I am new to Analysis Services.

In the adventureworks sample, we have the oltp database (AdventureWorks), the datawarehouse (AdventureWorksDW) and the cube (Adventure Works DW). I am a little lost here. I thought the SSIS package updated the cube directly from the OLTP database.

Looks like the datawarehouse is used to convert the OLTP to a star schema and *then* the cube is loaded.

Could you please explain.

Thanks!

The datawarehouse (DW) is an OLAP multidimensional database that uses data from the OLTP relational database to create cubes, dimensions and so on. Whether the data in cubes is being updated automatically from the OLTP database depends on the settings, specifically the storage type of partition aggregations (MOLAP, ROLAP, HOLAP and variations). Those that are used in Adventure Works by default use MOLAP, which is great for analytical processing, reports and so on. Here is some more information on that: http://msdn2.microsoft.com/en-us/library/ms178416.aspx .

Adventure works database, I can't connect to the database

Database not found !

My web.config file has this bit of code:

<add name="AppConnectionString1" connectionString="Data Source=".\SQLEXPRESS";AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf";Integrated Security=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

But........................... the is no "AdventureWorks_Data.mdf" in that folder ?

And the database is installed in the right place !

help !

Where is the AdventureWorks_Data.mdf file located? AttachDbFilename must point to the location of the mdf file. It sounds like you have the wrong connection string. If you're not putting the database into the Data folder, then you need to change your connection string to point to the correct location.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||

Did this resolve the issue?

Mike

|||

Hmmm

I've downloaded and installed the Adventure works database

I follow the instructions give here

http://msdn2.microsoft.com/en-us/library/ms310325.aspx

but this command here:

exec sp_attach_db @.dbname=N'AdventureWorks', @.filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf', @.filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf'

results in this error:

unable to open physical file,................................................. the system can't find the specified file.

The things is....................... i can't find a "AdventureWorks_Data.mdf" file.

|||

When you downloaded and installed the AdventureWorks sample, you were probably asked for a directory to install to, that's where the database should be. If you don't recall where it went, try searching your hard drive for it. (Start | Search and then look for "AdventrueWorks*")

Once you know where it is, you can either change the path in the command above, or you can move it to the Data directory for the instanse of SQL that you want to attach it to. If search doesn't find the file, that probably means it either didn't get installed, or it was deleted. Try installing again. You may have to uninstall the sample from Add/Remove Programs before you can install it again.

Mike

|||

I basically have the same problem; however, I do have the: AdventureWorks_Data.mdf and AdventureWorks_log.ldf files in the proper directory. But I do a 'search' and do not see the 'AdventureWorks' db file.

I run the script:

and get:

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.

Msg 602, Level 21, State 50, Line 1

Could not find row in sysindexes for database ID 9, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Thanks for any help in advance...

Adventure works database, I can't connect to the database

Database not found !

My web.config file has this bit of code:

providerName="System.Data.SqlClient" />

</connectionStrings>

But........................... the is no "AdventureWorks_Data.mdf" in that folder ?

And the database is installed in the right place !

help !

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||

Did this resolve the issue?

Mike

|||

Hmmm

I've downloaded and installed the Adventure works database

I follow the instructions give here

http://msdn2.microsoft.com/en-us/library/ms310325.aspx

but this command here:

results in this error:

unable to open physical file,................................................. the system can't find the specified file.

The things is....................... i can't find a "AdventureWorks_Data.mdf" file.

|||

Mike

|||

I run the script:

and get:

Msg 1813, Level 16, State 2, Line 1

Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.

Msg 602, Level 21, State 50, Line 1

Could not find row in sysindexes for database ID 9, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Thanks for any help in advance...

Adventure Works Cube

Hi all,

I try to browser the Adventure Work Cube from the SQL Server Business Intelligence Development Sudio, but it give me the wrong result. Fisrt I have paste the Date.Fiscal hierachy in the Filter Filter and select July 1 2001 and July 2 2001, then I paste the Sales Amount measure in the Data Field. The result is $517,737.44. Now I paste the Date.Fiscal Year attribute in the Row Fields, but the result is change to $503,80592. The result is wrong. The sales amount has been excluded July 2 2001 data. Why this happen?

Thanks,

Thomas,

I've recreated the steps you described. I get the same $517,737.44 value you described. When I drag the Date.[Fiscal Year] attribute hierarchy to the rows, I continue to get $517,737.44.

Do you know what service pack you are working with?

Thanks,
Bryan

|||

Hi Bryan

I am using AS 9.00.3.042. When you filter the day, where you drag the Date.Fiscal hierarchy. If I put the Date.Fiscal hierarchy in the dimension filter panel, then the result is correct. However, if I put the Date.Fiscal hierarchy in the Drop Filter Fields, then the result is not correct.

Thanks,

|||

Sorry, but I'm just not getting the same behavior and I'm using the same SP level. Could you possibly trace the MDX that is submitted for the query (using Profiler) and post that to this thread? That might help determine what's going on.

Thanks,
Bryan

|||

Hi Bryan,

I am using 64bit, do you think that cause the problem. Here is what the MDX looklike:

WITH
SET [Filtered] AS
'
DISTINCT(
{
[Date].[Fiscal].[Date].&[2],
[Date].[Fiscal].[Date].&[1]
}
)
'
SET [Tree] AS
'
HIERARCHIZE(
GENERATE(
[Filtered],
{
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Year]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Semester]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Quarter]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Month]),
[Date].[Fiscal].CURRENTMEMBER
}
)
)
'
SELECT [Tree] ON Columns FROM [Adventure Works]

<PropertyList xmlns="urn Tongue Tied chemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>SchemaData</Content><Format>Multidimensional</Format><AxisFormat>TupleFormat</AxisFormat><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

CREATE SESSION
MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS
'
AGGREGATE({
[Date].[Fiscal].[Date].&[1],
[Date].[Fiscal].[Date].&[2]
})
'

<PropertyList xmlns="urn Tongue Tied chemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>None</Content><Format>Tabular</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

SELECT
NON EMPTY [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
{
[Measures].[Sales Amount]
}
ON ROWS
FROM [Adventure Works]
WHERE
(
[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

<PropertyList xmlns="urn Tongue Tied chemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Format>Native</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

Thanks,

|||

Hi Bryan,

Here is the result:

Date.Fiscal

(Multiple Items)

Fiscal Year Sales Amount

FY2002 $503,805.92

Grand Total $503,805.92

|||

I'm not seeing the set definition for [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0].

|||

Sorry Bryan,

I forgot to include this:

CREATE SESSION
SET [Adventure Works].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0] AS
'
{
{ [Date].[Fiscal Year].[All Periods] },
AddCalculatedMembers([Date].[Fiscal Year].[Fiscal Year].MEMBERS)
}
'

|||

Here's what I've been able to figure out.

You're dropping the Fiscal Hierarchy on to the "Drop Filters Here" section of the interface. This is what generates this session member:

Code Snippet

CREATE SESSION

MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS

AGGREGATE({

[Date].[Fiscal].[Date].&[1],

[Date].[Fiscal].[Date].&[2]

})

The AGGREGATE function is causing you to select the first member of the set. If you change the members to &[2] and &[3], you'll get the value for just July 2, 2001. If you replace AGGREGATE with SUM, you'll get the right result but that's not really an option here.

I can't really say why the AGGREGATE function is behaving this way, but would suggest you use the other filtering option in the browser window. If you look just above the "Drop Filters Here" section, you'll see a grid with an entry that looks like "Select Dimension". Apply your filter here.

What his does that is different is that it creates a subcube which limits the available values. This will give you the results you are looking for:

Code Snippet

CREATE SUBCUBE [Adventure Works] AS ( SELECT ( { [Date].[Fiscal].[Date].&[1], [Date].[Fiscal].[Date].&[2] } ) ON COLUMNS FROM [Adventure Works])

Hope that helps,
Bryan

|||

Hi Bryan,

The reason I drag the Fiscal Hierarchy in the "Drop Filters Here" is I want it to act like Excel Pivot Table. When I browser the cube though Excel Pivot Table, it has the same error. Most of our end user use Excel Pivot Table to brower the cube, but AS2005 give the wrong answer for this case.

Thanks

|||

I'm at a bit of a loss to explain this beahviour.

One possible work around is to hide the [Date].Fiscal Year] attribute. I find that having [Date].[Fiscal].[Fiscal Year] and [Date].[Fiscal Year] in the same cube just confuses things,especially when using OWC as it is not really mutliple hierarchy and attribute aware.

What your users would need to do then is to drag [Date].[Fiscal] onto the column and filter it in place. If you start from your example that is showing the incorrect figures and drag [Date].[Fiscal Year] off the grid and drag the [Date].[Fiscal] from the filter to the columns does that give you a grid that looks how you want?

|||

I asked a friend of mine on the product team and it is possible that this issue was fixed in the hotfix rollup that was released for SP2 http://support.microsoft.com/kb/939537 I could not find a specific reference, but there are a couple of broader issues which it might come under. It might be worth testing this in a Virtual PC to see if it corrects the issue.

As with all hotfixes, it is only recommended to install this if it does address a particular issue. Otherwise they recommend waiting for the next service pack, but I have not heard when another service pack is likely to be released. It would not surprise me to see the next SP come out after Windows Server 2008 is released (which should be Q1 next year), but this is just a guess on my behalf.

sql

Thursday, March 22, 2012

AdventureWorks DB question

AdventureWorks Database not showing up management studio express

AdventureWorks Database not showing up management studio express

Tuesday, March 20, 2012

Adventure Works?

Adventure Works.sln DEPLOY ERROR first time

Adventure Works DW.sln Starting to write data of the 'Internet ~1 ~MG' partition. Hangs Foreve

Adventure Works DW.sln error

Adventure Works DW.sln error

Adventure Works DW.sln error

Adventure Works DW databases INSTALLATION

Adventure Works DW databases INSTALLATION

Adventure Works DB Samples with Express 2005 SQL Server

Adventure Works DB

Adventure Works DB

Adventure Works DB

Adventure Works DB

Adventure works databases

Adventure works database, I can't connect to the database

Adventure works database, I can't connect to the database

Adventure Works Cube

SQL Adding a User account

Blog Archive

About Me