Bug in SSAS Rolling up data?

I'm developing an Invoice cube and while I was debugging it I noticed some of the unit costs were too high, so I use the drillthrough to get the data behind it and the Row Count Measure (the one AS can add to your cube for you) had a 2 for one of the records.

All of the other records had a row count of 1, and it's my understanding that drillthrough shows the details of the data at the grain of the fact table.

I looked at the fact table and I had 2 distinct records that do indeed add up the the values represented in the erroneous record, so... Has anyone else had this? Is there a work around? Did I do something wrong?

Thanks in Advance,

Bryce

The drillthrough returns the most detail records in your cube schema aggregated at the grain of each of your dimensions, that's why you see a record count of 2 for some of the rows. What you will have to do is create a degenerate dimension off of your fact table which includes your primay key along with any other columns you want to see. You can then hide this dimension but include the columns in your drillthrough to obtain the most detailed records.

Hope this helps.

Van Dieu

|||

Do you think there will be a problem with the performance? To my understanding, the cube is going to have detailed granual level of data.

Bug in SSAS Rolling up data?

I'm developing an Invoice cube and while I was debugging it I noticed some of the unit costs were too high, so I use the drillthrough to get the data behind it and the Row Count Measure (the one AS can add to your cube for you) had a 2 for one of the records.

All of the other records had a row count of 1, and it's my understanding that drillthrough shows the details of the data at the grain of the fact table.

I looked at the fact table and I had 2 distinct records that do indeed add up the the values represented in the erroneous record, so... Has anyone else had this? Is there a work around? Did I do something wrong?

Thanks in Advance,

Bryce

The drillthrough returns the most detail records in your cube schema aggregated at the grain of each of your dimensions, that's why you see a record count of 2 for some of the rows. What you will have to do is create a degenerate dimension off of your fact table which includes your primay key along with any other columns you want to see. You can then hide this dimension but include the columns in your drillthrough to obtain the most detailed records.

Hope this helps.

Van Dieu

|||

Do you think there will be a problem with the performance? To my understanding, the cube is going to have detailed granual level of data.

Browsing cubes through interfaces other than the BI Studio?

I've built a cube, deployed it and processed it. I can use the brower in the BI Studio to see the data subsets and can manipulate it. How do I get this cube to the user without having to install BI Studios on their computer?

Basically how do I get the cube into IIS so the user can just open a web browser and go to it? Similar to how cognos does it with their cubes. Or is this not possible?

I see no way to view the cube unless you are using the BI Studio to open it. Am I missing something?

You are indee missing quite a lot.

There are many different ways to browse a cube using different applications. Just to name a few: Excel, Office Web Components ( you see it embedded in BI Dev studio), ProClarity, and many others.

After building a cube you should decide on the way you going to let your users to browse a cube data. Take a look at client application and evaluate which one works for you the best and then deploy it on client machines.

Try out a new PivotTables in Beta of Office2007.

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

|||

Could you point me to some documentation on or the correct direction to being able to view the cubes through a web interface? I'd like to be able to use strictly Server 2003 and SQL 2005 and be able to deploy these cubes to IIS for viewing without the use of a third system or special installations on PCs.

So with that in mind I'd like to be able to view these cubes from any computer that has an Internet explorer window.

|||

For instance take a look at this one http://www.proclarity.com/products/proclarity_for_sharepoint.asp

And this one http://www.panorama.com/products/nova_view_dashboards/index.html

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

|||If your cube is relatively simple and your reporting requirements are elementary, Report Builder is also a good (and free!) option.|||Try to use RadarCube ASP.NET for MS AS.|||

Take a look at this one:
http://www.beyond2020.com/products/vista_overview.php

For a demo:
http://www.beyond2020.com/products/demo.php

Browsing Cube

Hi,

I have created, populate and processed a cube successfully and can also browse it successfully on one server. But when I deploy this cube to another server I can create, populate and process the cube sucessfully but when I go to browse the cube through SQL Management Studio I recive the following error:

The query could not be processed: o OLE DB error: OLE DB or ODBC error: You do not have permission to run 'SP_TRACE_CREATE'.; 42000.

