Thursday, March 22, 2012
Analysis
used to use SSMS then moved onto using BIDS to browse cubes. Since BIDS
isn't a really good end user tool for the general users - should we be
looking at getting them up on Excel pivot tables or Reporting Services and
using a Report Model? I see the Report Model from a cube is somewhat harder
than designing your data set with BIDS.Thanks for the comment - Proclarity does rock but we are in the $25k to $35k
for that tool - whereas we own the other tools already.
The 2007 Excel is really nice with the Pivot tables.
"BYU" <BYU@.discussions.microsoft.com> wrote in message
news:FA94F6FF-469A-40AA-A484-CA776E5FA1C8@.microsoft.com...
> BIDS is really a development tool. We are using both SSRS and Excel pivot
> table here. SSRS provides more standard/fixed format presentation of
> cubes.
> Excel sounds more suitable for your users since they are already familiar
> with dimensions and measures. Another tool, also from MS is ProClarity,
> which gives cube users much more flexibilites as well as functionalities.
> "Joe" wrote:
>> We have a decision support group here and we have built many Cubes. They
>> used to use SSMS then moved onto using BIDS to browse cubes. Since BIDS
>> isn't a really good end user tool for the general users - should we be
>> looking at getting them up on Excel pivot tables or Reporting Services
>> and
>> using a Report Model? I see the Report Model from a cube is somewhat
>> harder
>> than designing your data set with BIDS.
Friday, February 24, 2012
AMO created cube and Calculated Measures
To continue with the cube (and my education) I want to dynamically add a calculated measure or two. The forums seem to suggest that this is not possible using AMO and suggest using the cubes MdxScript to create calculated measures.
I used script to new query window to look at the XMLA script for the cube. There is no MdxScripts section in the XML text. If I look at the cube in the debugger it shows the MdxScripts collection count to be 0. After running the code below to get an MdxScript object, add new commands and update the cube I can again look at the XMLA for the cube and now there is a MdxScript with only the new measures I created. The measures also show up when I browse the cube. However, I can no longer see any data for the original measures or the added measures. I'd appreciate any help in understanding what is happening.
Server srv = new Server();
srv.Connect("localhost");
Database db = srv.Databases["MyCube"];
Cube cb = db.Cubes["MyCube"];
MdxScript ms = cb.MdxScripts.Add();
string cmd = "CREATE MEMBER CurrentCube.Measures.Price as [Measures].[DOLSLS] / [Measures].[PKGSLS]";
ms.Commands.Add(new Command(cmd));
cmd = "CREATE MEMBER CurrentCube.Measures.MyMem2 as [Measures].[Price] * 2";
ms.Commands.Add(new Command(cmd));
cmd = "CREATE MEMBER CurrentCube.Measures.MyMem3 as [Measures].[Price] * 3";
ms.Commands.Add(new Command(cmd));
ms.Update();
cb.Update();
cb.Process();
srv.Dispose();
Thanks
John
Solved it.
1. Added semi-colon to end of strings.
2. I thought I had read somewhere in the forums that the CALCULATE as the first command was optional but it isn't. Added the CALUCULATE and it all works.
John
AMO created cube and Calculated Measures
To continue with the cube (and my education) I want to dynamically add a calculated measure or two. The forums seem to suggest that this is not possible using AMO and suggest using the cubes MdxScript to create calculated measures.
I used script to new query window to look at the XMLA script for the cube. There is no MdxScripts section in the XML text. If I look at the cube in the debugger it shows the MdxScripts collection count to be 0. After running the code below to get an MdxScript object, add new commands and update the cube I can again look at the XMLA for the cube and now there is a MdxScript with only the new measures I created. The measures also show up when I browse the cube. However, I can no longer see any data for the original measures or the added measures. I'd appreciate any help in understanding what is happening.
Server srv = new Server();
srv.Connect("localhost");
Database db = srv.Databases["MyCube"];
Cube cb = db.Cubes["MyCube"];
MdxScript ms = cb.MdxScripts.Add();
string cmd = "CREATE MEMBER CurrentCube.Measures.Price as [Measures].[DOLSLS] / [Measures].[PKGSLS]";
ms.Commands.Add(new Command(cmd));
cmd = "CREATE MEMBER CurrentCube.Measures.MyMem2 as [Measures].[Price] * 2";
ms.Commands.Add(new Command(cmd));
cmd = "CREATE MEMBER CurrentCube.Measures.MyMem3 as [Measures].[Price] * 3";
ms.Commands.Add(new Command(cmd));
ms.Update();
cb.Update();
cb.Process();
srv.Dispose();
Thanks
John
Solved it.
1. Added semi-colon to end of strings.
2. I thought I had read somewhere in the forums that the CALCULATE as the first command was optional but it isn't. Added the CALUCULATE and it all works.
John
Thursday, February 16, 2012
am i in the right place?
Is this the right place, or can you reccomend another group?
Thanks,
Dana WannerThis is a multi-part message in MIME format.
--=_NextPart_000_0183_01C35B57.3F9E30C0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Actually, microsoft.public.sqlserver.programming is the place for query =help.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dana Wanner" <dwanner@.westportofficefurniture.com> wrote in message =news:eOFXsg3WDHA.652@.TK2MSFTNGP10.phx.gbl...
I would like to find a newsgroup where I could get some basic querying =help.
Is this the right place, or can you reccomend another group?
Thanks,
Dana Wanner
--=_NextPart_000_0183_01C35B57.3F9E30C0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Actually, =microsoft.public.sqlserver.programming is the place for query help.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Dana Wanner"
--=_NextPart_000_0183_01C35B57.3F9E30C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0027_01C35B4E.5C99DCF0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks Tom!
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23vkaej3WDHA.2312@.TK2MSFTNGP10.phx.gbl...
Actually, microsoft.public.sqlserver.programming is the place for =query help.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dana Wanner" <dwanner@.westportofficefurniture.com> wrote in message =news:eOFXsg3WDHA.652@.TK2MSFTNGP10.phx.gbl...
I would like to find a newsgroup where I could get some basic querying =help.
Is this the right place, or can you reccomend another group?
Thanks,
Dana Wanner
--=_NextPart_000_0027_01C35B4E.5C99DCF0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks Tom!
"Tom Moreau"
Actually, =microsoft.public.sqlserver.programming is the place for query help.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Dana Wanner"
--=_NextPart_000_0027_01C35B4E.5C99DCF0--
Monday, February 13, 2012
Always get one more blank page after group footer?
hi,
I have a report, basically, it has a table, and a group, and I checked the "Page break " at end in the group properties window. But when I run the report, between two group, there always is one more blank page there which is not what I want. If I clear that checkbox, the second data group did not change to a new page.
So, I guess I should check the checkbox, but how to remove the blank page?
Thanks.
Hi,
You're right with checking the Checkbox, but make sure that your width of the page is not more than the standart page-width, which is 17.5.
I tried hard before i found it out ;-)
|||hey, thank you so much.
This is exactly what is happening for me.
If the width is in inch, in layout page, we need to change the width <= 6.5in
Thanks again.
the width will definitely be more than 6.5 in, but i need it to be wider since im printing in a landscae format
Always get one more blank page after group footer?
hi,
I have a report, basically, it has a table, and a group, and I checked the "Page break " at end in the group properties window. But when I run the report, between two group, there always is one more blank page there which is not what I want. If I clear that checkbox, the second data group did not change to a new page.
So, I guess I should check the checkbox, but how to remove the blank page?
Thanks.
Hi,
You're right with checking the Checkbox, but make sure that your width of the page is not more than the standart page-width, which is 17.5.
I tried hard before i found it out ;-)
|||hey, thank you so much.
This is exactly what is happening for me.
If the width is in inch, in layout page, we need to change the width <= 6.5in
Thanks again.
the width will definitely be more than 6.5 in, but i need it to be wider since im printing in a landscae format
Thursday, February 9, 2012
Alternative to GROUP BY
==============================
Write a query with the same meaning as the following query, but does not use a GROUP BY clause.
SELECT EventId, MIN (ElapsedTime), MAX (ElapsedTime)
FROM Results
GROUP BY EventId
=============================
There are approx 8 results per event, each with an elapsed time - it is results of swimming races.
I thought you had to use a GROUP BY with aggregate functions? I thought about temporary tables, but that doesn't eliminate the group by.
Thanks!
Nerddettewhat a wonderful question!!
have you tried a three-way self join with two correlated subqueries?
it will give the right answer, but it's not very efficient!!|||Tell your teacher he/she is an idiot and the query works perfectly well the way it's written. (grin)|||derrick, it was an intellectual exercise
whatsamatter, you couldn't do it? :p|||No, I couldn't do it because it's stupid. :) I'm allergic to doing something stupid for intellectual reasons.|||whatsamatter, you couldn't do it? :p
* puts her hand up * Um... yes? I still can't do it!
Any ideas, any commands that I can look up the syntax for? I think it might involve a subquery in the SELECT list, but I'm not sure. :(
Thanks.
Nerddette|||nerddette, i gave you my idea in post #2
i tested it and it works|||Without knowing which version of what database engine you are using, it is kind of tough to forumuate an answer. Assuming that your particular engine supports at least the basic SQL-92 syntax, you could do a SELECT DISTINCT to get the grouping done for you (it does the same thing, but doesn't require the GROUP BY clause). Once you've got that, you can probably use correlated sub-queries to get the Min and Max values for that particular EventId.
If you are looking for "pre-cooked" SQL, ready to submit for a grade, you will probably wait a long time here. I'm always happy to help somebody out, but I'm allergic to doing other people's homework!
I think that Rudy is just being perverse with the three-way self-join. I can see how it would work (although I wouldn't want to actually watch it), but I can think of at least 1e2 (an inside joke) easier ways to do it!
-PatP|||If you are looking for "pre-cooked" SQL, ready to submit for a grade, you will probably wait a long time here. I'm always happy to help somebody out, but I'm allergic to doing other people's homework!
Thanks for your help. I'm not looking for "pre-cooked" answers, but I understand your concerns as I get students at Uni wanting the same from me. :)
Nerddette|||I think that Rudy is just being perverse with the three-way self-join.
nope
it was the only way i could think of to do it
i do not, however, see how your way would work, pat
a correlated subquery can be used to restrict which value of ElapsedTime is chosen, but how do you get both min and max selected without a self-join?|||Folks,
How about following
select distinct EventId
,min_elapsed=(select min(b.ElapsedTime)
from Results c
where b.EventId=a.EventId)
,max_elapsed=(select max(c.ElapsedTime)
from Results c
where c.EventId=a.EventId)
from Results a|||a correlated subquery can be used to restrict which value of ElapsedTime is chosen, but how do you get both min and max selected without a self-join?Sushant is one character away from what I intended.
-PatP|||sushant, that's eventually where my self-join was headed
pat's right, i was being sneaky (i am trying to learn from a master)
i wasn't just going to plop orthogonality into the discussion without some sort of leadup
by the way you have a syntax error, or was that on purpose too? (just kidding)|||i wasn't just going to plop orthogonality into the discussion without some sort of leadupAwwww, why not ?!?!
Sneaky? Is there somebody being sneaky around here? Who, where, how ? Why am I always the last one to find out about these things ?!?!
-PatP|||speaking of orthogonality, i went in search of a few good links, and look what i found:
RelationalWeenie (http://c2.com/cgi/wiki?RelationalWeenie)
two things of interest there: the entire wiki looks like a goldmine for computer related stuff, and look, it's another site with a two-character domain name -- do you have any idea what that domain name might be worth on the open market? and there i was, at the dawn of the web, when there were plenty of these names available, and i never bothered to snap a few up...