another Freetexttable query problem

Hi All: Last month, I was looking for way to query 3 FT tables
simultaneously.
I found that a UNION with 3 separate queries worked, but someone pointed out
that duplicates would occur (and they did).
It was suggested that I use a query like:
select <columns>
from table1
inner join freetexttable(table1,*, @.srchstring) ft1
on ft1.key = table1.<key column>
inner join table2
on table2.<common key> = table1.<common key>
inner join freetexttable(table2,*, @.srchstring) ft2
on ft2.key = table2.<key column>
I did get that to work, but the problem is the query won't return any
matches if the searchstring is not found in both FT tables.
Going back to my original goal, I have 3 tables:
products
manufacturers
skus
And these tables are all in the FT cat.
I want to be able to search for a term like "blue" or "5180-1" (a sku) and
return a match from any table in the FT cat.
Is there any way to do it with the nested join query above? Or is there
another way to do it?
Before I started using the freetexttable query, I was using some fugly code
like:
SELECT <columns> FROM products WHERE <columns> LIKE '%<searchstr>%'
<if no results then>
SELECT <columns> FROM manufacturers WHERE <columns> LIKE '%<searchstr>%'
...do I need to do something like that, but instead concatenate the results
from each query into a temp table?
Thanks for any advice!
Hello geek-y-guy,
If you use a left join your query should work
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi All: Last month, I was looking for way to query 3 FT tables
> simultaneously.
> I found that a UNION with 3 separate queries worked, but someone
> pointed out that duplicates would occur (and they did).
> It was suggested that I use a query like:
> select <columns>
> from table1
> inner join freetexttable(table1,*, @.srchstring) ft1
> on ft1.key = table1.<key column>
> inner join table2
> on table2.<common key> = table1.<common key>
> inner join freetexttable(table2,*,
> @.srchstring) ft2
> on ft2.key = table2.<key
> column>
> I did get that to work, but the problem is the query won't return any
> matches if the searchstring is not found in both FT tables.
> Going back to my original goal, I have 3 tables:
> products
> manufacturers
> skus
> And these tables are all in the FT cat.
> I want to be able to search for a term like "blue" or "5180-1" (a sku)
> and return a match from any table in the FT cat.
> Is there any way to do it with the nested join query above? Or is
> there another way to do it?
> Before I started using the freetexttable query, I was using some fugly
> code like:
> SELECT <columns> FROM products WHERE <columns> LIKE
> '%<searchstr>%'
> <if no results then>
> SELECT <columns> FROM manufacturers WHERE <columns> LIKE
> '%<searchstr>%'
> ...do I need to do something like that, but instead concatenate the
> results from each query into a temp table?
> Thanks for any advice!
>
|||Thanks Simon, but are you saying a left join for every join in the query?
for the 3 tables there would be 6 joins in total.
"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a4eb038c9236201101b77@.msnews.microsoft .com...
> Hello geek-y-guy,
> If you use a left join your query should work
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
>
>

Analysis services connection

Hi!!
1. I am working with Analysis services 2005 and I would want to know
if, Is it recommendable to separate the DB server from of server AS?,
Since when I consult the cubes diminishes the performance the server of
database.
2. How I can to create news administrators OLAP in SQL 2005?
Is there one book what you can recommend on how connecting to me of
remote form?
Thanks,
Cecilia
Chile
1. Yes, you can run SQL and AS on separate machines, just be aware that
each machine would require its own SQL license.
2. To create add a user to the administration group at the server level
for AS you need to open up SQL Server Management Studio, connect to your
server, right click and select "properties" and then go to the security
settings and add the user to the list there.
Regards
Darren Gosbell [MVP]
Blog: http://geekswithblogs.net/darrengosbell
*** Sent via Developersdex http://www.codecomments.com ***
|||The connection of server work, thanks Darren.
Now I have other doubts:
1. if I have SQL server and AS on separate machines and I execute a MDX
query for a rolap cube, consume this query too many resources on the BD
server?
2. it really improves the performance of my server, if i separate it in
the 2 mentioned machines?
3. What percent of the load of a query takes the BD machine, (aprox)?
Thanks,
Cecilia

Analysis services connection