I have checked the security and it all appears to be the same, I am using the domain administrator on both servers.

Have you any idea what my issue might be?

Your help will be greatly appreciated.

Cheers,

Hi,

I resolved this issue myself and am position what my resolution so that i may hopefully help someone else if they are to encounter the same problem.

Resolution:

I set the authentication of the datasources being used by the cube to SQL authentication and then used the SQL server 'sa' account as the user.

|||This would imply that you are using ROLAP or HOLAP storage. You should not need to use the "sa" account. A user with db_datareader rights should be enough. The "sa" user has too many rights and really should not be used for anything other than administrative tasks.|||

Hi Darren,

All dimensions of the cube are using plain MOLAP and the cube itself is using plain MOLAP also. If you have any suggestions on how I could browse the cube with using 'sa' as the datasources user then that would be great as I also think as you mentioned that 'sa' should only be used for administration purposes.

Cheers,

|||

MOLAP storage only needs a SQL connection at processing time. When you browse it does not need a connection to the relational source. You could try using SQL Profiler to see what is going on. Do you have pro-active caching or something like that configured?

browsing cube

Hi

I have created a cube. I want to show it to the clients that do not have the Business Intellegence development studio or visual studio installed. Is there any other method of browsing a deployed cube?

Thanks in advance.

-padu

Probably the most widely available client is Excel.

Mosha has one of the best lists of SSAS clients on his wesite http://www.mosha.com/msolap/util.htm

|||Excel is a good choice. Also, you can generate a local cube file based on your data and let Excel connect directly to it. This way you can show the cube and data to the clients on a machine that doesn't run (and has no network connection to) the AS server.|||

Thanks guys,

This was really helpful.

-padu.

Browsing Cube

Hi,

I have created, populate and processed a cube successfully and can also browse it successfully on one server. But when I deploy this cube to another server I can create, populate and process the cube sucessfully but when I go to browse the cube through SQL Management Studio I recive the following error:

The query could not be processed: o OLE DB error: OLE DB or ODBC error: You do not have permission to run 'SP_TRACE_CREATE'.; 42000.

I have checked the security and it all appears to be the same, I am using the domain administrator on both servers.

Have you any idea what my issue might be?

Your help will be greatly appreciated.

Cheers,

Hi,

I resolved this issue myself and am position what my resolution so that i may hopefully help someone else if they are to encounter the same problem.

Resolution:

I set the authentication of the datasources being used by the cube to SQL authentication and then used the SQL server 'sa' account as the user.

|||This would imply that you are using ROLAP or HOLAP storage. You should not need to use the "sa" account. A user with db_datareader rights should be enough. The "sa" user has too many rights and really should not be used for anything other than administrative tasks.|||

Hi Darren,

All dimensions of the cube are using plain MOLAP and the cube itself is using plain MOLAP also. If you have any suggestions on how I could browse the cube with using 'sa' as the datasources user then that would be great as I also think as you mentioned that 'sa' should only be used for administration purposes.

Cheers,

|||

MOLAP storage only needs a SQL connection at processing time. When you browse it does not need a connection to the relational source. You could try using SQL Profiler to see what is going on. Do you have pro-active caching or something like that configured?

Browsing a cube in ssrs Matrix

When we browse the cube in management studio we are able to see the correct aggregates where as in the report from reporting services we are just getting the sums instead of running aggregates especially in the intersection cells of columns and rows. ( where ever the aggregrate function is difference/division. )

Is there a setting in reporting services which would make the report return the running aggregates like in the management studio.

thanks in advance

The brower is diferent in Reporting Services, but if you use the MATRIX in reporting Services, you all the cells for columns, rows and data!

Try it... if you never did that, use the wizard!

Helped?

Regards!

|||

--

|||

PedroCGD wrote:

The brower is diferent in Reporting Services, but if you use the MATRIX in reporting Services, you all the cells for columns, rows and data!

Try it... if you never did that, use the wizard!

Helped?

Regards!

Can you eloborate please.

Even when I use the matrix and the wizard still it dosent give me the running aggegates it is just giving me the sum in the intersections cells.

Any thots ?

Browser similar to SSAS required for SSRS

