Query Cube using SQL Select instead of MDX Select

In Analysis Services 2000, you can query cube using regular SQL Select command.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmpr/prsql_70e0.asp

Does Analysis Services 2005 support this feature?

Right now, I am using OpenQuery with MDX query to return Cube data into a SQL table:

select * FROM OPENQUERY(Olap_Linked_Server, '

select [Measures].[Sales] on columns, [Accounts].[Hierarchy].Members on rows from SalesCube')

But the result SQL table has long column names. How can I rename them?

Thanks,

Bo

Do your first insert into a temp table(TSQL) and rename the columns when you do the next insert to a second table, by using standard 'as', for renaming.

Regards

Thomas Ivarsson

Query could not be processed. Access is forbidden

I am using http to access my cube (MS Analysis Server) and there are users
who are getting "Query could not be processed. Access is forbidden"
intermittently. Can someone help out and let me know what is causing this
issue? Some users are not getting this error.Hi

Googling on this and it does not turn anything up directly, although there
does seem to be an underlying implication that is it either connectivity or
permissions that is causing "Query could not be processed.". Without more
details it is not possible to say more, but I suggest that you check the
usual sources for this type of problem, such as: number of connections on
the server, the reliability of the network to connect, if different users
can connect from the same machines, if the machines that can't connect are
using the same versions of software.

Further information may spread more light on this, such how is the
connection being made etc..

John

"Ramir Santos" <ramir@.optonline.net> wrote in message
news:qag3d.80$K85.18831@.news4.srv.hcvlny.cv.net...
> I am using http to access my cube (MS Analysis Server) and there are users
> who are getting "Query could not be processed. Access is forbidden"
> intermittently. Can someone help out and let me know what is causing this
> issue? Some users are not getting this error.
>|||John,
Thanks for spending time to answer my question.
as a followup, here are some of the details
1. we are using office web component to connect to the cube (office xp
sp 2)
2. 2 separate machine, 1 hosting our aspx and the other with Analysis
Services and IIS installed. Analysis services installed using developers
edition
3. we only have 5 users right now
let me know what other information you would need to help me out on
this. appreciate your time and effort!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:414daf9b$0$20644$afc38c87@.news.easynet.co.uk. ..
> Hi
> Googling on this and it does not turn anything up directly, although there
> does seem to be an underlying implication that is it either connectivity
> or
> permissions that is causing "Query could not be processed.". Without more
> details it is not possible to say more, but I suggest that you check the
> usual sources for this type of problem, such as: number of connections on
> the server, the reliability of the network to connect, if different users
> can connect from the same machines, if the machines that can't connect are
> using the same versions of software.
> Further information may spread more light on this, such how is the
> connection being made etc..
> John
>
> "Ramir Santos" <ramir@.optonline.net> wrote in message
> news:qag3d.80$K85.18831@.news4.srv.hcvlny.cv.net...
>> I am using http to access my cube (MS Analysis Server) and there are
>> users
>> who are getting "Query could not be processed. Access is forbidden"
>> intermittently. Can someone help out and let me know what is causing this
>> issue? Some users are not getting this error.
>>
>>
>>
>>
>>|||Hi Ramir

It looks like this may be the problem?
http://tinyurl.com/4obw5

John

"Ramir Santos" <ramir@.optonline.net> wrote in message news:<Qyp3d.150$gR1.273403@.news4.srv.hcvlny.cv.net>...
> John,
> Thanks for spending time to answer my question.
> as a followup, here are some of the details
> 1. we are using office web component to connect to the cube (office xp
> sp 2)
> 2. 2 separate machine, 1 hosting our aspx and the other with Analysis
> Services and IIS installed. Analysis services installed using developers
> edition
> 3. we only have 5 users right now
> let me know what other information you would need to help me out on
> this. appreciate your time and effort!
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:414daf9b$0$20644$afc38c87@.news.easynet.co.uk. ..
> > Hi
> > Googling on this and it does not turn anything up directly, although there
> > does seem to be an underlying implication that is it either connectivity
> > or
> > permissions that is causing "Query could not be processed.". Without more
> > details it is not possible to say more, but I suggest that you check the
> > usual sources for this type of problem, such as: number of connections on
> > the server, the reliability of the network to connect, if different users
> > can connect from the same machines, if the machines that can't connect are
> > using the same versions of software.
> > Further information may spread more light on this, such how is the
> > connection being made etc..
> > John
> > "Ramir Santos" <ramir@.optonline.net> wrote in message
> > news:qag3d.80$K85.18831@.news4.srv.hcvlny.cv.net...
> >> I am using http to access my cube (MS Analysis Server) and there are
> >> users
> >> who are getting "Query could not be processed. Access is forbidden"
> >> intermittently. Can someone help out and let me know what is causing this
> >> issue? Some users are not getting this error.
> >>
> >>
> >>
> >>
> >>

Query calculated OLAP members

I'm trying to create a report using an OLAP cube as my data source, but I can
retrieve only the dimensions and measures, not the calculated members.
My query is:
select * from MasterCube
Is there any way to extract them?Below is a sample MDX query (used in the sample Foodmart reporting services
report) which includes a calculated measure, Store Cost.
SELECT { [Measures].[Store Sales], [Measures].[Store Cost] } ON COLUMNS,
{ Descendants([Product].[All Products], [Product].[Brand Name], LEAVES) }
ON ROWS,
{ Time.[1997].[Q1],Time.[1997].[Q2],Time.[1997].[Q3],Time.[1997].[Q4] } ON
PAGES
FROM Sales
--
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
"Cesar Meza" <CesarMeza@.discussions.microsoft.com> wrote in message
news:0A29D2FD-F3CF-4772-A063-E585C6B1DDA3@.microsoft.com...
> I'm trying to create a report using an OLAP cube as my data source, but I
> can
> retrieve only the dimensions and measures, not the calculated members.
> My query is:
> select * from MasterCube
> Is there any way to extract them?|||The real issue this: I have a cube that is too big for Excel's Pivot table to
handle it. So I'm trying to export it as PDF report using RS.
So I have several formulas that rely on the Analysis Services functionalliy
(aggregates, ranking, etc.) that are already in the cube.
I know I can write the MDX statement to recreate them, but this will mean to
re-create all the calculated members once more in the statement.
Any suggestions?.
Thanks a lot for your response.

Query and process performace with incremental update

hello all,

we are working on a project with a large scale of data (around 1000 rows per second).
we built a cube on this fact table.

this table will hold at most 90M rows.

we need the data in the cube to be "real time", that mean, up to date.

we are doing it by proactive caching- incremental update.

we also need a very good query performance.

that's why the storage mode is set to MOLAP.

we still get a low performace from the cube process and and the querys.

any suggestions how to solve this issues?

Thanks in advance,

Shy Engelberg - Certagon.

You might be seeing the results of the meta data locking (see http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx) If you are processing the cube very frequently. You probably need to profile the server to gather as much information as you can to figure out where the issues are.

Is it on the source system - selected only new records?

Is the system CPU, IO or memory bound?

Are you using partitions to isolate the processing to a smaller subset of the data?

|||

The SSAS 2005 Performance Guide is a good reference for these kinds of issues ( http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc).

I agree with Darren's idea of identifying whether the problem is occuring in retreiving source data records or in assembling the MOLAP structures. And partitioning may also be beneficial if you can isolate updates to a smaller partition.

You may also want to consider using HOLAP. HOLAP will give you excellent query performance for most queries with shorter processing times.

Bryan