Is it Possible to query a Cube with Query Analyzer?

i would like to query either a cube or dimension direclty using tsql and query analyzer. i thought i would be able to use openquery but cant figure out the proper syntax. any help would be appreciated.

This is an example in a previous discussion here. I have not tried this example myself.

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

HTH

Thomas Ivarsson

is it possible to process the cube using vb.net/c# which exists in the remote server?

Hi

can we process the cube exists in the remote system, using vb.net/c#

i am able to process in the same system, where analysis service available.

i couln't process this from the client system.

is there any soluctions?

You can use either the AMO or ADOMD libraries to send processing commands to a remote server.

The AMO library is easier to work with for admin tasks, but you would have to redeploy the dll's. Where as any client that has to query the cubes will already need have ADOMD installed.

The following send an XMLA processing command using ADOMD, all you need to do is to create a console application, add a reference to Microsoft.AnalysisServices.AdomdClient and paste in the following:

Code Snippet

Sub Main()

Dim serverName As String = "Server1"

Dim databaseName As String = "Adventure Works DW"

Dim databaseID As String = databaseName

Dim cubeID As String = "Adventure Works"

Dim cn As New AdomdConnection("Provider=MSOLAP;Data Source=" & serverName & ";Initial Catalog=" & databaseName)

Console.WriteLine("Opening Connection...")

cn.Open()

Dim cmd As AdomdCommand

cmd = cn.CreateCommand()

cmd.CommandType = CommandType.Text

cmd.CommandText = "<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><Parallel><Process> <Object>" & _

"<DatabaseID>" & DatabaseID & "</DatabaseID>" & _

"<CubeID>" & cubeID & "</CubeID> " & _

"</Object><Type>ProcessFull</Type><WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch>"

Try

Console.WriteLine("Executing Command...")

cmd.ExecuteNonQuery()

Console.WriteLine("Command Complete")

Catch ex As Exception

Console.WriteLine(" --== ERROR ==--")

Console.WriteLine(ex.Message)

Console.WriteLine()

Finally

cn.Close()

Console.WriteLine("Finished")

End Try

End Sub

|||

Hi Darren

I tried your solution using AdoMd 8.0 on AS 2005,

but it seems that no data are refreshed,

neverthless the command text is executed without any error

Do I miss any other instruction (i.e: 'comitt transaction', .)?

Thanks

|||

I don't think ADOMD 8.0 has the execute method you need 9.0 to work with AS2005.

I just double checked and if your account has permission to process the server, database and cube properties are set correctly it should work. Try running a profiler session on the server and see if there are any errors being thrown at that end.

Is it possible to make Calculation-values persistent in a Cube !?

Hello

Is it possible to make Calculation-values persistent in a Cube !?

I made an very intensive calculation based on historic data. This resolves rather in a complex MDX-statement and becomes slow at retrieving time

I was wondering.... Is there a way to calculate the value at processing time (by MDX-expression) and store it in the cube ?

I know there is a field 'MeasureExpression' but this field is not full MDX suitable !
Actually... Except for the aggregation-functions (sum, max, min, ...) I want to use a MDX-calculation-expression ?!Well, this functionality is present in AS2005 (in the form of the CACHE statement) but it's unsupported and pretty much undocumented, so I wouldn't recommend using it.

It might be a better idea to focus on tuning your MDX and/or your cube. Can you give us some more details on what you want to do?

Chris|||

Okay if you ask for it

Calculating the today's stocking:
By summerizing all historic mutations till today !

Sum({[Posting Period].[All Posting Period].FirstChild.FirstChild.FirstChild.FirstChild : Head(Descendants([Posting Period].CurrentMember, [Posting Period].[Day])).Item(0)}, [Measures].[Mutation])

[Posting Period] levels => [Year].[Quarter].[Month].[Day]

Can you tune up this MDX-expression ?

Kind regards