Hi,

How to import analysis browser in SSRS. I need features similar to SSAS Cube browser and SSAS dimention browser.

Regards

Spandan

As you probably know, SSRS has an MDX Designer which you can use if you target SSAS 2005. It is possible to host your own query designer although this feature is currently not documented. You need to contact the RS product group for more details.

Browser similar to SSAS required for SSRS

Hi,

How to import analysis browser in SSRS. I need features similar to SSAS Cube browser and SSAS dimention browser.

Regards

Spandan

As you probably know, SSRS has an MDX Designer which you can use if you target SSAS 2005. It is possible to host your own query designer although this feature is currently not documented. You need to contact the RS product group for more details.

Browse date

After I built the cube I clicked browse date in the cube
file and received the message Undefined error. What gives?
How do I access the data. I'm new to this program and was
following the sample program that came with Analysis
Manager.
Thanks for the helpYou should re-post this on the Datawarehouse newsgroup... They should be
able to help you there.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gary" <gsylvester@.npgcable.com> wrote in message
news:2b9ea01c46881$22a97fa0$a401280a@.phx.gbl...
> After I built the cube I clicked browse date in the cube
> file and received the message Undefined error. What gives?
> How do I access the data. I'm new to this program and was
> following the sample program that came with Analysis
> Manager.
> Thanks for the help

Browse date

After I built the cube I clicked browse date in the cube
file and received the message Undefined error. What gives?
How do I access the data. I'm new to this program and was
following the sample program that came with Analysis
Manager.
Thanks for the helpYou should re-post this on the Datawarehouse newsgroup... They should be
able to help you there.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gary" <gsylvester@.npgcable.com> wrote in message
news:2b9ea01c46881$22a97fa0$a401280a@.phx
.gbl...
> After I built the cube I clicked browse date in the cube
> file and received the message Undefined error. What gives?
> How do I access the data. I'm new to this program and was
> following the sample program that came with Analysis
> Manager.
> Thanks for the help

Browse date

After I built the cube I clicked browse date in the cube
file and received the message Undefined error. What gives?
How do I access the data. I'm new to this program and was
following the sample program that came with Analysis
Manager.
Thanks for the help
You should re-post this on the Datawarehouse newsgroup... They should be
able to help you there.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gary" <gsylvester@.npgcable.com> wrote in message
news:2b9ea01c46881$22a97fa0$a401280a@.phx.gbl...
> After I built the cube I clicked browse date in the cube
> file and received the message Undefined error. What gives?
> How do I access the data. I'm new to this program and was
> following the sample program that came with Analysis
> Manager.
> Thanks for the help

Browse Cube Causes CPU Spike & Unresponsive SSAS

Hello,

I'm interested to see if anyone else has seen the behavior I have experienced. I could have missed a KB article, however, hoping that someone will help with some advice anyhow.

I have a cube (it is around 400 mb). Every morning I execute a SSIS package to process the cube (full process). This works every morning without any failures. When I came in this morning, I opened Management Studio, connected to SSAS, went to the cube, right clicked, and chose browse.

In Task Manager, the Mem Usage by msmdsrv.exe started around 411,000. Once I chose browse, the cpu jumped to 40-60 percent. It has been almost 30 minutes now, and the cpu is still in that range. The mem usage has risen to 530,000, but I expected this.

The problem is the CPU. I can't see any reason for such heavy cpu usage for 30 minutes +. If I restart the SSAS service, and browse the cube again, it seems to work fine. I have seen this happne 4-5 times now.

We are using SQL Server 2005 enterprise (no SP) on Windows Server 2003 SP 1. The machine has 1.5 gig of ram.

Any suggestions or direction is greatly appreciated.

Regards,

Dan

This is strange.

It is hard to say what is going on.
Try and run Profiler connect to Analysis Server and see what it is doing during these 30 min. See what kind of activity reported.

Try and run ActivityViewer sample application and see which sessions are active and what is getting executed.

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

|||

Thanks for your response Edward. I followed your suggestions, but I'm not sure it yielded any useful results. Below is what I have typed up & pasted as I was looking through things.

