A Sample of an MDX query?

Hello,

I have a cube that includes a measure called student count that is pivoted as data below, row called student statuses and column being a time dimension. The one thing left that I would like to accomplish is to create two extra calculated measures called Difference and %Changed using MDX or any other method? Does anyone has some MDX samples or ideas how I could calculate those two columns in blue in my SQL Server Analysis 2005 cube?

Any help is greatly appreciated!

Spring Semester 2006Spring Semester 2005Difference%ChangeUndergraduates Count490045004008.89%Graudates Count100090010011.11%

You can use Business Intelligence Development Studio to add your calculated measures. Start BIDS in Program Files->Microsoft Sql Server 2005->Sql Server Business Intelligence Development Studio and open your database. Double click on the cube and after the cube is opened, click on to the 3rd tab which says "Calculations" to add new calculated members. For more information, check http://msdn2.microsoft.com/en-us/library/ms169748.aspx.

HTH,

Chu

a row value between two dates

hi,

i have a resultset as ( sample )

id startdate enddate value

1 01.01.2007 10.05.2007 20

2 04.01.2007 12.04.2007 40

3 07.01.2007 09.06.2007 30

.

.

i need an olap resulset of the query : value between startdate (xx.xx.xx) and enddata (xx.xx.xx)

how am i supposed the get it on SSAS

2 different dimensions? mdx ?

plz help i could not find a resolution..

thx in advance

Keep in mind I have worked with this for less than a year, so take it for what it's worth...

It looks like you are still in a relational world - the MDX world is a bit different. You would have to have a fact table that holds the value and the foreign keys for your dimension table(s), which contain your dates. So, your schema would look like:

Code Block

fact_values

-

id [business key]

start_calendar_key

end_calendar_key

value

dim_calendar

-

calendar_key

date

and the contents of the tables:

Code Block

fact_values

-

1 20070101 20071005 20

2 20070401 20071204 40

3 20070701 20070906 30