|||It depends on what kind of measure [Measures].[Mutation] is. Is it a regular measure of type Sum? If so, then the following technique might be useful:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!107.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!111.entry

If [Measures].[Mutation] is a calculated measure, what is the definition?|||[Measures].[Mutation] is not a calculated measure but a fact ! Yes, a regular measure of type Sum.

At my first sight ytd seems to be the function I need !?
ytd is a server-side calculation and not client-side !?

Thank you for your usefull reply |||

Well, no, YTD is a function which does something similar to what you've done in your calculated member, but it probably won't perform much better than what you've done.

The performance optimisation comes from the fact that in your calculated member, you are summing up a large set of Days. This is likely to perform badly for two reasons:
1) There might be a lot of Days to sum up
2) There are unlikely to be any aggregations built at the Day level

Therefore what the articles I linked to suggest doing is trying to replace any Days in the set to be summed up with their common ancestors. So, for example, say you wanted to sum up all the Days in 2005 up to 7th December. What you could do is find the set of Days from 1st January to 7th December, then go through it replacing all the Days in Q1 with the Q1 member, all the Days in Q2 with the Q2 member, all the Days in Q3 with the Q3 member, all the Days in October and November with those members, and then leave the rest. So your set would be something like:
{[Time].[2005].[Q1], [Time].[2005].[Q2], [Time].[2005].[Q3], [Time].[2005].[Q4].[OCTOBER], [Time].[2005].[Q4].[NOVEMBER], [Time].[2005].[Q4].[DECEMBER].[1], [Time].[2005].[Q4].[DECEMBER].[2], [Time].[2005].[Q4].[DECEMBER].[3], [Time].[2005].[Q4].[DECEMBER].[4], [Time].[2005].[Q4].[DECEMBER].[5], [Time].[2005].[Q4].[DECEMBER]., [Time].[2005].[Q4].[DECEMBER].[7]}

You can probably guess that summing up 12 values (which is what would happen if you have the correct aggregations built) is going to be quicker than >300 values!

|||

But my issue is more intensive than that....
For every day... I want to sum up from the very first mutation till today... Even if this very first mutation begins at the year eg. 1998 !

[Time].[2005].[Q1].[jan].&[1] = Sum(all days of 98, 99, ..., 05 day 1)
[Time].[2005].[Q1].[jan].&[2] = Sum(all days of 98, 99, ..., 05 day 1, 2)
[Time].[2005].[Q1].[jan].&[3] = Sum(all days of 98, 99, ..., 05 day 1, 2, 3)

For every day in a year: I have to go back to the very first mutation (and the very first year) and sum all (previous days of every previous year) up till this year and this day !
That's why I was wondering... can you make calculations persistent ? or even cache it in the background !?

|||Well yes, but with this technique each extra complete year only means one more member to be added up (all days in 1998 are replaced with the 1998 member on the year level), so it doesn't make much difference. Try it if you can work out the MDX: I've seen it make a massive difference to query performance, and to be honest even if you did work out how to cache calculations then I think this would be the better approach.|||The following article also covers the issue:

http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm

I have found it very useful...|||Yes indeed.... it's a very very interested article
Many thx Michael.

Is it possible to make Calculation-values persistent in a Cube !?

Hello

Is it possible to make Calculation-values persistent in a Cube !?

I made an very intensive calculation based on historic data. This resolves rather in a complex MDX-statement and becomes slow at retrieving time

I was wondering.... Is there a way to calculate the value at processing time (by MDX-expression) and store it in the cube ?

I know there is a field 'MeasureExpression' but this field is not full MDX suitable !
Actually... Except for the aggregation-functions (sum, max, min, ...) I want to use a MDX-calculation-expression ?!Well, this functionality is present in AS2005 (in the form of the CACHE statement) but it's unsupported and pretty much undocumented, so I wouldn't recommend using it.

It might be a better idea to focus on tuning your MDX and/or your cube. Can you give us some more details on what you want to do?