I’ll start from the ActivityViewer.While I got a few errors from the JIT compiler getting it to run, I managed to hit continue each time and saw some interesting information.While I’m not 100% on how to interpret the findings, this is what I found:

Under Sessions, I found 25295. It is on the same cube/database that I tried to “Browse”.It appears that all the times show something around 12pm (although it is 8am… I’m going to ignore this for the time being) and under last command it has MDSCHEMA_CUBES.

If find this under “Transactions”

TRANSACTION_ID

TRANSACTION_SESSION_ID

TRANSACTION_START_TIME

TRANSACTION_ELAPSED_TIME_MS

TRANSACTION_CPU_TIME_MS

96EC1996-B252-4F80-A0FA-330EE37476BB

25A40F0E-A6FD-4FBA-A6FD-17DFFC0F2340

7/6/2006 12:36 PM

4176799

31

And this under “Locks”.The same Transaction ID above is causing the Lock.

SPID

LOCK_ID

LOCK_TRANSACTION_ID

LOCK_OBJECT_ID

LOCK_STATUS

LOCK_TYPE

LOCK_CREATION_TIME

LOCK_GRANT_TIME

25295

c6eceba6-19bc-480b-96fb-989273e4eade

96ec1996-b252-4f80-a0fa-330ee37476bb

<Object xmlns="urn:schemas-microsoft-com:xml-analysis:rowset"><DatabaseID>dw_prep_archive_PROD</DatabaseID></Object>

1

8

7/6/2006 12:36 PM

7/6/2006 12:36 PM

The time shown under the “lock creation time” is the time that I tried to browse the cube this morning.The profiler trace now shows 13:48 so this whole process has been occurring for over an hour (and my cpu is still above 45%).

This first set of data (below) from the profiler might show something useful, but I'm not sure what it means.

The second set below (seperated by a line) shows the set of commands that loop over and over again every 2 minutes or so. I know below is incredibly messy, but it basically shows “Flight recorder begin” followed by “discover locks”, “discover transactions”, “discover locks”, and then “Flight Recorder Snapshot End”.

Although I am relatively new to SSAS, I don’t think I see anything that could be causing this.Does anyone have any insight on what is happening here?

I really don't expect anyone to try to decipher below completely, but if anyone sees any unusual commands, I would appreciate it.

The only thing I can see is that my initial connection to browse the cube is trying to do something and in a transaction and is blocking everything else.

Regards,

Dan

---

---

Upon Connection

