Showing posts with label group. Show all posts
Showing posts with label group. Show all posts

Thursday, March 22, 2012

Analysis

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.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

I'm using AMO (based on the Adventure Works sample) to create a simple cube. I create it with a Measure Group named BaseFact that has 2 measures, Dollar Sales (DOLSLS) and Package Sales (PKGSLS). The cube create works and I can see the correct values for each of the measures when I browse it in Management Studio connected to Analysis Services.

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

I'm using AMO (based on the Adventure Works sample) to create a simple cube. I create it with a Measure Group named BaseFact that has 2 measures, Dollar Sales (DOLSLS) and Package Sales (PKGSLS). The cube create works and I can see the correct values for each of the measures when I browse it in Management Studio connected to Analysis Services.

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?

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 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" wrote in message news:eOFXsg3WDHA.652@.T=K2MSFTNGP10.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--|||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" = wrote in message news:%23vkaej3WDHA.=2312@.TK2MSFTNGP10.phx.gbl...
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" wrote in message news:eOFXsg3WDHA.652@.T=K2MSFTNGP10.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--

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.

|||Oh, sorry, I didn't think about the metric system |||hey, what about if im printing the report using a landscape format?

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.

|||Oh, sorry, I didn't think about the metric system |||hey, what about if im printing the report using a landscape format?

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

I have another question from my Uni assignment! I'm sure it's the way the questions are written that I'm having difficulty with! :(

==============================
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...