Chris|||

Okay if you ask for it

Calculating the today's stocking:
By summerizing all historic mutations till today !

Sum({[Posting Period].[All Posting Period].FirstChild.FirstChild.FirstChild.FirstChild : Head(Descendants([Posting Period].CurrentMember, [Posting Period].[Day])).Item(0)}, [Measures].[Mutation])

[Posting Period] levels => [Year].[Quarter].[Month].[Day]

Can you tune up this MDX-expression ?

Kind regards

|||It depends on what kind of measure [Measures].[Mutation] is. Is it a regular measure of type Sum? If so, then the following technique might be useful:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!107.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!111.entry

If [Measures].[Mutation] is a calculated measure, what is the definition?|||[Measures].[Mutation] is not a calculated measure but a fact ! Yes, a regular measure of type Sum.

At my first sight ytd seems to be the function I need !?
ytd is a server-side calculation and not client-side !?

Thank you for your usefull reply |||

Well, no, YTD is a function which does something similar to what you've done in your calculated member, but it probably won't perform much better than what you've done.

The performance optimisation comes from the fact that in your calculated member, you are summing up a large set of Days. This is likely to perform badly for two reasons:
1) There might be a lot of Days to sum up
2) There are unlikely to be any aggregations built at the Day level

Therefore what the articles I linked to suggest doing is trying to replace any Days in the set to be summed up with their common ancestors. So, for example, say you wanted to sum up all the Days in 2005 up to 7th December. What you could do is find the set of Days from 1st January to 7th December, then go through it replacing all the Days in Q1 with the Q1 member, all the Days in Q2 with the Q2 member, all the Days in Q3 with the Q3 member, all the Days in October and November with those members, and then leave the rest. So your set would be something like:
{[Time].[2005].[Q1], [Time].[2005].[Q2], [Time].[2005].[Q3], [Time].[2005].[Q4].[OCTOBER], [Time].[2005].[Q4].[NOVEMBER], [Time].[2005].[Q4].[DECEMBER].[1], [Time].[2005].[Q4].[DECEMBER].[2], [Time].[2005].[Q4].[DECEMBER].[3], [Time].[2005].[Q4].[DECEMBER].[4], [Time].[2005].[Q4].[DECEMBER].[5], [Time].[2005].[Q4].[DECEMBER]., [Time].[2005].[Q4].[DECEMBER].[7]}

You can probably guess that summing up 12 values (which is what would happen if you have the correct aggregations built) is going to be quicker than >300 values!

|||

But my issue is more intensive than that....
For every day... I want to sum up from the very first mutation till today... Even if this very first mutation begins at the year eg. 1998 !

[Time].[2005].[Q1].[jan].&[1] = Sum(all days of 98, 99, ..., 05 day 1)
[Time].[2005].[Q1].[jan].&[2] = Sum(all days of 98, 99, ..., 05 day 1, 2)
[Time].[2005].[Q1].[jan].&[3] = Sum(all days of 98, 99, ..., 05 day 1, 2, 3)

For every day in a year: I have to go back to the very first mutation (and the very first year) and sum all (previous days of every previous year) up till this year and this day !
That's why I was wondering... can you make calculations persistent ? or even cache it in the background !?

|||Well yes, but with this technique each extra complete year only means one more member to be added up (all days in 1998 are replaced with the 1998 member on the year level), so it doesn't make much difference. Try it if you can work out the MDX: I've seen it make a massive difference to query performance, and to be honest even if you did work out how to cache calculations then I think this would be the better approach.|||The following article also covers the issue:

http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm

I have found it very useful...|||Yes indeed.... it's a very very interested article
Many thx Michael.

is it possible to group calculated measures?

There are a lot of calculated measures on my cube. Is it possible to group calculated measure in

folders like you can group attibutes of dimensions?

