Showing posts with label time. Show all posts
Showing posts with label time. Show all posts

Thursday, March 29, 2012

Data Structure for Date Time Selection

Hi All,
We have a table which looks like the following:
ID (int)
Status (int)
Elgiblity (int)
EligibleTime (datetime)
Counter (int)
Flag (bit)
Typically our queries look like
SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
= 0 AND Counter < 2
Since we're doing a lot of selects based on time + status codes... are
there any database structures suited for this?
Also, the values in this table are updated quite often... how do ensure
the indexes are update to date?
We load and use about 200K rows of data per day. We keep about 45 days
worth of data in the database. We would lilke to return the data as fast
as possible... does anyone have suggestions on how to store the data?
Thanks.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.comLucas
Are you concerned about performance of the query? Do the users complain that
they have to wait for a request?
What are the inedexes defined on the table?
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D131BA935A9nntprogerscom@.127.0.0.1...
> Hi All,
> We have a table which looks like the following:
> ID (int)
> Status (int)
> Elgiblity (int)
> EligibleTime (datetime)
> Counter (int)
> Flag (bit)
> Typically our queries look like
> SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
> Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
> = 0 AND Counter < 2
> Since we're doing a lot of selects based on time + status codes... are
> there any database structures suited for this?
> Also, the values in this table are updated quite often... how do ensure
> the indexes are update to date?
> We load and use about 200K rows of data per day. We keep about 45 days
> worth of data in the database. We would lilke to return the data as fast
> as possible... does anyone have suggestions on how to store the data?
> Thanks.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Hi
to auto update statistics, use database option
AUTO_UPDATE_STATISTICS
>from bol
When set to ON, existing statistics are automatically updated when the
statistics become out-of-date because the data in the tables has changed.
When set to OFF, existing statistics are not automatically updated; instead,
statistics can be manually updated. For more information, see Statistical
Information.
By default, AUTO_UPDATE_STATISTICS is set to ON.
The status of this option can be determined by examining the
IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
To know when exactly statistics are updated
DBCC SHOW_STATISTICS
To updat statistics
UPDATE STATISTICS table | view
[
index
| ( statistics_name [ ,...n ] )
]
[ WITH
[
[ FULLSCAN ]
| SAMPLE number { PERCENT | ROWS } ]
| RESAMPLE
]
[ [ , ] [ ALL | COLUMNS | INDEX ]
[ [ , ] NORECOMPUTE ]
]
Regards
R.D
"Uri Dimant" wrote:

> Lucas
> Are you concerned about performance of the query? Do the users complain th
at
> they have to wait for a request?
> What are the inedexes defined on the table?
>
> "Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
> news:Xns96D131BA935A9nntprogerscom@.127.0.0.1...
>
>|||SORRY AT WRONG PLACE
"R.D" wrote:
> Hi
> to auto update statistics, use database option
> AUTO_UPDATE_STATISTICS
> When set to ON, existing statistics are automatically updated when the
> statistics become out-of-date because the data in the tables has changed.
> When set to OFF, existing statistics are not automatically updated; instea
d,
> statistics can be manually updated. For more information, see Statistical
> Information.
> By default, AUTO_UPDATE_STATISTICS is set to ON.
> The status of this option can be determined by examining the
> IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
> To know when exactly statistics are updated
> DBCC SHOW_STATISTICS
> To updat statistics
> UPDATE STATISTICS table | view
> [
> index
> | ( statistics_name [ ,...n ] )
> ]
> [ WITH
> [
> [ FULLSCAN ]
> | SAMPLE number { PERCENT | ROWS } ]
> | RESAMPLE
> ]
> [ [ , ] [ ALL | COLUMNS | INDEX ]
> [ [ , ] NORECOMPUTE ]
> ]
> Regards
> R.D
>
> "Uri Dimant" wrote:
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:eUaMZwQuFHA.360@.TK2MSFTNGP12.phx.gbl:

> Lucas
> Are you concerned about performance of the query? Do the users
> complain that they have to wait for a request?
> What are the inedexes defined on the table?
Hi,
It's an application that runs against the table... the application
places phone calls on behalf of records in the database - we need to put
out 100s or 1000s of calls per minute(i.e. Emergency phone calls).
Right now there are a combination of factors:
1. Table Structure Could be Optimized
2. hardware could be better
As you see in my original post, the table is pretty much a flat file
(there are actually more columns - like 30+ but they're parameter
columns)... I'm just wondering if you guys have any pointers on how to
store datetime data in a way that is easily selectable.
Thanks!
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Lucas
can be bit specific
is it the problem of insertion or select statement to retrive it.
pl.Post script
Regards
R.D
"Lucas Tam" wrote:

> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:eUaMZwQuFHA.360@.TK2MSFTNGP12.phx.gbl:
>
> Hi,
> It's an application that runs against the table... the application
> places phone calls on behalf of records in the database - we need to put
> out 100s or 1000s of calls per minute(i.e. Emergency phone calls).
> Right now there are a combination of factors:
> 1. Table Structure Could be Optimized
> 2. hardware could be better
> As you see in my original post, the table is pretty much a flat file
> (there are actually more columns - like 30+ but they're parameter
> columns)... I'm just wondering if you guys have any pointers on how to
> store datetime data in a way that is easily selectable.
> Thanks!
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com
>|||examnotes <RD@.discussions.microsoft.com> wrote in
news:B0959AEA-AB2D-48A9-922D-5E0528AB5CE3@.microsoft.com:

> Lucas
> can be bit specific
> is it the problem of insertion or select statement to retrive it.
> pl.Post script
> Regards
> R.D
Problem with select statement:
SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
= 0 AND Counter < 2
Something like that can take a while... and use a lot of resources.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com

Tuesday, March 27, 2012

Data source for Time Dimension?

Hi;

I have been trying to create Time Dimension for one of the column in the table i.e. EntryDate and I am unable to connect the time dimension which I create using the Wizard using the 2nd option which is Without using data source. It created a dimension but it is not connected to any data source and when I process it gives me error, data source is not specified.

I am very new to Analysis service, I am using SQL Server 2005. I want to create a report where I can show my data by month, by year depending upon the EntryDate column of a table.

Thanks

Have you tried the adventure works dw data base that is part of the samples that you can install with SQL Server 2005 and SSAS2005? In that database you have several dimension tables and a time dimension table.

If you would like to test your skills in building dimensions with a data source I recommend this database.

Regards

Thomas Ivarsson

Sunday, March 25, 2012

Data Sliles or Filter or seperate Fact Tables for ourCube Partitio

Hi,
we have cubes partitioned on the time dimension. The majority of data that
comes in if for the last 4 days or so. The new data is loaded into a seperate
fact table, and loaded into a partition. This partition is then merged into
the current month partition.
However, we can have data that comes in which is a month or longer old. So
would be merged into the incorrect partition. And would remain there until a
full reprocess of the Fact tables are cube partitiions.
I understand that when data slices are defined for a partition, that AS2000
will only visit the partitions that contain the relevent data. What happens
if we use data slices in our situation?
If the data is in the incorrect partition, as a result of merging, is it
possible that the data is missed out, depending on the query? Or would the
merging not be allowed to take place?
Would we want to define data slices for all partitions, except for the
current month ?
Or would we want to define a filter, and rather than a build and merge, do
an incremental build against the cube ?
We are really looking for the quickest load time possible, while having
correct data of course.
Thanks in advance for any advice and help.
Is this for AS 2000 or 2005? In 2000, slices that you defined are used for
querying. In 2005, slices for MOLAP partitions are detected automatically.
For ROLAP partitions the slice you specify will be used to eliminate
partitions from queries.
But besides that, only your latest partition would have "incorrect" data,
right? So just don't set a slice for that last partition -- it would mean
that this partition would be unnecessarily scanned for some queries, but it
should be a fairly small partition anyway...
Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:370E22EC-6304-49F2-B01D-812B37B2E1EF@.microsoft.com...
> Hi,
> we have cubes partitioned on the time dimension. The majority of data that
> comes in if for the last 4 days or so. The new data is loaded into a
> seperate
> fact table, and loaded into a partition. This partition is then merged
> into
> the current month partition.
> However, we can have data that comes in which is a month or longer old. So
> would be merged into the incorrect partition. And would remain there until
> a
> full reprocess of the Fact tables are cube partitiions.
> I understand that when data slices are defined for a partition, that
> AS2000
> will only visit the partitions that contain the relevent data. What
> happens
> if we use data slices in our situation?
> If the data is in the incorrect partition, as a result of merging, is it
> possible that the data is missed out, depending on the query? Or would the
> merging not be allowed to take place?
> Would we want to define data slices for all partitions, except for the
> current month ?
> Or would we want to define a filter, and rather than a build and merge, do
> an incremental build against the cube ?
> We are really looking for the quickest load time possible, while having
> correct data of course.
> Thanks in advance for any advice and help.
>

Data Sliles or Filter or seperate Fact Tables for ourCube Partitio