Hi!!
1. I am working with Analysis services 2005 and I would want to know
if, Is it recommendable to separate the DB server from of server AS?,
Since when I consult the cubes diminishes the performance the server of
database.
2. How I can to create news administrators OLAP in SQL 2005?
Is there one book what you can recommend on how connecting to me of
remote form?
Thanks,
Cecilia
Chile1. Yes, you can run SQL and AS on separate machines, just be aware that
each machine would require its own SQL license.
2. To create add a user to the administration group at the server level
for AS you need to open up SQL Server Management Studio, connect to your
server, right click and select "properties" and then go to the security
settings and add the user to the list there.
Regards
Darren Gosbell [MVP]
Blog: http://geekswithblogs.net/darrengosbell
*** Sent via Developersdex http://www.codecomments.com ***|||The connection of server work, thanks Darren.
Now I have other doubts:
1. if I have SQL server and AS on separate machines and I execute a MDX
query for a rolap cube, consume this query too many resources on the BD
server?
2. it really improves the performance of my server, if i separate it in
the 2 mentioned machines?
3. What percent of the load of a query takes the BD machine, (aprox)?
Thanks,
Cecilia|||In article <1163085164.731268.160000@.e3g2000cwe.googlegroups.com>,
Hoody.cu@.gmail.com says...
> The connection of server work, thanks Darren.
> Now I have other doubts:
> 1. if I have SQL server and AS on separate machines and I execute a MDX
> query for a rolap cube, consume this query too many resources on the BD
> server?
>
Sorry, but ROLAP cubes are the one exception. In the case of ROLAP, the
AS server is really just a thin semantic layer that translates MDX into
SQL and runs some pretty large queries against the DB server

> 2. it really improves the performance of my server, if i separate it in
> the 2 mentioned machines?
>
This really only makes a difference when AS is using MOLAP storage as
then it has its own copy of the data and may not need to query the DB
server at all after it has gone through it's processing stage

> 3. What percent of the load of a query takes the BD machine, (aprox)?
>
For ROLAP probably 99%
For MOLAP 0%
(after the data has been read once during the processing stage)
I assume that you are using ROLAP because you need "real-time" data. I
don't know what your exact requirements are, or if you have looked into
any of this before, but AS has a number of ways of implementing "near
real-time" systems. So that if it works with your data and your users
can accept some degree of "lag" in the update of their reports you
should be able to get an increase in the performance of your MDX queries
and reduce the load on your DB server.
If you are interested in this there are a couple of different features
you could read up on.
1) proactive caching - can automatically keep your MOLAP storage up to
date, based either on notifications or polling the source system.
2) You can use incremental processing and schedule a job to run on a
periodic basis to refresh the MOLAP store.
These options do not fit all circumstances and sometimes you may need to
use ROLAP storage, but if you can avoid ROLAP you will probably have a
much better experience.
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

Analysis Services Calculations

Is it possible to group/ arrange Analysis Services Calculations in cubes into separate Folders? In other words similart to measures which can be grouped in to folders I would want to know if we can group these calculated meassures into folders.

Thanks

In the calculations tab you can see a very small properties button, called calculation properties, that you can click on.

This is in BI-Dev Studio.

HTH

Thomas Ivarsson

|||

Great, thanks..

Now how can I control the security of these calculated members. meaning, I do not want the calculated field to be available to a pariculat role?

I can control it at the Measure level that makes this calculated field. in other words I do not want one of the calculated field not visible to a particular Role Member..

|||

Now how can I control the security of these calculated members. meaning, I do not want the calculated field to be available to a pariculat role?

This is not possible in AS2005.

|||

Thanks Mosha, Is there any other work around with which I can make the caluclated fields available/ visible only to a set of users.

I have a calculated field of $'s which I do not want to be visible to all the memebers. It should be visible only to Managers who are members of Managers Role.

|||You should define it as a real member of the dimension then. Since the fact table won't have keys related to that member - after processing it will be empty - so you are not paying anything in the space. Then inside MDX Script you can overwrite it with any MDX expression you want, just like you do with the calculated members. But now since it is a real member, you can secure it in a role.|||

Another option is to add Cell Data security to the measure. The following article shows details of how to add security to only allow users to use a specific measure.

http://msdn2.microsoft.com/en-us/library/aa933600(SQL.80).aspx

This works for calculated members as well.

In the Cell Data tab of the Role editor, tick 'Enable read permissions', and add a statement referring to the measures the user IS allowed to use. This will then disallow access to any other measures.

e.g.

If we have 3 measures:

MemberCount
SalesCount
MemberPay

And want to diallow access to the MemberPay measure for a specific role, we could use the following statement in the Cell Data page:

Measures.CurrentMember.Name = "MemberCount" or Measures.CurrentMember.Name = "SalesCount"