Yes it is possible. You will find it if you mark the small calculation properties button in the calculation tab in the cube editor.

HTH

Thomas Ivarsson

Is it possible to create cube file without Microsoft Analysis Services

Can I create a cube file, .cub ,without the Microsoft Analysis Services
Pls Guide me as I am new in this field.
Thanks
LoydI think that other products (Cognos, MicroStrategy, possibly others) may let you do this, but I am not certain.

I do know that MS Excel will let you OPEN a .CUB file, but you can't create a .CUB file.

Regards,

hmscott

Can I create a cube file, .cub ,without the Microsoft Analysis Services

Pls Guide me as I am new in this field.

Thanks
Loyd|||Hi Loyd you can create a .cub file using MSQuery in presnt in MS office.

What do you intend to use it for??

Try and let me know.

Regards,
Hemanrh|||You can create .cub file with MS excel bat you need anyway Analysis Services to do it.
I don't know any other programs

is it possible to create a cube fact which is base on the result of a stored procedure

I want to create a cube fact which is base on the result of a stored procedure, is it possible?

In software there are few things that are not possible :)

The most common schenario for building cubes if first to build a data warehouse where you collect data in several tables. Then you design your cube and load data from datawarehouse.

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

Is Excel ASOLEDB9 taking advantage of cube partitioning?

Hi,

I wonder if Excel ASOLEDB9 is benefiting from cube partitioning?
Some queries are very slow and the Excel generated code look not that good

I tried to pick some queries from SQL Server Profiler and run them in an mdx query window and I get syntax errors.

This leaves me perplex since I have the feeling that people try endless queries through their Excel pivot cube, then, after a while they cancel the Excel query because it takes forever, then the server remain stuck on a high level of CPU usage.

Is it because the syntax error or is it because they just ask for too much data?
Is canceling an Excel pivot data refresh enough to stop the server's query processing?

Yesterday night, it was so bad (100% CPU) that I had to restart the server.

Thanks,

Philippe

Cube partitioning is server-side, so all clients should benefit from it.

When you cancel a query in Excel, you dont cancel it server-side. Check out the following thread for more info on this.

|||Guys,
This is going to be a big problem.
Queries cancelled by the user keep running on the server.

This kills the server and there is no way that someone would spend time trying to manually trace these runaway queries and manually cancel them on the server.

It is also a big issue to have to restart the server everyday just because of these runaway queries.

I would like to see a fix for it in SP2 with a high Priority rating.

This is a server killer.

Probably the biggest bug ever in SSAS2005.

Philippe|||

If anything, this is probably a Excel bug.

If you are really struggeling with this you could try to write some custom code that identifies long running queries (look at the activityviewer sample application). Then you could cancel these queries with a xmla cancel command. Finally schedule your code to run every ten minutes or so with SQL Server Agent.

Is cube browser available as seperate control for use in winforms app?

I love the cube browser in SQL Server management studio and would like to use it in a client app. But I don't want to give end users the SQL Server management studio.

Is it available as a seperate control or is there another one that would be available to me? I have tried the Excel addin for cube analysis but it is not as flexible and a Pivot Table report is pretty static.

Ta

Dirc

It's actually just a slightly modified OWC pivot table.|||

I installed OWC 11 and added a axPivotTable control to my winform. There were so many problems getting it to do anything I gave up.

Using COM controls designed for the web in a winform app seems a bit dodgy anyway....

|||I agree, it's a little odd to work with. And the properties aren't documented that well. But it's a lot better than having to code it yourself :)|||

Hi,

I am also trying to use the OWC control, but I am using it for a web based olap browser that needs to be integrated. If you have some links, material, information regarding the use of OWC, please do forward me the same.

Also, if you are succesful in implementing the OWC, give me some pointers.

thanks

Regards

|||

Found RadarCube NET Windows Forms for MS AS: http://www.radar-soft.com/products/radarwin_msas.aspx

Has similar functionality.