Hi,
we have cubes partitioned on the time dimension. The majority of data that
comes in if for the last 4 days or so. The new data is loaded into a seperat
e
fact table, and loaded into a partition. This partition is then merged into
the current month partition.
However, we can have data that comes in which is a month or longer old. So
would be merged into the incorrect partition. And would remain there until a
full reprocess of the Fact tables are cube partitiions.
I understand that when data slices are defined for a partition, that AS2000
will only visit the partitions that contain the relevent data. What happens
if we use data slices in our situation?
If the data is in the incorrect partition, as a result of merging, is it
possible that the data is missed out, depending on the query? Or would the
merging not be allowed to take place?
Would we want to define data slices for all partitions, except for the
current month ?
Or would we want to define a filter, and rather than a build and merge, do
an incremental build against the cube ?
We are really looking for the quickest load time possible, while having
correct data of course.
Thanks in advance for any advice and help.Is this for AS 2000 or 2005? In 2000, slices that you defined are used for
querying. In 2005, slices for MOLAP partitions are detected automatically.
For ROLAP partitions the slice you specify will be used to eliminate
partitions from queries.
But besides that, only your latest partition would have "incorrect" data,
right? So just don't set a slice for that last partition -- it would mean
that this partition would be unnecessarily scanned for some queries, but it
should be a fairly small partition anyway...
Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:370E22EC-6304-49F2-B01D-812B37B2E1EF@.microsoft.com...
> Hi,
> we have cubes partitioned on the time dimension. The majority of data that
> comes in if for the last 4 days or so. The new data is loaded into a
> seperate
> fact table, and loaded into a partition. This partition is then merged
> into
> the current month partition.
> However, we can have data that comes in which is a month or longer old. So
> would be merged into the incorrect partition. And would remain there until
> a
> full reprocess of the Fact tables are cube partitiions.
> I understand that when data slices are defined for a partition, that
> AS2000
> will only visit the partitions that contain the relevent data. What
> happens
> if we use data slices in our situation?
> If the data is in the incorrect partition, as a result of merging, is it
> possible that the data is missed out, depending on the query? Or would the
> merging not be allowed to take place?
> Would we want to define data slices for all partitions, except for the
> current month ?
> Or would we want to define a filter, and rather than a build and merge, do
> an incremental build against the cube ?
> We are really looking for the quickest load time possible, while having
> correct data of course.
> Thanks in advance for any advice and help.
>sql

Data selection not happening for a date because of time format

hello all,

i am making a query which select the data again a particuler date.

