Field Names

I want to build a report that from an OLAP data source that uses a named set
that is a rolling 12 month view. The problem is I don't like the field names
that are displayed. For Example,
'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
All I want is August 2003. I know I can edit the field name, but what
happens when the fields change due to the nature of the rolling view? Any
ideas?
--
Michael Hardy
ETL Developer
Visit our web pages:
www.atlantis.com
www.oceanclub.com
www.oneandonlyresorts.com
www.kerzner.comOpuspocus wrote:
> I want to build a report that from an OLAP data source that uses a named set
> that is a rolling 12 month view. The problem is I don't like the field names
> that are displayed. For Example,
> 'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
> All I want is August 2003. I know I can edit the field name, but what
> happens when the fields change due to the nature of the rolling view? Any
> ideas?
We uses this technique, coudl be more out there...
WITH
Member [Measures].[TimeMemberUniqueName] as
'[Time].currentmember.UniqueName'
Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
SET [SelectedPeriod] AS '[Time].[All Time].[2003].[Q4] : [Time].[All
Time].[2003].[Q4].Lag(3)'
SELECT
{ [Measures].[TimeDisplayName], [Measures].[DaMeasure] } ON AXIS(0)
{ [SelectedPeriod]} on 1
FROM XX_YY
In the dynamic version we get the "lag" and actual date from parameters
and code functions. We then get a static field_name
"Measures_TimeDisplayName" to be used in the report.
Regards
// Jonas Montonen|||I'm trying to work out the syntax. I get a 'Query failed: Syntax error in
axis definition' for the following MDX. Can't figure out why.
WITH
Member [Measures].[TimeMemberUniqueName] as
'[ArrivalDate].currentmember.UniqueName',
Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
SET [SelectedPeriod] AS '[ArrivalDate].[All ArrivalDate].[2003].[3] :
[ArrivalDate].[All ArrivalDate].[2003].[3].Lag(3)'
SELECT
{ [Measures].[TimeDisplayName], [Measures].[RsrvtnCount] } ON Axis(0)
{ [SelectedPeriod]} on ROWS
FROM [RsrvtnSummary]
"Jonas Montonen" wrote:
> Opuspocus wrote:
> > I want to build a report that from an OLAP data source that uses a named set
> > that is a rolling 12 month view. The problem is I don't like the field names
> > that are displayed. For Example,
> >
> > 'ArrivalDate_All_ArrivalDate_2003_Quarter_3_2003_August_2003'.
> > All I want is August 2003. I know I can edit the field name, but what
> > happens when the fields change due to the nature of the rolling view? Any
> > ideas?
> We uses this technique, coudl be more out there...
> WITH
> Member [Measures].[TimeMemberUniqueName] as
> '[Time].currentmember.UniqueName'
> Member [Measures].[TimeDisplayName] as 'Time.Currentmember.Name'
> SET [SelectedPeriod] AS '[Time].[All Time].[2003].[Q4] : [Time].[All
> Time].[2003].[Q4].Lag(3)'
> SELECT
> { [Measures].[TimeDisplayName], [Measures].[DaMeasure] } ON AXIS(0)
> { [SelectedPeriod]} on 1
> FROM XX_YY
> In the dynamic version we get the "lag" and actual date from parameters
> and code functions. We then get a static field_name
> "Measures_TimeDisplayName" to be used in the report.
> Regards
> // Jonas Montonen
>sql

Fiel Level Security

I have a form in an Access front-end that is fed by a SQL View. Update
security on the View is done on the SQL side.
The Question is how can I restrict (blank or hide) fields on the form based
on the users security access?
How do I read if the user has access to a specific field?
Any help will be greatly appreciated.Berny,
Check the Permissions() function in SQL Books OnLine, I think this is what
you need.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Berny" <BlancoB at msn Dot com> wrote in message
news:#T49zIv3DHA.3656@.TK2MSFTNGP11.phx.gbl...

quote:

---

> I have a form in an Access front-end that is fed by a SQL View. Update
> security on the View is done on the SQL side.
> The Question is how can I restrict (blank or hide) fields on the form

---

based

quote:

---

> on the users security access?
> How do I read if the user has access to a specific field?
> Any help will be greatly appreciated.
>

---

sql

Federated server table partition

In a federated server, you create the same table on more than one server and
use a view to link them all together. I have two questions:
1) Can modulo division be used to determine which server a row goes on?
My reason is that modulo division will give the most even
distribution of the data.
2) Can I put a 2nd table on the same server, but in a different filegroup?
The DDL will get complicated and I want to start with 6 table
fragments on 2 servers, eventually moving to 3, then 6 servers, without
having to make wholesale changes to the DDL.
Oops: SQL Server 2000
"JayKon" wrote:

> In a federated server, you create the same table on more than one server and
> use a view to link them all together. I have two questions:
> 1) Can modulo division be used to determine which server a row goes on?
> My reason is that modulo division will give the most even
> distribution of the data.
> 2) Can I put a 2nd table on the same server, but in a different filegroup?
> The DDL will get complicated and I want to start with 6 table
> fragments on 2 servers, eventually moving to 3, then 6 servers, without
> having to make wholesale changes to the DDL.
|||Answers inline.
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
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:A4D5D4C5-75C5-4B19-9BD9-E9B318787460@.microsoft.com...
> In a federated server, you create the same table on more than one server
> and
> use a view to link them all together. I have two questions:
> 1) Can modulo division be used to determine which server a row goes on?
> My reason is that modulo division will give the most even
> distribution of the data.
Yes, but then the query optimizer will not be able to take advantage of
Distributed Partitioned View, or even the indexes on table partitioning.
> 2) Can I put a 2nd table on the same server, but in a different filegroup?
> The DDL will get complicated and I want to start with 6 table
> fragments on 2 servers, eventually moving to 3, then 6 servers, without
> having to make wholesale changes to the DDL.
Absolutely! Some people run all the member tables on DPVs on the same
database. However you are not getting scale out performance this way. If you
have large data sets being returned you should use different servers, if the
results sets are small the network hop becomes the bottleneck and you should
move all tables locally.
|||Questions inline.

> Yes, but then the query optimizer will not be able to take advantage of
> Distributed Partitioned View, or even the indexes on table partitioning.
Even if the rows are accessed my the IDENTITY column (which is what I want
to do the modulo on)? I'm thinking of a title/item structure, or a customer
structure.
The lookup of the number would either be from a search engine, or a seperate
lookup table.

> Absolutely! Some people run all the member tables on DPVs on the same
> database. However you are not getting scale out performance this way. If you
> have large data sets being returned you should use different servers, if the
> results sets are small the network hop becomes the bottleneck and you should
> move all tables locally.
This is understood, however, I was thinking about this as more a way to keep
DDL changes to a minimum when expanding the number of servers in the cluster,
while at the same time reducing hard drive and I/O controller (and posibily
buss) activity.
Also, I intended seperate NIC's for the DB Servers to talk to each other.
One other question.
I saw a reference that said only the index for the key on a DPV was allowed.
Does that mean that if I wand another index, I need to build a seperate data
structure that will contain the PK for the table?
|||If your queries are by identity value, yes it should.
I would advise you to test this out with a representative load to see if it
will work.
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
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:70BF14C6-7CC8-4098-BC5D-53B9A79EE0D1@.microsoft.com...
> Questions inline.
>
> Even if the rows are accessed my the IDENTITY column (which is what I want
> to do the modulo on)? I'm thinking of a title/item structure, or a
> customer
> structure.
> The lookup of the number would either be from a search engine, or a
> seperate
> lookup table.
>
> This is understood, however, I was thinking about this as more a way to
> keep
> DDL changes to a minimum when expanding the number of servers in the
> cluster,
> while at the same time reducing hard drive and I/O controller (and
> posibily
> buss) activity.
> Also, I intended seperate NIC's for the DB Servers to talk to each other.
> One other question.
> I saw a reference that said only the index for the key on a DPV was
> allowed.
> Does that mean that if I wand another index, I need to build a seperate
> data
> structure that will contain the PK for the table?
sql

fault with configure the select stament

Goodday all

I,m having a problem with something that worked well just recently. maybe I changed a setting by mistake.

I have a Database grid view on my page. When I configure my data sourse and get to "configure the select statment " on the wizard and press advanced to open the wizard with two check boxes " generate INSERT, UPDATE etc and the other check box "Use optimistic concurrency " this input box of the wizard is greyed out (non responsive)

Does anyone know how to rectify this?

Regards

Rob

Insufficient information to be sure. Did you indicate to generate insert, update, or delete statements?

The optimistic concurrency does not mean anything unless you created at least one of those statements so it is likely disabled.

FastLoad OLEDB Destination with Oralce

Hi,

why Table or View – fast load option in Data Access mode is not listed when we connect Oracle Database in the OLEDB Destination.

Thanks

Jegan

When you switch data sources using an OLE DB destination, you are most probably using a different kind and version of the OLE DB provider. I'm guessing that in your case, you are using Microsoft's Oracle OLE DB provider? that `-fast load` option is a feature of the underlying OLE DB Provider, which in your case, I'm guessing is Microsoft's OLE DB provider (SQL Native Client). And that is probably not a feature of the underlying OLE DB Provider for Oracle that you are using, and thus, do not show-up as an option.

|||Thanks Deniz.

Fastest way

I've got a complex view (lots of inner and outer joins from several tables) in my SQL Server 2000 Database.

In my VB program, I've connected a grid to this view via an Ado Control
but the performances are not so great (even bad).

How can I boost the execution of this view ?

ThxI would look at those joins and see if there is any non-indexed scan first. You can also run the wizard to see if there is any room for improvement. Good luck!|||Hi again Joe from Texas (is it hot there at this moment ?)

