I am using SQL server 2000. I am trying to create the simplist of queries in my mind and I keep getting duplications and its driving me nuts. I have two tables. (MASTER, SLAVE we will call them) All I am trying to do is "show me data within this date range" and it gives me results but duplicates. Help. PLEASE do not refer me to http://www.dbforums.com/t1201578.html because that is unclear to me! Here is my code:
SELECT PLNAME, PFNAME, PMNAME, DOB, ACCOUNT, SERVICE DATE
FROM MASTER, SLAVE
WHERE TECH='TECHNAME' AND(SERVICE DATE>='20060101' AND SERVICE DATE<='20061230')
ORDER BY SERVICE DATE
I am a noob, but I can't see this being complex.SELECT DISTINCT ...
might solve your problem.
Oh, yes ... this query has two tables. Where did you join them? If you do not do that, you'll get cartesian product of all values. Therefore, joining tables properly might be another possible solution. Such as
...
WHERE slave.some_column = master.some_column
AND ...|||Well, I tried the distinct and it made my outcome worse. I do not have a join to the tables because I am not clear how to do it yet.|||Well, you'd better figure that out :)
I have no idea how your tables look like (it is unclear which table contains which column you provided in the sample query), but seeing "DOB" (Date Of Birth?), you might have something like PERSON_ID in both tables which *might* be the column you are looking for.|||Agree with Littlefoot. You must join the tables by creating a relationship between the two. In order to do this, each of the tables must contain a common KEY upon which the tables are to be linked.
To create a relationship between the tables, click on the relationship icon on the toolbar, follow the steps that ultimately will permit you to make a connection between the common KEY, a field in the database, usually holding unique data for each record, eg. SS#.|||The join syntax is pretty straightforward
lets assume that TECH is the common column
SELECT PLNAME, PFNAME, PMNAME, DOB, ACCOUNT, SERVICE DATE
FROM MASTER
join SLAVE on Slave.TECH=master.tech
WHERE MASTER.TECH='TECHNAME' AND(SERVICE DATE>='20060101' AND SERVICE DATE<=#20061230#)
ORDER BY SERVICE DATE
incidentally I think you will have a problem with spaces in column names
I'd suggest developing or using a naming convention.
Personally I've always used something that distinguishs tables form columnsm used captialisation to make things easier to read, but there as many naming conventions as politicians want to grab all the cash in your pocket. Find one htat works for you, or your organistion and stick with it.
Select PLName, PFName, PMName, DoB, Acct, SrvDate
FROM DTMaster
join DTSlave on Slave.Tech=DTMaster.Tech
WHERE DTMaster.Tech='TECHNAME' AND(SERVICE DATE>='20060101' AND SrvDate<='20061230')
ORDER BY SrvDate
HTH|||I am using SQL server 2000. I am trying to create the simplist of queries in my mind and I keep getting duplications and its driving me nuts. I have two tables. (MASTER, SLAVE we will call them) All I am trying to do is "show me data within this date range" and it gives me results but duplicates. Help. PLEASE do not refer me to http://www.dbforums.com/t1201578.html because that is unclear to me! Here is my code:
SELECT PLNAME, PFNAME, PMNAME, DOB, ACCOUNT, SERVICE DATE
FROM MASTER, SLAVE
WHERE TECH='TECHNAME' AND(SERVICE DATE>='20060101' AND SERVICE DATE<='20061230')
ORDER BY SERVICE DATE
I am a noob, but I can't see this being complex.
What the others said, basically!
What columns correspond to what tables? Where is Tech? Is it on Master or Slave? The others are right; this syntax looks like it'll get you a cartesian product which isn't what you want. You want an inner join!
Also, if you're looking for a date range, maybe try using BETWEEN instead of 'SERVICE DATE>='20060101' AND SERVICE DATE<='20061230''
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Sunday, March 11, 2012
Another Newbie Question (about queries)
I'm moving things from Access to SQL Server. What do I do with queries? I'm
using Access 2000 to access Sql Server, and there doesn't seem to be
anything for queries.
using Access 2000 to access Sql Server, and there doesn't seem to be
anything for queries.
Does SQL Server have queries, or are they just done in the page code (to
access data on a website)?
I also have a trial version of Enterprise Manager, but I can't figure out
how to connect. If that would show queries.
Thanks
JAQueries would be either Views or Stored Procedures in your SQL database.
Normally Views are for select statement and the rest you would use Stored
Procedures.
When you open SQL Enterprise Manager you need to expand on the left
pane until you open your local server, databases and access your database.
SQL Query Analyzer is where you can execute SQL code for example
SELECT statements and see the result there.
> I'm moving things from Access to SQL Server. What do I do with queries?
I'm
> using Access 2000 to access Sql Server, and there doesn't seem to be
> anything for queries.
> Does SQL Server have queries, or are they just done in the page code (to
> access data on a website)?
> I also have a trial version of Enterprise Manager, but I can't figure out
> how to connect. If that would show queries.
Thursday, March 8, 2012
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
>
>
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
>
>
Monday, February 13, 2012
Analysis services query timeout from SSIS
Hi,
I've just developed a simple integration services package which picks up a list of MDX queries from a table then runs them against our Cube using a .NET OleDb connection and saves the results to a csv file.
Unfortunately the queries frequently timeout, despite the fact that the Timeout property on the Connection Manager is set to 100000 and the Connect Timeout property is set to 600.
I have some logging on the packages and this revels that the queries will often timeout after less than 60 seconds with the error below:
<<Query>> failed with the following error: "XML for Analysis parser: The XML for Analysis request timed out before it was completed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Looking at the properties of the connection manager, I can see that the ConnectionString which has been generated is:
Data Source=Server;Initial Catalog=CubeName;Provider=MSOLAP.3;Integrated Security=SSPI;Connect Timeout=600;Auto Synch Period=1000;Timeout=100000;
Subsequent re-running of the package will often succeed, I'm guessing this is because the Cube has cached the query result and is able to return it fast enough.
Is there an additional timeout property that I'm missing somewhere?
Your help is much appreciated!
-Stuart
Perhaps try ExternalCommandTimeout? (http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx)|||Chris Webb built a "cache warming" prototype using SSIS and I think he found a way around this issue, you can find the post on his blog about this at http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1062.entry|||
Hi,
Sorry for the delay in replying and thanks for your suggestions. It seems that Chris Webb's solution was to ignore the timeout, which was fine given that he was building a cache warmer and didn't actually need to get the results. Given that I did need the results I decided to try and build some retry logic into my SSIS package.
I had a very clear and simple idea in my mind of how this would work. I would simply add an additional flow connector to the Execute SQL task with a Failure & Expression constraint where @.RetryCount < @.MaxRetries, then I would flow into a script task to increment @.RetryCount then back into the same Execute SQL task. Unfortunately though SSIS will not allow this as this would result in looping dependency.
The somewhat contrived solution I have now come up with is to extract my MDX Query list into a .NET Dataset pointed to by an SSIS Object variable, onto which I add an additional column to keep track of the number of retires. I then loop over the dataset and in the event of a query failing, I copy the row in the dataset and add it to the bottom, incrementing RetryCount.
It seems like a crazy solution to the problem of a query timing out. Is anyone at Microsoft aware that the Execute SQL task will timeout after 30 seconds when using an ADO .NET OleDb Connection with the Analysis Services provider, regardless of what you set the timeouts to be?
-Stuart
Sunday, February 12, 2012
Analysis Services Help
I'm looking for some modest help in developing some MDX queries in Analysis Services against a datacube I've built.
Would anyone be willing to answer some specific e-mail on this? I am willing to provide a small honorarium for some timely help here.Unless there are disclosure issues you may have the best help from posting your questions here (Most likely, if you contact those who are most helpful, many will not refuse whatever you might wish to donate via paypal, etc.)?|||Originally posted by DBA
Unless there are disclosure issues you may have the best help from posting your questions here (Most likely, if you contact those who are most helpful, many will not refuse whatever you might wish to donate via paypal, etc.)?
I'm not too concerned about disclosure issues. I was mostly concerned about the detail involved. Providing enough context for someone to help me is going to be somewhat involved, and I'm not sure exactly what questions I need to ask. I was hoping for some assistance that might be a bit more iterative than is really practical (and perhaps appropriate) with a public web-based forum.
Given the suggestion, I'll try and work up a summary and sub-scenario and see if anyone is interested in helping (and perhaps helping more).
Thanks!
Would anyone be willing to answer some specific e-mail on this? I am willing to provide a small honorarium for some timely help here.Unless there are disclosure issues you may have the best help from posting your questions here (Most likely, if you contact those who are most helpful, many will not refuse whatever you might wish to donate via paypal, etc.)?|||Originally posted by DBA
Unless there are disclosure issues you may have the best help from posting your questions here (Most likely, if you contact those who are most helpful, many will not refuse whatever you might wish to donate via paypal, etc.)?
I'm not too concerned about disclosure issues. I was mostly concerned about the detail involved. Providing enough context for someone to help me is going to be somewhat involved, and I'm not sure exactly what questions I need to ask. I was hoping for some assistance that might be a bit more iterative than is really practical (and perhaps appropriate) with a public web-based forum.
Given the suggestion, I'll try and work up a summary and sub-scenario and see if anyone is interested in helping (and perhaps helping more).
Thanks!
Analysis Services Data Access Auditing with msmdqlog.mdb
I’m starting to look at using a data warehouse and would like to have some
tracking of the queries ran against the data warehouse.
I’ve gone through several articles about the MS Access database msmdqlog.m
db
and I’m just looking for ways to implement the auditing and I have a few
questions.
I guess the easy thing is to generate reports from the Access database using
Access itself to help me figure out if who is looking at the data, how long
it takes the queries to run… But my real question is are there better thin
gs
to do with it.
I read that it might be best to migrate the logging away from Access and
into SQL Server directly but following that thought it lead me to some horro
r
stories about the many registry entries that need to be modified and the
pains encountered when updating service packs and the like. Seemed that
Analysis Services was tied in many ways to the msmdqlog.mdb for this type of
auditing.
I was also considering the possibility of creating a cube in the data
warehouse based on the QueryLog table from the msmdqlog.mdb
What are some of the prevailing ways to report on usage statistics from
Analysis Services?
Thanks for any help.
LarryWhat we do is import the logs from the Access database into SQL Server
on a regular basis with a DTS.
Then report off of the SQL Server.
Paul|||Larry,
If you have the space I would move the log, it will speed things up a little
and give you better access to the data you want. I've never heard of any
horror stories, I sure didn't have any problems.
Here are 2 links to help you get started -
http://www.sqlservercentral.com/col...ce
s.asp
[url]http://www.sqlservercentral.com/columnists/rbalukonis/analysisaboutdimensions.asp[
/url]
The first is a basic intro to building a basic cube. If you want you could
always adapt it to suit the things you want to analyse (eg. Usage by cube by
user by hour etc.)
I have a cube with Users; Cubes; Time (month day hour) and hours dims, my
measures are usage (no. of times a user appears in the log) and query
duration.
Realistically you can only get a feel good factor with the data, why?
because usually you only log every 10 queries so if user a makes 9 queries
and user b makes the 10th user b looks good. Also depending on the front end
each time a user does a new query the client cache may answer the query and
therefore not show on the log. There are quite a few things that mean you
don't gat an accurate picture of who’s doing what, but the log is a fair
yardstick.
I use the log as a gauge on cube utilization/ importance and user usage,
preheating the cache at peak periods and reporting to managers. Personally
query duration for me is not that important, even though I know it should be
.
Data is also available to analyse by dimension, it is fairly hard to
translate and prone to problems when dimensions are radically changed or
added. If it was reliable it would prove very beneficial. Have a look you ma
y
find it of great benefit.
Hope this helps you start out.|||Thanks for the help but I ran into a problem.
I read and followed the article “Analysis About Analysis Services” I
m
having problems
though with authentication. Is there any type of authentication tied to the
AS and the Access database?
I imported the msmdqlog.mdb into a new SQL Server 2000 database (there were
about 30 records).
I changed the registry entries QueryLogConnectionString and
RemoteQueryLogConnectionString to use the SQLLOLBE data provider, Initial
catalog and server.
I created the history table and then decided to test it out by querying the
FoodMart cube with Excel and the MDX Sample Application. No results were
logged. I returned to the msmdqlog.mdb file and nothing was added there
which I was glad to see. But I can’t get new entries added to the QueryLo
g
table in SQL. I figured something is wrong with the connection string.
I went to Analysis Services server properties and clicked Clear Log, I
checked the QueryLog table in the records I had brought over from the Access
were cleared from the SQL table.
I went to Event view and found a message that read:
Event Type: Error
Event Source: MSSQLServerOLAPService
Event Category: Server
Event ID: 124
Date: 11/30/2004
Time: 12:03:32 PM
User: N/A
Computer: ComputerName
Description:
Relational data provider reported error: [Cannot open database requested
in
login 'OLAP_Audit_Tracker'. Login fails.;42000].
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
I’ve changed SQL Security and made all the appropriate changes but still g
et
stopped at the authentication issue.
Any thoughts?
Thanks
Larry
"HWUK" wrote:
> Larry,
> If you have the space I would move the log, it will speed things up a litt
le
> and give you better access to the data you want. I've never heard of any
> horror stories, I sure didn't have any problems.
> Here are 2 links to help you get started -
> http://www.sqlservercentral.com/col...
ces.asp
> http://www.sqlservercentral.com/col...ons.as
p
> The first is a basic intro to building a basic cube. If you want you could
> always adapt it to suit the things you want to analyse (eg. Usage by cube
by
> user by hour etc.)
> I have a cube with Users; Cubes; Time (month day hour) and hours dims, my
> measures are usage (no. of times a user appears in the log) and query
> duration.
> Realistically you can only get a feel good factor with the data, why?
> because usually you only log every 10 queries so if user a makes 9 queries
> and user b makes the 10th user b looks good. Also depending on the front e
nd
> each time a user does a new query the client cache may answer the query an
d
> therefore not show on the log. There are quite a few things that mean you
> don't gat an accurate picture of who’s doing what, but the log is a fair
> yardstick.
> I use the log as a gauge on cube utilization/ importance and user usage,
> preheating the cache at peak periods and reporting to managers. Personally
> query duration for me is not that important, even though I know it should
be.
> Data is also available to analyse by dimension, it is fairly hard to
> translate and prone to problems when dimensions are radically changed or
> added. If it was reliable it would prove very beneficial. Have a look you
may
> find it of great benefit.
> Hope this helps you start out.
>|||First place to look is the Logging tab in properties of your analysis
services. Where it says "write to log once per ? queries" is how many
queries have to be sent to the server before a log is entered. If the value
is high your tests may not have made enough queries. The default should be
10. Also remember that pivot table services uses intelligent caching,
therefore, doing ten queries on a pivot table men not generate ten requests
to the server and the client computer may be able to deduce the query from
cache. To do a test you could reduce this to 1 and return it to a more
suitable number later.
If you feel everything is OK with the logging then look at the
authentication. Does the user have adequate permissions, what is the users
default database?, is the user a member of the analysis services
administration, is the Analysis Server Service a domain account etc.
check out - http://webservertalk.com/t943017.html and see if this discussion
is
simmilar to your case. Also see http://support.microsoft.com/?id=224973
Have a look at those to begin with, if you still have problems we'll I may
need more information.
tracking of the queries ran against the data warehouse.
I’ve gone through several articles about the MS Access database msmdqlog.m
db
and I’m just looking for ways to implement the auditing and I have a few
questions.
I guess the easy thing is to generate reports from the Access database using
Access itself to help me figure out if who is looking at the data, how long
it takes the queries to run… But my real question is are there better thin
gs
to do with it.
I read that it might be best to migrate the logging away from Access and
into SQL Server directly but following that thought it lead me to some horro
r
stories about the many registry entries that need to be modified and the
pains encountered when updating service packs and the like. Seemed that
Analysis Services was tied in many ways to the msmdqlog.mdb for this type of
auditing.
I was also considering the possibility of creating a cube in the data
warehouse based on the QueryLog table from the msmdqlog.mdb
What are some of the prevailing ways to report on usage statistics from
Analysis Services?
Thanks for any help.
LarryWhat we do is import the logs from the Access database into SQL Server
on a regular basis with a DTS.
Then report off of the SQL Server.
Paul|||Larry,
If you have the space I would move the log, it will speed things up a little
and give you better access to the data you want. I've never heard of any
horror stories, I sure didn't have any problems.
Here are 2 links to help you get started -
http://www.sqlservercentral.com/col...ce
s.asp
[url]http://www.sqlservercentral.com/columnists/rbalukonis/analysisaboutdimensions.asp[
/url]
The first is a basic intro to building a basic cube. If you want you could
always adapt it to suit the things you want to analyse (eg. Usage by cube by
user by hour etc.)
I have a cube with Users; Cubes; Time (month day hour) and hours dims, my
measures are usage (no. of times a user appears in the log) and query
duration.
Realistically you can only get a feel good factor with the data, why?
because usually you only log every 10 queries so if user a makes 9 queries
and user b makes the 10th user b looks good. Also depending on the front end
each time a user does a new query the client cache may answer the query and
therefore not show on the log. There are quite a few things that mean you
don't gat an accurate picture of who’s doing what, but the log is a fair
yardstick.
I use the log as a gauge on cube utilization/ importance and user usage,
preheating the cache at peak periods and reporting to managers. Personally
query duration for me is not that important, even though I know it should be
.
Data is also available to analyse by dimension, it is fairly hard to
translate and prone to problems when dimensions are radically changed or
added. If it was reliable it would prove very beneficial. Have a look you ma
y
find it of great benefit.
Hope this helps you start out.|||Thanks for the help but I ran into a problem.
I read and followed the article “Analysis About Analysis Services” I
m
having problems
though with authentication. Is there any type of authentication tied to the
AS and the Access database?
I imported the msmdqlog.mdb into a new SQL Server 2000 database (there were
about 30 records).
I changed the registry entries QueryLogConnectionString and
RemoteQueryLogConnectionString to use the SQLLOLBE data provider, Initial
catalog and server.
I created the history table and then decided to test it out by querying the
FoodMart cube with Excel and the MDX Sample Application. No results were
logged. I returned to the msmdqlog.mdb file and nothing was added there
which I was glad to see. But I can’t get new entries added to the QueryLo
g
table in SQL. I figured something is wrong with the connection string.
I went to Analysis Services server properties and clicked Clear Log, I
checked the QueryLog table in the records I had brought over from the Access
were cleared from the SQL table.
I went to Event view and found a message that read:
Event Type: Error
Event Source: MSSQLServerOLAPService
Event Category: Server
Event ID: 124
Date: 11/30/2004
Time: 12:03:32 PM
User: N/A
Computer: ComputerName
Description:
Relational data provider reported error: [Cannot open database requested
in
login 'OLAP_Audit_Tracker'. Login fails.;42000].
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
I’ve changed SQL Security and made all the appropriate changes but still g
et
stopped at the authentication issue.
Any thoughts?
Thanks
Larry
"HWUK" wrote:
> Larry,
> If you have the space I would move the log, it will speed things up a litt
le
> and give you better access to the data you want. I've never heard of any
> horror stories, I sure didn't have any problems.
> Here are 2 links to help you get started -
> http://www.sqlservercentral.com/col...
ces.asp
> http://www.sqlservercentral.com/col...ons.as
p
> The first is a basic intro to building a basic cube. If you want you could
> always adapt it to suit the things you want to analyse (eg. Usage by cube
by
> user by hour etc.)
> I have a cube with Users; Cubes; Time (month day hour) and hours dims, my
> measures are usage (no. of times a user appears in the log) and query
> duration.
> Realistically you can only get a feel good factor with the data, why?
> because usually you only log every 10 queries so if user a makes 9 queries
> and user b makes the 10th user b looks good. Also depending on the front e
nd
> each time a user does a new query the client cache may answer the query an
d
> therefore not show on the log. There are quite a few things that mean you
> don't gat an accurate picture of who’s doing what, but the log is a fair
> yardstick.
> I use the log as a gauge on cube utilization/ importance and user usage,
> preheating the cache at peak periods and reporting to managers. Personally
> query duration for me is not that important, even though I know it should
be.
> Data is also available to analyse by dimension, it is fairly hard to
> translate and prone to problems when dimensions are radically changed or
> added. If it was reliable it would prove very beneficial. Have a look you
may
> find it of great benefit.
> Hope this helps you start out.
>|||First place to look is the Logging tab in properties of your analysis
services. Where it says "write to log once per ? queries" is how many
queries have to be sent to the server before a log is entered. If the value
is high your tests may not have made enough queries. The default should be
10. Also remember that pivot table services uses intelligent caching,
therefore, doing ten queries on a pivot table men not generate ten requests
to the server and the client computer may be able to deduce the query from
cache. To do a test you could reduce this to 1 and return it to a more
suitable number later.
If you feel everything is OK with the logging then look at the
authentication. Does the user have adequate permissions, what is the users
default database?, is the user a member of the analysis services
administration, is the Analysis Server Service a domain account etc.
check out - http://webservertalk.com/t943017.html and see if this discussion
is
simmilar to your case. Also see http://support.microsoft.com/?id=224973
Have a look at those to begin with, if you still have problems we'll I may
need more information.
Analysis Services Data Access Auditing with msmdqlog.mdb
I’m starting to look at using a data warehouse and would like to have some
tracking of the queries ran against the data warehouse.
I’ve gone through several articles about the MS Access database msmdqlog.mdb
and I’m just looking for ways to implement the auditing and I have a few
questions.
I guess the easy thing is to generate reports from the Access database using
Access itself to help me figure out if who is looking at the data, how long
it takes the queries to run… But my real question is are there better things
to do with it.
I read that it might be best to migrate the logging away from Access and
into SQL Server directly but following that thought it lead me to some horror
stories about the many registry entries that need to be modified and the
pains encountered when updating service packs and the like. Seemed that
Analysis Services was tied in many ways to the msmdqlog.mdb for this type of
auditing.
I was also considering the possibility of creating a cube in the data
warehouse based on the QueryLog table from the msmdqlog.mdb
What are some of the prevailing ways to report on usage statistics from
Analysis Services?
Thanks for any help.
Larry
What we do is import the logs from the Access database into SQL Server
on a regular basis with a DTS.
Then report off of the SQL Server.
Paul
|||Larry,
If you have the space I would move the log, it will speed things up a little
and give you better access to the data you want. I've never heard of any
horror stories, I sure didn't have any problems.
Here are 2 links to help you get started -
http://www.sqlservercentral.com/colu...isservices.asp
http://www.sqlservercentral.com/colu...dimensions.asp
The first is a basic intro to building a basic cube. If you want you could
always adapt it to suit the things you want to analyse (eg. Usage by cube by
user by hour etc.)
I have a cube with Users; Cubes; Time (month day hour) and hours dims, my
measures are usage (no. of times a user appears in the log) and query
duration.
Realistically you can only get a feel good factor with the data, why?
because usually you only log every 10 queries so if user a makes 9 queries
and user b makes the 10th user b looks good. Also depending on the front end
each time a user does a new query the client cache may answer the query and
therefore not show on the log. There are quite a few things that mean you
don't gat an accurate picture of who’s doing what, but the log is a fair
yardstick.
I use the log as a gauge on cube utilization/ importance and user usage,
preheating the cache at peak periods and reporting to managers. Personally
query duration for me is not that important, even though I know it should be.
Data is also available to analyse by dimension, it is fairly hard to
translate and prone to problems when dimensions are radically changed or
added. If it was reliable it would prove very beneficial. Have a look you may
find it of great benefit.
Hope this helps you start out.
|||Thanks for the help but I ran into a problem.
I read and followed the article “Analysis About Analysis Services” I’m
having problems
though with authentication. Is there any type of authentication tied to the
AS and the Access database?
I imported the msmdqlog.mdb into a new SQL Server 2000 database (there were
about 30 records).
I changed the registry entries QueryLogConnectionString and
RemoteQueryLogConnectionString to use the SQLLOLBE data provider, Initial
catalog and server.
I created the history table and then decided to test it out by querying the
FoodMart cube with Excel and the MDX Sample Application. No results were
logged. I returned to the msmdqlog.mdb file and nothing was added there
which I was glad to see. But I can’t get new entries added to the QueryLog
table in SQL. I figured something is wrong with the connection string.
I went to Analysis Services server properties and clicked Clear Log, I
checked the QueryLog table in the records I had brought over from the Access
were cleared from the SQL table.
I went to Event view and found a message that read:
Event Type:Error
Event Source:MSSQLServerOLAPService
Event Category:Server
Event ID:124
Date:11/30/2004
Time:12:03:32 PM
User:N/A
Computer:ComputerName
Description:
Relational data provider reported error: [Cannot open database requested in
login 'OLAP_Audit_Tracker'. Login fails.;42000].
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
I’ve changed SQL Security and made all the appropriate changes but still get
stopped at the authentication issue.
Any thoughts?
Thanks
Larry
"HWUK" wrote:
> Larry,
> If you have the space I would move the log, it will speed things up a little
> and give you better access to the data you want. I've never heard of any
> horror stories, I sure didn't have any problems.
> Here are 2 links to help you get started -
> http://www.sqlservercentral.com/colu...isservices.asp
> http://www.sqlservercentral.com/colu...dimensions.asp
> The first is a basic intro to building a basic cube. If you want you could
> always adapt it to suit the things you want to analyse (eg. Usage by cube by
> user by hour etc.)
> I have a cube with Users; Cubes; Time (month day hour) and hours dims, my
> measures are usage (no. of times a user appears in the log) and query
> duration.
> Realistically you can only get a feel good factor with the data, why?
> because usually you only log every 10 queries so if user a makes 9 queries
> and user b makes the 10th user b looks good. Also depending on the front end
> each time a user does a new query the client cache may answer the query and
> therefore not show on the log. There are quite a few things that mean you
> don't gat an accurate picture of who’s doing what, but the log is a fair
> yardstick.
> I use the log as a gauge on cube utilization/ importance and user usage,
> preheating the cache at peak periods and reporting to managers. Personally
> query duration for me is not that important, even though I know it should be.
> Data is also available to analyse by dimension, it is fairly hard to
> translate and prone to problems when dimensions are radically changed or
> added. If it was reliable it would prove very beneficial. Have a look you may
> find it of great benefit.
> Hope this helps you start out.
>
|||First place to look is the Logging tab in properties of your analysis
services. Where it says "write to log once per ? queries" is how many
queries have to be sent to the server before a log is entered. If the value
is high your tests may not have made enough queries. The default should be
10. Also remember that pivot table services uses intelligent caching,
therefore, doing ten queries on a pivot table men not generate ten requests
to the server and the client computer may be able to deduce the query from
cache. To do a test you could reduce this to 1 and return it to a more
suitable number later.
If you feel everything is OK with the logging then look at the
authentication. Does the user have adequate permissions, what is the users
default database?, is the user a member of the analysis services
administration, is the Analysis Server Service a domain account etc.
check out - http://webservertalk.com/t943017.html and see if this discussion is
simmilar to your case. Also see http://support.microsoft.com/?id=224973
Have a look at those to begin with, if you still have problems we'll I may
need more information.
tracking of the queries ran against the data warehouse.
I’ve gone through several articles about the MS Access database msmdqlog.mdb
and I’m just looking for ways to implement the auditing and I have a few
questions.
I guess the easy thing is to generate reports from the Access database using
Access itself to help me figure out if who is looking at the data, how long
it takes the queries to run… But my real question is are there better things
to do with it.
I read that it might be best to migrate the logging away from Access and
into SQL Server directly but following that thought it lead me to some horror
stories about the many registry entries that need to be modified and the
pains encountered when updating service packs and the like. Seemed that
Analysis Services was tied in many ways to the msmdqlog.mdb for this type of
auditing.
I was also considering the possibility of creating a cube in the data
warehouse based on the QueryLog table from the msmdqlog.mdb
What are some of the prevailing ways to report on usage statistics from
Analysis Services?
Thanks for any help.
Larry
What we do is import the logs from the Access database into SQL Server
on a regular basis with a DTS.
Then report off of the SQL Server.
Paul
|||Larry,
If you have the space I would move the log, it will speed things up a little
and give you better access to the data you want. I've never heard of any
horror stories, I sure didn't have any problems.
Here are 2 links to help you get started -
http://www.sqlservercentral.com/colu...isservices.asp
http://www.sqlservercentral.com/colu...dimensions.asp
The first is a basic intro to building a basic cube. If you want you could
always adapt it to suit the things you want to analyse (eg. Usage by cube by
user by hour etc.)
I have a cube with Users; Cubes; Time (month day hour) and hours dims, my
measures are usage (no. of times a user appears in the log) and query
duration.
Realistically you can only get a feel good factor with the data, why?
because usually you only log every 10 queries so if user a makes 9 queries
and user b makes the 10th user b looks good. Also depending on the front end
each time a user does a new query the client cache may answer the query and
therefore not show on the log. There are quite a few things that mean you
don't gat an accurate picture of who’s doing what, but the log is a fair
yardstick.
I use the log as a gauge on cube utilization/ importance and user usage,
preheating the cache at peak periods and reporting to managers. Personally
query duration for me is not that important, even though I know it should be.
Data is also available to analyse by dimension, it is fairly hard to
translate and prone to problems when dimensions are radically changed or
added. If it was reliable it would prove very beneficial. Have a look you may
find it of great benefit.
Hope this helps you start out.
|||Thanks for the help but I ran into a problem.
I read and followed the article “Analysis About Analysis Services” I’m
having problems
though with authentication. Is there any type of authentication tied to the
AS and the Access database?
I imported the msmdqlog.mdb into a new SQL Server 2000 database (there were
about 30 records).
I changed the registry entries QueryLogConnectionString and
RemoteQueryLogConnectionString to use the SQLLOLBE data provider, Initial
catalog and server.
I created the history table and then decided to test it out by querying the
FoodMart cube with Excel and the MDX Sample Application. No results were
logged. I returned to the msmdqlog.mdb file and nothing was added there
which I was glad to see. But I can’t get new entries added to the QueryLog
table in SQL. I figured something is wrong with the connection string.
I went to Analysis Services server properties and clicked Clear Log, I
checked the QueryLog table in the records I had brought over from the Access
were cleared from the SQL table.
I went to Event view and found a message that read:
Event Type:Error
Event Source:MSSQLServerOLAPService
Event Category:Server
Event ID:124
Date:11/30/2004
Time:12:03:32 PM
User:N/A
Computer:ComputerName
Description:
Relational data provider reported error: [Cannot open database requested in
login 'OLAP_Audit_Tracker'. Login fails.;42000].
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
I’ve changed SQL Security and made all the appropriate changes but still get
stopped at the authentication issue.
Any thoughts?
Thanks
Larry
"HWUK" wrote:
> Larry,
> If you have the space I would move the log, it will speed things up a little
> and give you better access to the data you want. I've never heard of any
> horror stories, I sure didn't have any problems.
> Here are 2 links to help you get started -
> http://www.sqlservercentral.com/colu...isservices.asp
> http://www.sqlservercentral.com/colu...dimensions.asp
> The first is a basic intro to building a basic cube. If you want you could
> always adapt it to suit the things you want to analyse (eg. Usage by cube by
> user by hour etc.)
> I have a cube with Users; Cubes; Time (month day hour) and hours dims, my
> measures are usage (no. of times a user appears in the log) and query
> duration.
> Realistically you can only get a feel good factor with the data, why?
> because usually you only log every 10 queries so if user a makes 9 queries
> and user b makes the 10th user b looks good. Also depending on the front end
> each time a user does a new query the client cache may answer the query and
> therefore not show on the log. There are quite a few things that mean you
> don't gat an accurate picture of who’s doing what, but the log is a fair
> yardstick.
> I use the log as a gauge on cube utilization/ importance and user usage,
> preheating the cache at peak periods and reporting to managers. Personally
> query duration for me is not that important, even though I know it should be.
> Data is also available to analyse by dimension, it is fairly hard to
> translate and prone to problems when dimensions are radically changed or
> added. If it was reliable it would prove very beneficial. Have a look you may
> find it of great benefit.
> Hope this helps you start out.
>
|||First place to look is the Logging tab in properties of your analysis
services. Where it says "write to log once per ? queries" is how many
queries have to be sent to the server before a log is entered. If the value
is high your tests may not have made enough queries. The default should be
10. Also remember that pivot table services uses intelligent caching,
therefore, doing ten queries on a pivot table men not generate ten requests
to the server and the client computer may be able to deduce the query from
cache. To do a test you could reduce this to 1 and return it to a more
suitable number later.
If you feel everything is OK with the logging then look at the
authentication. Does the user have adequate permissions, what is the users
default database?, is the user a member of the analysis services
administration, is the Analysis Server Service a domain account etc.
check out - http://webservertalk.com/t943017.html and see if this discussion is
simmilar to your case. Also see http://support.microsoft.com/?id=224973
Have a look at those to begin with, if you still have problems we'll I may
need more information.
Thursday, February 9, 2012
Analysis Services and Reporting Services
Hello !
I am currently evaluating ability of Reporting Services (RS) to work
with output of MDX queries. RS looks very appealing when working with
relational output. But working with multidimensional output is somewhat
cumbersome. I haven't found any examples so far running against Analysis
Services.
Any thoughts\sources of information on this subject would be greatly
appreciated. We have to decide whether go with RS as UI to display output
from the cube .
Thank you in advance,
Igor.There's no real integration for AS currently in RS - just the OLAP provider
for OLE DB. This means you need to re-format the data you retrieve from cube
s and there's no query builder type implementation. What you could do is wri
te a reporting tool in .NET
to create the rdl (examples in the RS books online) and supply the MDX via a
client tool - such as a web page or desktop app. That way you can handle th
e formatting etc in the reporting tool, rather than doing every time in the
Report Designer.
HTH
Phil.|||Thanks a lot,
Igor.
"Phil Austin" <anonymous@.discussions.microsoft.com> wrote in message
news:E99A5AFD-CB71-4878-BD98-4380C528CBFC@.microsoft.com...
> There's no real integration for AS currently in RS - just the OLAP
provider for OLE DB. This means you need to re-format the data you retrieve
from cubes and there's no query builder type implementation. What you could
do is write a reporting tool in .NET to create the rdl (examples in the RS
books online) and supply the MDX via a client tool - such as a web page or
desktop app. That way you can handle the formatting etc in the reporting
tool, rather than doing every time in the Report Designer.
> HTH
> Phil.|||There are some examples in the samples folder of Reportin service to
fetch from OLAP. Try it
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi,
I would like to suggest u to use the MS Reporting Services addin of
Panorama NovaView to have MS Analysis Services views in MS Reporting
Services with a simple wizard. On our website www.gmsbv.nl you will
find some screenshots.
Marco
"imarchenko" <imarchenko@.hotmail.com> wrote in message news:<#$1yPagBEHA.1600@.tk2msftngp13.
phx.gbl>...
> Hello !
> I am currently evaluating ability of Reporting Services (RS) to work
> with output of MDX queries. RS looks very appealing when working with
> relational output. But working with multidimensional output is somewhat
> cumbersome. I haven't found any examples so far running against Analysis
> Services.
> Any thoughts\sources of information on this subject would be greatly
> appreciated. We have to decide whether go with RS as UI to display output
> from the cube .
>
> Thank you in advance,
>
> Igor.
I am currently evaluating ability of Reporting Services (RS) to work
with output of MDX queries. RS looks very appealing when working with
relational output. But working with multidimensional output is somewhat
cumbersome. I haven't found any examples so far running against Analysis
Services.
Any thoughts\sources of information on this subject would be greatly
appreciated. We have to decide whether go with RS as UI to display output
from the cube .
Thank you in advance,
Igor.There's no real integration for AS currently in RS - just the OLAP provider
for OLE DB. This means you need to re-format the data you retrieve from cube
s and there's no query builder type implementation. What you could do is wri
te a reporting tool in .NET
to create the rdl (examples in the RS books online) and supply the MDX via a
client tool - such as a web page or desktop app. That way you can handle th
e formatting etc in the reporting tool, rather than doing every time in the
Report Designer.
HTH
Phil.|||Thanks a lot,
Igor.
"Phil Austin" <anonymous@.discussions.microsoft.com> wrote in message
news:E99A5AFD-CB71-4878-BD98-4380C528CBFC@.microsoft.com...
> There's no real integration for AS currently in RS - just the OLAP
provider for OLE DB. This means you need to re-format the data you retrieve
from cubes and there's no query builder type implementation. What you could
do is write a reporting tool in .NET to create the rdl (examples in the RS
books online) and supply the MDX via a client tool - such as a web page or
desktop app. That way you can handle the formatting etc in the reporting
tool, rather than doing every time in the Report Designer.
> HTH
> Phil.|||There are some examples in the samples folder of Reportin service to
fetch from OLAP. Try it
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi,
I would like to suggest u to use the MS Reporting Services addin of
Panorama NovaView to have MS Analysis Services views in MS Reporting
Services with a simple wizard. On our website www.gmsbv.nl you will
find some screenshots.
Marco
"imarchenko" <imarchenko@.hotmail.com> wrote in message news:<#$1yPagBEHA.1600@.tk2msftngp13.
phx.gbl>...
> Hello !
> I am currently evaluating ability of Reporting Services (RS) to work
> with output of MDX queries. RS looks very appealing when working with
> relational output. But working with multidimensional output is somewhat
> cumbersome. I haven't found any examples so far running against Analysis
> Services.
> Any thoughts\sources of information on this subject would be greatly
> appreciated. We have to decide whether go with RS as UI to display output
> from the cube .
>
> Thank you in advance,
>
> Igor.
Subscribe to:
Posts (Atom)