I insert values in the table for with current date(Today's date) and the records is inserted with the date format(2006-07-14 16:12:09),now when i run the query after 2 or 3 minutes to select the records inserted today, my query returns no results.

I think it is because of the the time (14:16 in this case) that after 2 minutes, the query looks for the records inserted at (2006-07-14 18:12 or 2006-07-14 19:12) and does not get the result.

Is there a method to not consider the time(14:16) when running the query but the query fetches the records including the records inserted at this time(14:16) no matter at what time I run the query today?

Please anyone help me!

Thanks in advance!

to get all of today's rows use this

SELECT *
FROM YourTable
WHERE YourDateColumn >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks you SQL_Menace for writing to me!

your query did exactly that I needed.

Thanks indeed!

|||

SQL_Menace, I have another question that is closely related to the previous one.

I am searching for the records between two dates.

Here is my query(modified after your reply):

SELECT * FROM Mytable WHERE(DateEntered >=DATEADD(dd, DATEDIFF(dd, 0, '" + Convert.ToDateTime(srchdate) + "')+0, 0) AND DateEntered <= DATEADD(dd, DATEDIFF(dd, 0, '" + Convert.ToDateTime(todate) + "')+0, 0))

DateEntered is my date column, and Convert.ToDateTime(srchdate), Convert.ToDateTime(todate) are the values given by the user at runtime.

Problem here is the same. I insert 10 records today(2006-07-14 18:16:33) but when I provide yesterday(2006-07-13 19:16:33) as FROM DATE and today(2006-07-14 18:16:33) as TO DATE as parameter to search beteen these two dates, query returns nothing i.e. count is zero(0).

How can I achieve this?

Thank you!

Thursday, March 22, 2012

data retrival time

How can i speed up the return time from search between 2 different server?

I am currently using SQL 2000 and SQL 2005 With the same query, the searching time is much faster to select between database within the same server instead of two different one. Is there something that i can do in the server configuration to speed up the searching time?

Thanks in advance

Could you be more specific? The query in question, the execution plans and the table metadata, including the indexes plus some basic data on the rough row counts/value distributions would be very helpful too.|||

Hi Tengiz,

I am Calling a SP with Views inside that will cross over defferent Server,

For Example,

in Server 2005 i have a SP called spSample in the data base called dbDEV

within that SP I have View1, View2 and View3 in my databse , Tempdt, in Server 2000

so inside my SP it'll be something like the following


CREATE PROCEDURE [dbo].[spSample ]
(
@.Key_ID int
)
As
SELECT *

FROM tblBK WITH (NOLOCK)
right JOIN dbo.Tempdt.View1 ON dbo.dbDEV.tblBK.BK_ID = dbo.Tempdt.View1.BK_FK
left JOIN dbo.Tempdt.View2 ON dbo.dbDEV.tblBK.State_FK = dbo.Tempdt.View2.State_ID
left JOIN dbo.Tempdt.View3 ON dbo.dbDEV.tblBK.Country_FK = dbo.Tempdt.View3 .Country_ID
where (dbo.dbDEV.tblBK.Key_ID = @.Key_ID )
ORDER BY tblBK.POL

GO

the sp Works find if both database was with in the same server, but was very slow if i moved on of the database to a different server. What can i do to make returning data time faster?

Thanks

|||

Since the SP is created in the database dbDEV and the 'local' (with respect to the database the SP is created in) table tblBK participates in an outer join in the way that assumes that the entire table also participating in the join (View1) will be pulled in order to produce the result you request, depending on the size of the table this could indeed take a long time.

Do you really need the following outer join here: tblBK RIGHT join View1? Considering the where clause on tblBK.Key_ID it doesn’t look like having the tblBK participate in a right join matters here since the rows where tblBK.Key_ID is NULL are filtered out anyway.

Hence if you simply remove the 'RIGHT' from the query text and use the normal inner join instead you should see the improvement in the perf and see the results that you request:

CREATE PROCEDURE [dbo].[spSample ]
(
@.Key_ID int
)
As
SELECT *

FROM tblBK WITH (NOLOCK)
JOIN dbo.Tempdt.View1 ON dbo.dbDEV.tblBK.BK_ID = dbo.Tempdt.View1.BK_FK
left JOIN dbo.Tempdt.View2 ON dbo.dbDEV.tblBK.State_FK = dbo.Tempdt.View2.State_ID
left JOIN dbo.Tempdt.View3 ON dbo.dbDEV.tblBK.Country_FK = dbo.Tempdt.View3 .Country_ID
where (dbo.dbDEV.
tblBK.Key_ID = @.Key_ID )
ORDER BY tblBK.POL

Sunday, March 11, 2012

Data of different dates

I want to make a report which is using parameter field of a single date. But the same time I have some fields those have to show some data out of the parameter field. And the grouping is same.

For example, a coloum show data of the date which is slected but another field have to show data of the current month but Less and equal to the selected field.

ThankyouYour best bet is to create one as a subreport to the other. These 2 reports link by the parameter field, but use different filter.

That's all I can say with the info you provide.

Good luck.

Data not being displayed.

Hi,
I have just installed MS SQL in my computer and I am trying to run MSSQL and Visual studio for the first time. However I have run into aproblem that I can't solve. Basically the problem is that the data Ifetch from the database is not being displayed/bound to the ddl I haveon the page.
I have used VS to configure the sqlDataAdapter, DataSet, sqlConnection,and the sqlCommands. Basically the data is being fetched from theNorthwind sample database. From the preview option on thesqlDataAdapter I am able to preview the information from the database.So it seems to work. I have also configured the ddl settings so thatthe Categories table is the datasource. I have also set the ddl.DataTextField = "CategoryName"; ddl.DataValueField = "CategoryID";
When I compiled and run the page, no error messages are generated, butthe ddl is still completely blank and I can't understand why.
Does anybody know how I can solve this?? :s

Om Sri Sai Ram

Did you callDataBind method.

Thanks,

Ram

|||Hi,
I have just double-checked that I had called DataBind, and itturns out that I had. However, I had forgotten to fill the DataSet:
this.sqlDataAdapter1.Fill(this.dataSet11);
Now I get an error message:
Login failed for user 'DISABLED\ASPNET'|||Om Sri Sai Ram

Are u using windows authentication? If yes, check whether ASPNET account has pemission to access the table.
If you still have problems, post the code.
Thanks,
Ram

Wednesday, March 7, 2012

Data mining cube with 0-value facts

I'm defining a mining structure against an OLAP dimension. The continuous value that I'm using both as input and for forecasting represents the time to complete a certain process.

There's something that strikes me as if it could be a problem, but I'm not sure. Our fact table has multiple columns (with multiple correponding measures in the cube). The "time-to-complete" measure is only populated on some of the fact rows - the rows that represent completion information. Other rows represent other information, and the "time-to-complete" value is set to 0. This works fine for cumulative time-to-complete and average time-to-complete, but it seems like it could mess up data mining. Will those 0-value facts skew the mining results? I'm not seeing a way to filter out those entries and only include the non-zero facts in the mining processing.

Or perhaps I'm totally misunderstanding something, which is quite possible. :)

The zeroes will mess you up in DM and OLAP. An average of a value ignores nulls, but not zeroes.

You can set your measure to retain null values in the fact table - check out the data binding settings.

Hope this helps,

Richard

|||The zeros are properly handled in our average calculated measures in OLAP (we don't use the EE AverageOfChildren aggregation function).

Sounds like it will screw up DM stuff, though. If the values were nulls instead of 0, would DM handle it correctly?|||

Sine you've modeled the variable as continous, a 0 value will be treated as a possible value whereas a null would be treated as missing by DM. They would hence generate different DM model.

If the only zero value in your data correspond to null (missing) value, you can either filter those out from the input data or have another computed measure be null whenever the measure is 0 and use that measure as input to the data mining algorithm.

Hope this helps

|||>you can either filter those out from the input data

How would I do that? There isn't a dimension slice that represents this condition, and that's the only filtering that I've been able to find so far.

A computed measure wouldn't be ideal, but it might work. I'll think about that.
|||Sorry, my earlier statement was not accurate. You cannot filter by measure values in OLAP Mining Model.|||OK, after doing a little more testing, I'm more confused.

As a test, I created a new calculation that takes the original measure and converts 0-valued facts to Nulls. I added that new calculation to the mining structure and models and processed them. Then I ran a singleton prediction query for both the original and new measures. They came up with the exact same results. So, as far as I can tell, either:

a) The 0-value facts don't affect the mining results
b) The null facts are treated the same as the 0 facts (and not ignored)
c) Something about how I'm doing my test is flawed.