I've put indexes on all the needed columns
but I've seen somewhere that we can create indexes on views

would that help ?|||If you look up "Creating an Indexed View" in Books Online, you'll see that there are a lot of restrictions on what types of views can be indexed. In particular, you can't index your view if it contains any of these elements:
Subqueries
Outer joins
Self joins
DISTINCT keyword
SUM functions that reference nullable expressions
.
.
. etc...

If your view is as complex as you say it is, there is a good chance that it cannot be indexed. Also, when you index a view SQL Server creates a permanent virtual table with the results of that view, and which is then updated any time any of the values in the underlying tables are updates. This can slow down other processing.

I think you would be better off rewriting your statement as a stored procedure rather than as a view. Stored procedures are more efficient than views.

Also, post your code and we may be able to find ways to make it more efficient.

blindman

Fan Trap or Multiple one-to-many joins

I'm having a problem creating a view on some data that involves two one-to-many joins like this:

tbl1 m--> tbl2 <--n tbl3

and I need to create a view on the data from all three tables without duplicates from tbl1 and tbl3.

The problem is that tbl1 and tbl3 are not related at all, except that they are linked by data in tbl2.

Think of it like this: you have a project, which can have multiple consultants, and multiple stakeholders, and the data must be returned in such a way that each consultant and each stakeholder appears once in the output (the project name must appear multiple times of course). The issue is that the following two datasets are logically distinct but semantically identical:

proj A, consultant A, stakeholder A
proj A, consultant B, stakeholder B
--
proj A, consultant B, stakeholder A
proj A, consultant A, stakeholder B

but what I'm aiming for is:

proj A, consultant A, stakeholder A
proj A, consultant B, stakeholder B

I've heard this described as a fan trap, but usual solutions involve reorganzing the data so that tbl3 joins tbl1 joins tbl2, but in my case there is no link there

Incidently, the intended platform for this is DB2 and/or SQL Server. Any help appreciated, thanks.your examples are not very clear

you start out by diagramming tbl1, tbl2, tbl3, and then immediately switch to projects, consultants, and shareholders, without showing the actual data in these tables, just some apparent cross join query results

you might wish to show a more comprehensive example, because so far, it's hard to understand what you're asking|||I've heard this described as many things, most of which aren't polite to repeat. ;)

You're trying to figure out how to build a join to show the relationship between consultants and shareholders, to produce a one-to-one join between two tables that explicitly have no relationship. If you figure out how to make this happen, please let me know... I'm sure that I'll be fascinated by the explanation!

You've got a clear relationship between project and shareholder, and another relationship between project and consultant. You don't explicitly state that there must be a shareholder or a consultant for any given project, and at some point in the project's life I can guarantee that there will not be one of either. You don't explicitly state that there must be one shareholder for every consultant. If you think about these requirements, unless at least one of these requirements is false, you can't get the output you want... There ain't no way to git there from here.

You need to rethink either the specifications or the requirements. Something has got to give because using the definitions that you've given, the present problem can't be solved.

-PatP|||ah, so that's what that is -- i had never heard that terminology before

this pdf is a pretty good explanation --
http://support.businessobjects.com/documentation/installation_resources/5i/tips_and_tricks/pdf/universe_design/ut001.pdf

i would solve this problem with a UNION query --

proj A, consultant A
proj A, consultant B
proj A, stakeholder A
proj A, stakeholder B

failure to publish an indexed view logbased publication

i am trying to publish an indexed view logbased publication but I'm getting "must be a schema-bound view with at least a clustered index to be published as a log-based indexed view article."

The table has clustered index and the view is schema-bound.

this is the script for schemabinding

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERVIEW [nth].[musictracks1] withschemabinding

AS

SELECT autoid,idtrack,songname,singer,writer,composer,center

FROM nth.musictracks

GO

SETANSI_NULLSOFF

GO

SETQUOTED_IDENTIFIEROFF

GO

and this is for making the publication an indexed view logbased publication

execsp_addarticle

@.publication = N'lb_publication',

@.article = N'musictracks1',

@.source_owner = N'nth',

@.source_object = N'musictracks1',

@.type = N'indexed view logbased',

@.description =null,

@.creation_script =null,

@.pre_creation_cmd = N'none',

@.schema_option = 0x0000000008000001,

/* table name doesn’t have to be the same as view name */

@.destination_table = N'musictracks1',

@.destination_owner = N'nth'

GO

Any idea what is wrong?

Thanks,

Salah

If you want to use logbased for an indexed view, then you have to create a unique clustered index on the indexed view as the error msg indicates. if you just want to replicate the view schema, then you can used indexed view schema only.|||

can you tell how can I create a unique clustered index on the indexed view ?

Thanks

|||

you create an index on it like you do with any other table, for example:

create unqiue clustered index [uc_index1] on musictracks1 (autoid)