Discover Begin 26 - DISCOVER_PROPERTIES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<PropertyName>ProviderVersion</PropertyName>
</RestrictionList> 101 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:20.000 2006-07-06 12:35:20.000 3572 25267 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover End 26 - DISCOVER_PROPERTIES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<PropertyName>ProviderVersion</PropertyName>
</RestrictionList> 101 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:20.000 2006-07-06 12:35:20.000 0 0 3572 25267 0 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover Begin 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"><ObjectExpansion>ExpandFull</ObjectExpansion></RestrictionList> 99 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:22.000 2006-07-06 12:35:22.000 25271 QG
Discover End 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"><ObjectExpansion>ExpandFull</ObjectExpansion></RestrictionList> 99 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:22.000 2006-07-06 12:35:37.000 15107 0 25271 2625 QG
Discover Begin 26 - DISCOVER_PROPERTIES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<PropertyName>DBMSVersion</PropertyName>
</RestrictionList> 101 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:39.000 2006-07-06 12:35:39.000 3572 25267 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover End 26 - DISCOVER_PROPERTIES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<PropertyName>DBMSVersion</PropertyName>
</RestrictionList> 101 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:39.000 2006-07-06 12:35:39.000 0 0 3572 25267 0 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover Begin 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"><ObjectExpansion>ExpandObject</ObjectExpansion></RestrictionList> 103 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:44.000 2006-07-06 12:35:44.000 25280 QG
Discover End 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"><ObjectExpansion>ExpandObject</ObjectExpansion></RestrictionList> 103 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:44.000 2006-07-06 12:35:44.000 47 0 25280 47 QG
Discover Begin 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"><DatabaseID>dw_prep_archive_PROD</DatabaseID><ObjectExpansion>ExpandObject</ObjectExpansion></RestrictionList> 102 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:48.000 2006-07-06 12:35:48.000 25284 QG
Discover End 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"><DatabaseID>dw_prep_archive_PROD</DatabaseID><ObjectExpansion>ExpandObject</ObjectExpansion></RestrictionList> 102 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:48.000 2006-07-06 12:35:48.000 31 0 25284 31 QG
Discover Begin 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<ObjectExpansion>ExpandObject</ObjectExpansion>
</RestrictionList> 105 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:50.000 2006-07-06 12:35:50.000 3572 25289 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover End 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<ObjectExpansion>ExpandObject</ObjectExpansion>
</RestrictionList> 105 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:50.000 2006-07-06 12:35:50.000 47 0 3572 25289 47 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover Begin 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<DatabaseID>dw_prep_archive_PROD</DatabaseID>
<ObjectExpansion>ExpandObject</ObjectExpansion>
</RestrictionList> 105 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:52.000 2006-07-06 12:35:52.000 3572 25289 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover End 31 - DISCOVER_XML_METADATA <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<DatabaseID>dw_prep_archive_PROD</DatabaseID>
<ObjectExpansion>ExpandObject</ObjectExpansion>
</RestrictionList> 105 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:35:52.000 2006-07-06 12:35:52.000 31 0 3572 25289 31 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Timeout>0</Timeout> <SspropInitAppName>Micros
Discover Begin 26 - DISCOVER_PROPERTIES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<PropertyName>DBMSVersion</PropertyName>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:04.000 2006-07-06 12:36:04.000 3572 25295 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover End 26 - DISCOVER_PROPERTIES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<PropertyName>DBMSVersion</PropertyName>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:04.000 2006-07-06 12:36:04.000 0 0 3572 25295 0 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover Begin 4 - MDSCHEMA_CUBES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<CATALOG_NAME>dw_prep_archive_PROD</CATALOG_NAME>
<CUBE_NAME>DW PREP ARCHIVE</CUBE_NAME>
<CUBE_SOURCE>3</CUBE_SOURCE>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:04.000 2006-07-06 12:36:04.000 3572 25295 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover End 4 - MDSCHEMA_CUBES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<CATALOG_NAME>dw_prep_archive_PROD</CATALOG_NAME>
<CUBE_NAME>DW PREP ARCHIVE</CUBE_NAME>
<CUBE_SOURCE>3</CUBE_SOURCE>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:04.000 2006-07-06 12:36:04.000 78 0 3572 25295 16 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover Begin 4 - MDSCHEMA_CUBES 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:09.000 2006-07-06 12:36:09.000 3572 25295 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover End 4 - MDSCHEMA_CUBES 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:09.000 2006-07-06 12:36:09.000 0 0 3572 25295 0 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover Begin 4 - MDSCHEMA_CUBES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<CUBE_SOURCE>3</CUBE_SOURCE>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:09.000 2006-07-06 12:36:09.000 3572 25295 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover End 4 - MDSCHEMA_CUBES <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<CUBE_SOURCE>3</CUBE_SOURCE>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:09.000 2006-07-06 12:36:09.000 16 0 3572 25295 16 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Discover Begin 5 - MDSCHEMA_DIMENSIONS <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<CUBE_NAME>DW PREP ARCHIVE</CUBE_NAME>
<CUBE_SOURCE>3</CUBE_SOURCE>
</RestrictionList> 106 DMLENZ Microsoft SQL Server Management Studio 2006-07-06 12:36:10.000 2006-07-06 12:36:10.000 3572 25295 QG <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"> <Catalog>dw_prep_archive_PROD</Catalog> <ShowH
Query Subcube 2 - Non-cache data 00000000,000000000000000000000000,0000000,00,00000000,00000000,00000000 0 2006-07-06 12:36:10.000 2006-07-06 12:36:10.000 797 dw_prep_archive_PROD 25307 531
Query Subcube 2 - Non-cache data 00000000,000000000100100000011000,0000000,00,00000000,00000000,00000000 0 2006-07-06 12:36:10.000 2006-07-06 12:36:11.000 219 dw_prep_archive_PROD 25307 1156
Query Subcube 2 - Non-cache data 00000000,000000000100100000011000,0000000,00,00000000,00000000,00000000 0 2006-07-06 12:36:19.000 2006-07-06 12:36:19.000 406 dw_prep_archive_PROD 25307 9656