Anyone have any suggestions or thoughts here?|||It's possible that the column doesn't have any impact on the result at all? What algorithm are you using and does the column show up as "important" in the viewer?|||Actually, I'm working with a degeneratively simple test case here, where the mining structure only includes one input - a single continuous value, used both as input and for prediction. Perhaps that's too trivial for a reasonable test.

I'm using the Neural Network algorithm, but I can't bring up the viewer - I just get an error (which I reported in another thread in these forums).|||

Aha!

That's the reason for this problem and may be the reason for the viewer problem as well. It seems you found a bug in the system, the bug being that the software didn't return an error when you tried to create this model .

When you mark a column as "input" what you are stating is "use this column as an input to predict other columns." When you mark it as output you are stating "determine values of this column based on all other inputs." We have the concept of "input AND output" because we allow you to predict multiple attributes in a single model.

Therefore, what is happening is that for that column, you have no inputs, and it is an input for no other columns, since there are no more columns for which it could be an input. Simply put, it's an invalid model definition which we should have rejected from the start.

Thanks

-Jamie

|||Doh! Did I mention I was a noob with data mining? :)

I fixed the model, and the results seem to make more sense now. However, it didn't fix the problem with the mining model viewer.|||It's hard to say - there may not be enough differentiation for the viewer to show patterns at that point. I would try things out with a more well known dataset - e.g. the tutorial so you can get a feel for how the tools work in a tested environment and then see if the issue seems to be the software not working correctly or your data not being rich enough to demonstrate descriptive results.

Data mining against a cube with time dimension

We have a set of cubes and dimensions, and we're experimenting with data mining against the cubes (primarily for forecasting applications). We have a custom time dimension (which we call calendar), not generated by the BIStudio wizard. The dimension has year/month/day/hour/... attributes. But when I try to add this Calendar dimension to the mining structure as a nested table using BI studio, it only shows the Year attribute, not the others. Other dimensions seem to show all the attributes.

Is there something we've done wrong in defining our time dimension? What determines which attributes show up as available for selection in BI studio?Attributes need to be "browseable" to be included in mining models, meaning they need attribute hierarchies. In this case you would need a "YearMonth" attribute to build a model by months. If, for example, you simply used "month", you would only get 12 months - each with the aggregation of all years for each month.|||Thanks, Jamie. But our attributes do have attribute hierarchies enabled (although they are set to invisible, so only our user hierarchies show up in cube browsers). And the attributes use composite keys which include all the relevant time components (e.g. year and month for year attribute), which if I understand you correctly is equivalent to your "YearMonth" example. Yet still, only Year shows up as an attribute for DM.|||I did a little more experimenting with this problem, and here's what I've found.

If I use the New Mining Structure wizard, then when adding a nested table all of the attributes in our calendar dimension are shown correctly, and I can select them.

However, if I don't add the nested table in the wizard, then try to add it later in the designer, only the Year level shows up. I can't seem to get the other attributes to show up as options at that point.

You know, I just went in to Adventure Works and tried setting up a new mining structure against the cube, and it looks like it behaves the same way. So I guess there isn't something wonky about our dimension setup. But why this difference? Why can I add an nested table with an attribute in wizard but not the designer?|||

When you select a Measure Group->Dimension, the attributes you should see are not all the Cube Dimension attributes, but the attributes in the Dimension which relate to the measure group. The attributes shown by the designer is the correct set. In your case, please check whether the measure group relates the Time dimension to the fact table by the Year attribute.

We have confirmed that this is a bug in the wizard because it shows all the attributes of the cube dimension. We’ll address this issue in an upcoming release.

