Data Dictionary for Dimensions

Is there any easy way to create a dictionary or lookup function based on
Dimensions in a cube? We only have 4 cubes so far but the big one has some
50 dimensions and sometimes finding the attribute you want to report on is
consuming.
Would like to for example to search somewhere for where the attribute
"model" is, from example below and have it return the cube and dimension
it's in:
Something like:
Cube XYZ
Dimension: Autos
Attributes:
Make
Model
Year Released
..Hello Joe,
I am not sure what version of Analysis services you are using so I am
going to assume that you are using AS 2005.
With AS 2005 you have the ability to create perspectives. These can be
used to group dimensions and measures into common areas for reporting.
Perspectives can be defined to the attribute and measure level. You can
also create a linked cube that can maintain the perspectives across all
your cubes.
You could also consider using report builder as a front end. It
provides a good ad hoc query interface, but is not a replacement for
pivot tables in Excel. Report Builder creates a semantic layer on top
of the cube that allows users to search for attributes and entities
when creating a query.
To use this you will need Reporting services 2005 installed. Check out
my blog post on creating a report model against AS 2005 cubes.
http://bi-on-sql-server.blogspot.co...er-and-udm.html
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||underprocessable|||Hello Joe,
I have seen this error before. It was caused by the SQL server being
renamed Check the following:
1. Use the full server name instead of local alias
2. Check that the server has not been renamed
3. Try to connect to the RS server through Management Studio
Are you only getting this error in BIDS. Have you tried accessing
Reports in Report Manager?
Myles|||I got it working well on my local box - so it must be related to the server
I'm deploying too...maybe security issues...
<Myles.Matheson@.gmail.com> wrote in message
news:1156928164.274497.324030@.i42g2000cwa.googlegroups.com...
> Hello Joe,
> I have seen this error before. It was caused by the SQL server being
> renamed Check the following:
> 1. Use the full server name instead of local alias
> 2. Check that the server has not been renamed
> 3. Try to connect to the RS server through Management Studio
> Are you only getting this error in BIDS. Have you tried accessing
> Reports in Report Manager?
> Myles
>

Data by ID and Getdate function

I am trying to return data by date but I also need to return data by date "and" by ID. I've queried various select statements but always get only one days worth of data and the date is repeated many times. I only want to return data for today only but with ID as the other variable.

For example I want only the shows for today by date and by ID. ID of course being the key in the DB. Below I will show you a code block followed by a text version of what it looks like in the browser when tested.

Code Snippet

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:\webservice\Kuow\Kuow.UDL"
set recProgram = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT *, Air_Date AS Expr1 FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101))"

'strSQL = "SELECT *, Air_Date AS Expr1, Unit AS Expr2 FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) AND (Unit = 'TB')"
recProgram.Open strSQL,con
%>

<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>

Output:
ID Unit Subject Title Long_Summary Body_Text Related_Events Air_Date AudioLink

(Reading across the screen from left to right)
1234 WK1 Subject Title a summary some body text Event text 4/13/2007 wkdy20070413-a.rm

Here is the URL used for testing:
http://Test Server IP/test/defaultweekday2.asp

I need to be able to append to this URL an ID number so that not only do I get content by Air_Date but also by ID.

http://Test Server IP/test/defaultweekday2.asp?ID=1234

How to do this?

You might want to look in Books Online about the usage of GROUP BY.

Code Snippet

GROUP BY convert( varchar(10), getdate(), 101 )) , Unit

|||Ok I will do that but for now is your example a working example? If not what other examples could I try?|||

It 'should' work IF you change the SELECT to

"SELECT *, Air_Date = convert( varchar(10), getdate(), 101 )), Unit FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) GROUP BY convert( varchar(10), getdate(), 101 )) , Unit"

|||I tested your suggested by replacing the second half of my select query with your code starting with WHERE...

When I tested it I got this

Microsoft OLE DB Provider for SQL Server error '80040e14'