---

Profiler that "loops"

Notification11 - Flight Recorder Snapshot Begin0 2006-07-06 12:52:29.0002006-07-06 12:52:29.000 25544

Server State Discover Begin8 - DISCOVER_LOCKS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

<LOCK_MIN_TOTAL_MS>0</LOCK_MIN_TOTAL_MS>

</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.000 25545 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"></PropertyList>

Server State Discover End8 - DISCOVER_LOCKS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

<LOCK_MIN_TOTAL_MS>0</LOCK_MIN_TOTAL_MS>

</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.0000255450

Server State Discover Begin7 - DISCOVER_JOBS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

<JOB_MIN_TOTAL_TIME_MS>0</JOB_MIN_TOTAL_TIME_MS>

</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.000 25546 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"></PropertyList>

Server State Discover End7 - DISCOVER_JOBS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

<JOB_MIN_TOTAL_TIME_MS>0</JOB_MIN_TOTAL_TIME_MS>

</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.0000255460

Server State Discover Begin2 - DISCOVER_SESSIONS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.000 25547 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"></PropertyList>

Server State Discover End2 - DISCOVER_SESSIONS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.0000255470

Server State Discover Begin1 - DISCOVER_CONNECTIONS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.000 25548 <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis"></PropertyList>

Server State Discover End1 - DISCOVER_CONNECTIONS<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

</RestrictionList>0 2006-07-06 12:52:29.0002006-07-06 12:52:29.0000255480

Notification12 - Flight Recorder Snapshot End0 2006-07-06 12:52:29.0002006-07-06 12:52:29.000025544

|||

This is great investigation you've done!

Unfortunately, I think the situation probably indicates problem with Analysis Services.
Dont see anything you should be doing differently.

Before you go and log your case with Customer Support Services can you please try and find a machine you can install Analysis Services SP1 into.
And see if your situaion is solved in SP1.

Feel free to contact me if you get stuck with your efforts.

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

|||

Thanks for your suggestions Edward.

I'm pretty sure I found the culprit. Whether it is a bug in SSAS (or bad mdx), the person who created the cube has calculations and named sets using the calculations. I'm not entirely sure what the purpose of them were, however when I removed the named sets. the problem was solved. The cube allows me to click "Browse" without any hangups.

I am in the process of utilizing a support contract we have to determine if this is a problem.

Thanks again.

Regards,

Dan

Browse Cube

Just a question please,

When i browse(query) the cube in analysis services how can i save those queries?Can they be saved anyware?

(By browse i mean explore,drill down data in the Cube Browser of Analysis services 2005.)

Help please !!!

No, you cannot save queries done through the cube browser in the BI Development Studio (BIDS).

You could use Profiler to capture the MDX queries that are sent, but you would then have to use a different tool (like SSMS or Reporting Services) to run those queries. BIDS is not really meant to be a reporting tool. You will need to look at a different tool to do this, Excel pivot tables are probably one of the cheaper, more accessible solutions.

|||

You can capture the queries using SQL Server Profiler:

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

>>

SQL Server 2005 Books Online

Introduction to Monitoring Analysis Services with SQL Server Profiler

You can use SQL Server Profiler to monitor events generated by an instance of Microsoft . By using SQL Server Profiler, you can do the following:

Monitor the performance of an instance of Analysis Services.

Debug Multidimensional Expressions (MDX) statements.

Identify MDX statements that run slowly.

Test MDX statements in the development phase of a project by stepping through statements to confirm that the code works as expected.

Troubleshoot problems in Analysis Services by capturing events on a production system and replaying them on a test system. This approach is useful for testing or debugging purposes and lets users continue to use the production system without interference.

Audit and review activity that occurred on an instance of Analysis Services. A security administrator can review any one of the audited events. This includes the success or failure of a login try and the success or failure of permissions in accessing statements and objects.