Thanks

Friday, February 24, 2012

data loss

Hi,

I have a big problem with a database in MS SQL SERVER 2000.

the rows into the some tables, for the second time, have been mixed between
they without appearing reason.

the application that uses the db is totally TRANSACTIONAL and they do not
exist query that they do not have clause WHERE.

The database is on a computer with NAS architecture.

I have this problem for the first time in 5 years of use of MS SQL SERVER

Can someone help me?

TIA"Stefano Ferrante" <s.ferrante@.staffmedia.it> wrote in message
news:c2flov$sqc$1@.lacerta.tiscalinet.it...
> Hi,
> I have a big problem with a database in MS SQL SERVER 2000.
> the rows into the some tables, for the second time, have been mixed
between
> they without appearing reason.

I have no idea what you mean above. Inserted rows are disappearing?

Or rows appear to be in a different order when you do a select?

Remember, a table is a collection of rows. There is NO order unless you use
an ORDER BY clause. It doesn't matter what order they are inserted in.

> the application that uses the db is totally TRANSACTIONAL and they do not
> exist query that they do not have clause WHERE.
> The database is on a computer with NAS architecture.

SQL Server does NOT normally support a NAS architecture. Are you sure
you don't mean SAN? That's more common.

> I have this problem for the first time in 5 years of use of MS SQL SERVER
> Can someone help me?

> TIA|||Stefano,
SQL Server is behaving normally if it is randomly sorting data. Data is not
supposed to be sorted any particular way and you are not supposed to write
code that relies on a particular sort. If you did write code that depends
on a particular sort, shame on you--but a view with an ORDER BY clause may
get you the sort you need. However--most of the time ORDER BY also means
the data isn't updateable so if the reason for the sort is to update things
you may be SOL.

"Stefano Ferrante" <s.ferrante@.staffmedia.it> wrote in message
news:c2flov$sqc$1@.lacerta.tiscalinet.it...
> Hi,
> I have a big problem with a database in MS SQL SERVER 2000.
> the rows into the some tables, for the second time, have been mixed
between
> they without appearing reason.
> the application that uses the db is totally TRANSACTIONAL and they do not
> exist query that they do not have clause WHERE.
> The database is on a computer with NAS architecture.
> I have this problem for the first time in 5 years of use of MS SQL SERVER
> Can someone help me?
> TIA

Sunday, February 19, 2012

Data in table is deleted