GROUP BY expressions must refer to column names that appear in the select list.

/test/defaultweekday2.asp, line 24

Code Snippet

strSQL = "SELECT *, Air_Date AS Expr1 FROM T_Programs GROUP BY convert( varchar(10), getdate(), 101 )) , Unit"

|||Ok I see what your getting at however I just tested it in my browser and got this:

Microsoft OLE DB Provider for SQL Server error '80040e14'

Line 1: Incorrect syntax near ')'.

/test/defaultweekday2.asp, line 22

Code Snippet

strSQL = "SELECT *, Air_Date = convert( varchar(10), getdate(), 101 )), Unit FROM T_Programs WHERE (Air_Date = CONVERT(varchar(10), GETDATE(), 101)) GROUP BY convert( varchar(10), getdate(), 101 )) , Unit"

I counted the ( ) to make sure there was the correct number of left and rights ones. Does it not like the end of the line or what?|||

As the error message indicates (and you might want to read up on using GROUP BY), any column in the GROUP BY MUST also be in the SELECT list.

Your GROUP BY includes Unit, and the SELECT list does not.

I think that the query I posted earlier 'should' work. But this alteration will not.

GROUP BY requires ALL columns in the SELECT list to EITHER be in the GROUP BY clause, or be aggregations. Trying to select all columns with a [SELECT * ] will not work.

I suggest that you start out small, perhaps with the query that I posted earlier, try to understand how GROUP BY works, and then expand your query a small piece at a time.

|||Arnie -

Sounds good. I'm reading up on it now as I learn and thank you for your most recent reply. Your thinking is helping me think. I'm sure it's obvious I'm new to SQL. I'll be glad when I get good enough that I can provide the people I work for and with the answers they seek in a relatively quick turnaround.

There is nothing worse then having work that is over your head and your figuring out how to do it while your solving real world business problems. Can be stressful. But hey it's one way to ensure I'll remember it.

Dash in search string

Is it posible that dashes "-" are considering as punctuation when using
CONTAINSTABLE function? It's the behavior I see but I cannot find any text in
BOL that specify this.
This gives lots of rows:
SELECT k.KEY, * FROM ContainsTable(Request,*,N'ISSI-2007-0')
This gives no of rows:
SELECT k.KEY, * FROM ContainsTable(Request,*,N'ISSI-2007-00')
But I know that from the first resultset, full-text indexed column include
data that start with 'ISSI-2007-00'...
I would like that someone can confirm this behavior and let me know where I
can find ducomentation on that "-" behavior!
David, MCDBA
For the most part it is throw away. The rules vary from language to language
though. Basically it is broken as ISSI and 2007 and 0 (for ISSI-2007-0), and
ISSI 2007 and 00 for the second one.
I get different results from you.
create table david(pk int identity not null constraint davidpk primary key ,
charcol varchar(20))
GO
create fulltext index on david(charcol) key index davidpk
GO
insert into david(charcol) values('ISSI-2007-0')
insert into david(charcol) values('ISSI-2007-00')
GO
select * from david where contains(*,'ISSI-2007-0')--both found
GO
select * from david where contains(*,'ISSI-2007-00')--only the second is
found
GO
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:F80F130B-78F1-4942-89AA-589E6419DCB7@.microsoft.com...
> Is it posible that dashes "-" are considering as punctuation when using
> CONTAINSTABLE function? It's the behavior I see but I cannot find any text
> in
> BOL that specify this.
> This gives lots of rows:
> SELECT k.KEY, * FROM ContainsTable(Request,*,N'ISSI-2007-0')
> This gives no of rows:
> SELECT k.KEY, * FROM ContainsTable(Request,*,N'ISSI-2007-00')
> But I know that from the first resultset, full-text indexed column include
> data that start with 'ISSI-2007-00'...
> I would like that someone can confirm this behavior and let me know where
> I
> can find ducomentation on that "-" behavior!
> David, MCDBA