You would create a single dimension (Calendar), include it twice in your cube (once for start date, once for end date - see "Role Playing Dimensions" - http://technet.microsoft.com/en-us/library/ms174487.aspx) and a single measure group based on your fact table. Do a sum on the value.

And your MDX would do something like:

Code Block

Select [Measures].[Fact Value] on 0

From [Cube Name]

Where ( { [Calendar Start].[Date].&[YYYYMMDD] : [Calendar Start].[Date].&[YYYYMMDD] }

, { [Calendar End].[Date].&[YYYYMMDD] : [Calendar End].[Date].&[YYYYMMDD] }

)

I think this is what you were after... I'm sure there's a hundred ways to model this out -

Hope this helps,

John

a question about CLR Integration on Trigger coding

I wanna create a trigger using c#. basing on the SQL 2005 Sample, I have
written following code:
[SqlTrigger(Name = "TestForInsert", Target = "[MyUser].[TestTable]",
Event = "FOR INSERT")]
……// c sharp code
when compiling the source code, VS 2005 pops a error which means that it can
not find the object "[MyUser].[TestTable]"
Using the Profiler, I found that when compiling, .Net runtime just add
square brackets at the Target parameter value. In my code, the target is jus
t
[[MyUser].[TestTable]], but [MyUser] should be a schema
how should i do?From the c# documentation I have, you are doing everything correctly. The
code sample I have is
[SqlTrigger(Name = @."EmailAudit", Target = "[dbo].[Users]", Event = "FOR
INSERT, UPDATE, DELETE")]
Perhaps you should attempt to create a trigger on an object owned by the dbo
and then just reference the table name (without the owner). I'd be
interested in hearing how you progress with this.
--
Adam J Warne, MCDBA
"DaemonLin" wrote:

> I wanna create a trigger using c#. basing on the SQL 2005 Sample, I have
> written following code:
> [SqlTrigger(Name = "TestForInsert", Target = "[MyUser].[TestTable]",
> Event = "FOR INSERT")]
> ……// c sharp code
> when compiling the source code, VS 2005 pops a error which means that it c
an
> not find the object "[MyUser].[TestTable]"
> Using the Profiler, I found that when compiling, .Net runtime just add
> square brackets at the Target parameter value. In my code, the target is j
ust
> [[MyUser].[TestTable]], but [MyUser] should be a schema
> how should i do?|||It's been a while since I examined CLR integration so when you say this
->I wanna create a trigger using c#
Can I ask why you wanna.?
"DaemonLin" <DaemonLin@.discussions.microsoft.com> wrote in message news:1129B2AD-515D-4DF5-
B665-131E59939A67@.microsoft.com...
>I wanna create a trigger using c#. basing on the SQL 2005 Sample, I have
> written following code:
> [SqlTrigger(Name = "TestForInsert", Target = "[MyUser].[TestTable]",
> Event = "FOR INSERT")]
> ..// c sharp code
> when compiling the source code, VS 2005 pops a error which means that it c
an
> not find the object "[MyUser].[TestTable]"
> Using the Profiler, I found that when compiling, .Net runtime just add
> square brackets at the Target parameter value. In my code, the target is j
ust
> [[MyUser].[TestTable]], but [MyUser] should be a schema
> how should i do?|||But the table i wanna create trigger on is a member of schema which is not
owned by dbo, how should i do?
"Adam Warne" wrote:
> From the c# documentation I have, you are doing everything correctly. The
> code sample I have is
> [SqlTrigger(Name = @."EmailAudit", Target = "[dbo].[Users]", Event = "FOR
> INSERT, UPDATE, DELETE")]
> Perhaps you should attempt to create a trigger on an object owned by the d
bo
> and then just reference the table name (without the owner). I'd be
> interested in hearing how you progress with this.
> --
> Adam J Warne, MCDBA
>
> "DaemonLin" wrote:
>|||i wrote a sample , the server is SQL 2005 and database is AdventureWorks, th
e
trigger is on the table [HumanResources].[Department], the attribute is:
[Microsoft.SqlServer.Server.SqlTrigger(Name = @."TestTrigger", Target =
"[HumanResources].[Department]", Event = "FOR UPDATE, INSERT, DELETE")]
when i deploy it, IDE hints that
"Cannot find the object "[HumanResources].[Department]" because it does not
exist or you do not have permissions."
using SQL PROFILER and tracing it, the result sent to SQL Server is:
EXEC sp_executesql N'CREATE TRIGGER [TestTrigger] ON
[[HumanResources]].[Department]]] FOR UPDATE, INSERT, DELETE......
The schema HumanResources is owned by user dbo
so, how should i do?
"Adam Warne" wrote:

> From the c# documentation I have, you are doing everything correctly. The
> code sample I have is
> [SqlTrigger(Name = @."EmailAudit", Target = "[dbo].[Users]", Event = "FOR
> INSERT, UPDATE, DELETE")]
> Perhaps you should attempt to create a trigger on an object owned by the d
bo
> and then just reference the table name (without the owner). I'd be
> interested in hearing how you progress with this.
> --
> Adam J Warne, MCDBA

A Query-Transpose of Data

I am supplying you with Sample Data:-

Initial Classcode SampleSize Average
--- ---- ----------
ADK SSC 22 3.6800000000000002
ADK TSC 17 2.7599999999999998
ADK TSM 5 3.5499999999999998
ANB FCA 31 3.23
ANB FCB 50 3.0499999999999998
ANB FCC 30 3.0899999999999999
ANB SCA 35 3.02
ANB SCB 9 3.4300000000000002
ANB TCA 30 2.77
ANB TCB 6 1.8799999999999999
APG MCH 10 3.8300000000000001
APG TSCH 9 4.21
AUG FCC 30 3.5499999999999998
AUG SCA 28 2.7800000000000002
AUG SCB 29 3.4300000000000002
AUG SCC 30 2.8999999999999999
AUG TCA 30 2.8599999999999999
AUG TCB 29 2.1200000000000001
AVK TSP 12 3.6200000000000001
BKK FS 32 2.52
BKK TSM 5 3.3799999999999999
BSK SSP 28 3.1200000000000001
BSK TSP 12 3.0600000000000001
--- ---- ----------

These are the averages of teachers grouped by initial.
Maximum 7 averages are related to each teacher.
Ignore the column SampleSize.
Using this output is it possible to get output like this:

Initial Class1 Avg1 Class2 Avg2 Class3 Avg3 Class4 Avg4..
ADKTSA 1.4 TSB 2.5 TSC 4.5 SSC 5.0..
ANB SSA 1.4 SSB 2.5 NULL NULL NULL NULL..
APG...................
AVK...............
BKK...............
BSK................

Since the maximum class nos are 7, those having less than classes will

contain NULL in the class and average field.

Is it possible to carry out this in single query?

-SameerSameer (sameer75@.gmail.com) writes:
> These are the averages of teachers grouped by initial.
> Maximum 7 averages are related to each teacher.
> Ignore the column SampleSize.
> Using this output is it possible to get output like this:
> Initial Class1 Avg1 Class2 Avg2 Class3 Avg3 Class4 Avg4..
> ADK TSA 1.4 TSB 2.5 TSC 4.5 SSC 5.0..
> ANB SSA 1.4 SSB 2.5 NULL NULL NULL NULL..
> APG ...................
> AVK ...............
> BKK ...............
> BSK ................

The averages in the output does not seem to agree with the averages
in the result. But there is no indication of any calculation.

> Since the maximum class nos are 7, those having less than classes will
> contain NULL in the class and average field.
> Is it possible to carry out this in single query?

Single query may be possible, but I leave that to the purists. I would
use a temp table:

INSERT #temp (initial, class, average, average)
SELECT initial, class average, (SELECT COUNT(*)
FROM tbl b
WHERE a.class <= b.class)
FROM tbl

Then it a seven-way self-join:

SELECT a.initial, a.class, a.avg, b.class, b.avg, ...
FROM #temp a
LEFT JOIN #temp b ON a.initial = b.initial
AND b.classno = 2
LEFT JOIN #temp c ON a.initial = c.initial
AND c.classno = 2
...
WHERE a.classno = 1

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

A problem with Adventure Works DW sample

Hi All,

I have downloaded and installed AdventureWorksDBCI.msi
but there is not instawdb.sql scrip. When I try to run a sample
it says:
Error 1 Database 'Adventure Works DW' :

I have checked in MSSQL Management Studio but the Adventure Works
does not exist there.

Which steps I need to do to install the Adventure Works DW'.

Thanks

Sergiy

Sergiy,

If I recall, after running the AdventureWorksDBCI.msi, you need to ATTACH the database. It 'should' be in the default datafile location.