Hi!
I have a problem with table in a SQL Server 2000 box. It seems like the
table is truncated from time to time! I don't know why and it has just
started occuring, but it happens maybe once a week now.
Is there any way to monitor if a delete statement occurs on just that table?
Is it possible in Profiler to monitors just action against one table?
Any hint or tips would be greatly appreciated!!
regards
Fredrik
Yes you can have profiler monitor a single table... Set the table in the
filter clause... Delete is not the same as truncate however, so watch for
both...(Delete will run a trigger, if one exists, and truncate does not.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:7631AE53-49D2-4504-8A87-6FB5C61FB517@.microsoft.com...
> Hi!
> I have a problem with table in a SQL Server 2000 box. It seems like the
> table is truncated from time to time! I don't know why and it has just
> started occuring, but it happens maybe once a week now.
> Is there any way to monitor if a delete statement occurs on just that
table?
> Is it possible in Profiler to monitors just action against one table?
> Any hint or tips would be greatly appreciated!!
> regards
> Fredrik

Data in table is deleted

Hi!
I have a problem with table in a SQL Server 2000 box. It seems like the
table is truncated from time to time! I don't know why and it has just
started occuring, but it happens maybe once a week now.
Is there any way to monitor if a delete statement occurs on just that table?
Is it possible in Profiler to monitors just action against one table?
Any hint or tips would be greatly appreciated!!
regards
FredrikYes you can have profiler monitor a single table... Set the table in the
filter clause... Delete is not the same as truncate however, so watch for
both...(Delete will run a trigger, if one exists, and truncate does not.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:7631AE53-49D2-4504-8A87-6FB5C61FB517@.microsoft.com...
> Hi!
> I have a problem with table in a SQL Server 2000 box. It seems like the
> table is truncated from time to time! I don't know why and it has just
> started occuring, but it happens maybe once a week now.
> Is there any way to monitor if a delete statement occurs on just that
table?
> Is it possible in Profiler to monitors just action against one table?
> Any hint or tips would be greatly appreciated!!
> regards
> Fredrik

Data in Report Headers

I'm having the devil of a time working around RS limitations here.
I have a report that displays a purchase order. ON all pages but the first I'm supposed to display a header like this:
From: OUR NAME To: Vendor Name
PO 123456 Page 2 of 4 Date: 01 January 2007
The three non-date fields in the header appear in the datasets used in the report. After much trial and error I ended up
creating three hidden textboxes in the report body that contain the data I want to use in the header. I then use separate
textboxes in the header which refer to those textboxes in the body.
Aside from the fact that this is a huge PITA, I find that this header only works properly on the first page of the report!
ON pages 2 and later the fields are empty!
Any suggestions? I'm pulling my hair out here on something that should be quite simple...
BarryMy favorite solution is to use internal parameters with query-based default
value.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry Seymour" <bseymour@.spamcop.net> wrote in message
news:Oa2GTf4LHHA.5016@.TK2MSFTNGP04.phx.gbl...
> I'm having the devil of a time working around RS limitations here.
> I have a report that displays a purchase order. ON all pages but the first
> I'm supposed to display a header like this:
> From: OUR NAME To: Vendor Name
> PO 123456 Page 2 of 4 Date: 01 January 2007
> The three non-date fields in the header appear in the datasets used in the
> report. After much trial and error I ended up
> creating three hidden textboxes in the report body that contain the data I
> want to use in the header. I then use separate
> textboxes in the header which refer to those textboxes in the body.
> Aside from the fact that this is a huge PITA, I find that this header only
> works properly on the first page of the report!
> ON pages 2 and later the fields are empty!
> Any suggestions? I'm pulling my hair out here on something that should be
> quite simple...
> Barry
>|||Otay... lemme research what the heck you mean and I'll come back if I don't get it. <g>
Barry
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message news:umi8F86LHHA.3588@.TK2MSFTNGP06.phx.gbl...
My favorite solution is to use internal parameters with query-based default
value.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Barry Seymour" <bseymour@.spamcop.net> wrote in message
news:Oa2GTf4LHHA.5016@.TK2MSFTNGP04.phx.gbl...
> I'm having the devil of a time working around RS limitations here.
> I have a report that displays a purchase order. ON all pages but the first
> I'm supposed to display a header like this:
> From: OUR NAME To: Vendor Name
> PO 123456 Page 2 of 4 Date: 01 January 2007
> The three non-date fields in the header appear in the datasets used in the
> report. After much trial and error I ended up
> creating three hidden textboxes in the report body that contain the data I
> want to use in the header. I then use separate
> textboxes in the header which refer to those textboxes in the body.
> Aside from the fact that this is a huge PITA, I find that this header only
> works properly on the first page of the report!
> ON pages 2 and later the fields are empty!
> Any suggestions? I'm pulling my hair out here on something that should be
> quite simple...
> Barry
>|||"Barry Seymour" wrote:
> Otay... lemme research what the heck you mean and I'll come back if I don't get it. <g>
> Barry
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message news:umi8F86LHHA.3588@.TK2MSFTNGP06.phx.gbl...
> My favorite solution is to use internal parameters with query-based default
> value.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Barry Seymour" <bseymour@.spamcop.net> wrote in message
> news:Oa2GTf4LHHA.5016@.TK2MSFTNGP04.phx.gbl...
> > I'm having the devil of a time working around RS limitations here.
> >
> > I have a report that displays a purchase order. ON all pages but the first
> > I'm supposed to display a header like this:
> >
> > From: OUR NAME To: Vendor Name
> > PO 123456 Page 2 of 4 Date: 01 January 2007
> >
> > The three non-date fields in the header appear in the datasets used in the
> > report. After much trial and error I ended up
> > creating three hidden textboxes in the report body that contain the data I
> > want to use in the header. I then use separate
> > textboxes in the header which refer to those textboxes in the body.
> >
> > Aside from the fact that this is a huge PITA, I find that this header only
> > works properly on the first page of the report!
> > ON pages 2 and later the fields are empty!
> >
> > Any suggestions? I'm pulling my hair out here on something that should be
> > quite simple...
> >
> > Barry
> >
> >
>
>|||Did you find out how to fix this? I'm having the same problem.
Ken
"Barry Seymour" wrote:
> Otay... lemme research what the heck you mean and I'll come back if I don't get it. <g>
> Barry
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message news:umi8F86LHHA.3588@.TK2MSFTNGP06.phx.gbl...
> My favorite solution is to use internal parameters with query-based default
> value.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Barry Seymour" <bseymour@.spamcop.net> wrote in message
> news:Oa2GTf4LHHA.5016@.TK2MSFTNGP04.phx.gbl...
> > I'm having the devil of a time working around RS limitations here.
> >
> > I have a report that displays a purchase order. ON all pages but the first
> > I'm supposed to display a header like this:
> >
> > From: OUR NAME To: Vendor Name
> > PO 123456 Page 2 of 4 Date: 01 January 2007
> >
> > The three non-date fields in the header appear in the datasets used in the
> > report. After much trial and error I ended up
> > creating three hidden textboxes in the report body that contain the data I
> > want to use in the header. I then use separate
> > textboxes in the header which refer to those textboxes in the body.
> >
> > Aside from the fact that this is a huge PITA, I find that this header only
> > works properly on the first page of the report!
> > ON pages 2 and later the fields are empty!
> >
> > Any suggestions? I'm pulling my hair out here on something that should be
> > quite simple...
> >
> > Barry
> >
> >
>
>|||We had a similar problem.
My workaround was to dump the Report Header and setup a 'dummy' group, it
must be the first group. 'Include Group Header' don't 'Repeat Group Header'
and don't 'Include Group Footer'.
Whatever you put it this group, it will accept parameters and dataset data
will only appear once at the beginning of the report.
Hope this helps

Friday, February 17, 2012

Data from three Tables (Joins)

Thanks for your responses.
Well the time can be same and cannot be same
For ex:
Table1
(timestamp,readingA)
(2/2/2005 12:09:26,123)
(2/2/2005 12:19:26,324)
Table2
(timestamp,readingB)
(2/2/2005 12:09:26,321)
(2/2/2005 12:29:26,657)
Table3
(timestamp,readingC)
(2/2/2005 12:09:26,231)
(2/2/2005 12:39:26,987)
Result Desired
(timestamp,readingA,readingB,readingC)
2/2/2005 12:09:26,123,321,231
2/2/2005 12:19:26,324,,
2/2/2005 12:29:26,,657,
2/2/2005 12:39:26,,,987
AS you can see the timestamp which is common in all tables is
displayed once alongwith its readings, but the one which is not common
is displayed alongwith its readings while other readings are blank.
I hope now it is clearThis is a typical xtab problem. Here is a trick.
select timestamp,max(case when tb=1 then reading else 0 end) readingA,
max(case when tb=2 then reading else 0 end) readingB,
max(case when tb=3 then reading else 0 end) readingC
from(
select *,1
from tb1
union all select *,2
from tb2
union all select *,3
from tb3) derived(timestamp,reading,tb)
group by timestamp
Do check out these:
http://support.microsoft.com/kb/175574
or
http://www.sqlteam.com/item.asp?ItemID=2955
or
http://www.rac4sql.net/
-oj
"Pradeep" <agarwalp@.eeism.com> wrote in message
news:364c5b9b.0502022352.7cddb731@.posting.google.com...
> Thanks for your responses.
> Well the time can be same and cannot be same
> For ex:
> Table1
> (timestamp,readingA)
> (2/2/2005 12:09:26,123)
> (2/2/2005 12:19:26,324)
> Table2
> (timestamp,readingB)
> (2/2/2005 12:09:26,321)
> (2/2/2005 12:29:26,657)
> Table3
> (timestamp,readingC)
> (2/2/2005 12:09:26,231)
> (2/2/2005 12:39:26,987)
> Result Desired
> (timestamp,readingA,readingB,readingC)
> 2/2/2005 12:09:26,123,321,231
> 2/2/2005 12:19:26,324,,
> 2/2/2005 12:29:26,,657,
> 2/2/2005 12:39:26,,,987
> AS you can see the timestamp which is common in all tables is
> displayed once alongwith its readings, but the one which is not common
> is displayed alongwith its readings while other readings are blank.
> I hope now it is clear|||On 2 Feb 2005 23:52:26 -0800, Pradeep wrote:

>Thanks for your responses.
>Well the time can be same and cannot be same
>For ex:
>Table1
>(timestamp,readingA)
>(2/2/2005 12:09:26,123)
>(2/2/2005 12:19:26,324)
>Table2
>(timestamp,readingB)
>(2/2/2005 12:09:26,321)
>(2/2/2005 12:29:26,657)
>Table3
>(timestamp,readingC)
>(2/2/2005 12:09:26,231)
>(2/2/2005 12:39:26,987)
>Result Desired
>(timestamp,readingA,readingB,readingC)
>2/2/2005 12:09:26,123,321,231
>2/2/2005 12:19:26,324,,
>2/2/2005 12:29:26,,657,
>2/2/2005 12:39:26,,,987
>AS you can see the timestamp which is common in all tables is
>displayed once alongwith its readings, but the one which is not common
>is displayed alongwith its readings while other readings are blank.
Hi Pradeep,
Try if this accomplishes what you want:
SELECT COALESCE (A."timestamp", B."timestamp", C."timestamp"),
A.readingA, B.readingB, C.readingC
FROM Table1 AS A
FULL OUTER JOIN Table2 AS B
ON B."timestamp" = A."timestamp"
FULL OUTER JOIN Table3 AS C
ON C."timestamp" = COALESCE (A."timestamp", B."timestamp")
(untested)
By the way, I suggest you use another name for the timestamp column, as
timestamp is a reserved word in SQL Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)