New Time Dimension Role after cube wizard

If I have a data source view consisting of two tables - a fact table and a time dimension table. The fact table has three date columns - transaction begin date, transaction end date, and updated date. When I initially designed the data source view my requirements only called for the trans begin and end dates so I made two joins to the time table from the sales table. I then ran the cube wizard which correctly detected that I intended to use the time table twice for two different Time hierarchies (Roles) on a single time dimension.

I have recently been asked to add the updated date but I cannot figure out how to add a new hierarchy (Role) on the time dimension. Any Ideas?

You should be able to go to the Dimension Usage tab in the cube designer and click on the Add Cube Dimension button on the toolbar (the third button from the left). When the list of dimensions comes up, simply select your Time dimension and add it again. You'll then see the dimension added within the list of dimensions down the left side of the tab, likely with a name like Time (Time 1) or something similar. Now, simply highlight the newly added role-playing dimension, hit F2, and rename it whatever you want. Then, of course, set the appropriate relationship between this new dimension and the measure group(s) within the cube...

HTH,

Dave Fackler

sql

New Template, open exsisting, any way to call reporting wizard now?

Any way to load my new template, and then call wizard? seems wizard is
not accessable if after I start with open existing item.
Can anybody recommend and easy to follow book on on reporting services
2005?
dumb question, say I have a grouping report like this
group
detail detail datetime
detail detail datetime
detail detail datetime
and I want to change the background color any datetime fields that
elapsed more than 4 hours since last entry to red. How? where? what?
Thanks.In the background color - have an expression :
I hardcoded the date/time below as an example, but this would actually be
your Fields!dtDateField.Value and getdate() would be Now()
If it is greater than 4 hours, it is displayed in red, otherwise blue - but
=iif(datediff(hh, '10/27/06 8:00:00AM', Now()) > 4, "Red", "White")
=-Chris
"Jason" <jason@.cyberpine.com> wrote in message
news:1161914681.963331.129650@.i3g2000cwc.googlegroups.com...
> Any way to load my new template, and then call wizard? seems wizard is
> not accessable if after I start with open existing item.
> Can anybody recommend and easy to follow book on on reporting services
> 2005?
>
> dumb question, say I have a grouping report like this
>
> group
> detail detail datetime
> detail detail datetime
> detail detail datetime
>
> and I want to change the background color any datetime fields that
> elapsed more than 4 hours since last entry to red. How? where? what?
> Thanks.
>|||sorry, 4 hours greater than than the date/time on the subsiquent row
the row.
But, the thing I really stuck on is the report design, if I'm limited
to one dataset the group. Can I have two datasets in a groupset, or
will I have create a view with duplicating data?
I have two tables
filelog:
id
filename
datelog:
id
date
type
I'm trying to do this
Id
filename, filename, filename (line wraping if neccessary)
date type
date type
id
filename, filename, filename (line wraping if neccessary)
date type
date type
==there might be some 5 to 20 filelog rows for every id, there can number
of datelog entries with the same id, but other than Id there is no
other corrilationbe between filelog and datelog
Chris Conner wrote:
> In the background color - have an expression :
> I hardcoded the date/time below as an example, but this would actually be
> your Fields!dtDateField.Value and getdate() would be Now()
> If it is greater than 4 hours, it is displayed in red, otherwise blue - but
> =iif(datediff(hh, '10/27/06 8:00:00AM', Now()) > 4, "Red", "White")
> =-Chris
> "Jason" <jason@.cyberpine.com> wrote in message
> news:1161914681.963331.129650@.i3g2000cwc.googlegroups.com...
> > Any way to load my new template, and then call wizard? seems wizard is
> > not accessable if after I start with open existing item.
> >
> > Can anybody recommend and easy to follow book on on reporting services
> > 2005?
> >
> >
> > dumb question, say I have a grouping report like this
> >
> >
> > group
> > detail detail datetime
> > detail detail datetime
> > detail detail datetime
> >
> >
> > and I want to change the background color any datetime fields that
> > elapsed more than 4 hours since last entry to red. How? where? what?
> >
> > Thanks.
> >

New subscription wizard never completes or even errors (SQL 2005 Trans repl with Queued Up

I am testing replication (Transactional with Queued Updating) between SQL
Server 2005 Developer Edition (which is on a domain) and SQL Express (which
is on a machine on same LAN but in a workgroup). I have configured the
publisher as its own distributor and created a publication consisting of 2
views (1 indexed, 1 regular). My problem is that when I run the New
Subscription Wizard, after clicking Finish the process hangs forever on the
first step ("Creating Subscription For ....."). No errors appear, nothing.
Clicking Stop has no effect.
Both views are tiny, only returning half a dozen rows. I have also tried
with a publication containing only one small table, and the same thing
occurs.
The publisher is clearly able to connect to the subscriber because the
linked server is created on the subscriber successfully. There is no sign of
a subscription however on the subscriber.
Here is the configuration for the subscription (following steps in New
Subscription Wizard):
Push subscription
Subscription database is new blank database
Distribution Agent Security: Run under SQL Agent service account, Connects
to distributor by impersonation, Connects to subscriber using a SysAdmin SQL
login
Schedule: runs continuously
Queued updating
Create new linked server using same SysAdmin SQL login as above
Initialize subscription immediately
What could be wrong?
Can you script out your publication creation steps and then run it manually
through Query Analyzer or the SSMS and see where it hangs. This should give
you some indication of where the problem is.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||The step that hangs is the last step - the execution of sp_link_publication
on the subscriber:
exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db =
N'RMIS_QA', @.publication = N'test views', @.distributor =
N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
N'xxxx'
In the Application Event log the following error appears:
MS DTC is unable to communicate with MS DTC on a remote system. No common
RPC protocol is supported between the two systems. Please ensure that one
or more of the following RPC protocols are common to both systems: TCP/IP,
SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904, CmdLine:
C:\WINNT\System32\msdtc.exe
I have checked that the MSDTC service is running on publisher and
subscriber. The publisher server is Windows XP SP2 and from googling it
looks like DTC is not given network access by default, however I have
enabled the required settings and the error persists:
In Admin tools.Component Services or via Component Services MSC Snap-in
- right-click the appropriate computer node and select properties
- on the MSDTC tab click Security Configuration button
- in addition to the Network DTC Access being ticked, tick Allow Remote
Clients, Allow Inbound, Allow Outbound
- restart MSDTC
The subscriber server is Windows 2000 Workstation, and it doesn't look like
there are any DTC settings to configure.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||Is this an upgrade from SQL 2000? There are some issues with a remote
distributor in an upgrade. Basically the repl_distributor remote server is
modified to a linked server and there have been cases where you have to
update the passwords to get this to work. AFAIK - MSDTC is not involved in
queued replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
> The step that hangs is the last step - the execution of
> sp_link_publication on the subscriber:
> exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db
> = N'RMIS_QA', @.publication = N'test views', @.distributor =
> N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
> N'xxxx'
> In the Application Event log the following error appears:
> MS DTC is unable to communicate with MS DTC on a remote system. No common
> RPC protocol is supported between the two systems. Please ensure that one
> or more of the following RPC protocols are common to both systems:
> TCP/IP, SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904,
> CmdLine: C:\WINNT\System32\msdtc.exe
> I have checked that the MSDTC service is running on publisher and
> subscriber. The publisher server is Windows XP SP2 and from googling it
> looks like DTC is not given network access by default, however I have
> enabled the required settings and the error persists:
> In Admin tools.Component Services or via Component Services MSC Snap-in
> - right-click the appropriate computer node and select properties
> - on the MSDTC tab click Security Configuration button
> - in addition to the Network DTC Access being ticked, tick Allow Remote
> Clients, Allow Inbound, Allow Outbound
> - restart MSDTC
> The subscriber server is Windows 2000 Workstation, and it doesn't look
> like there are any DTC settings to configure.
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
|||I figured it out after a long search.
MSDTC is involved, at least to set up the subscription. MSDTC in Windows XP
(and Win 2003 also I think) has some network access settings that are set to
prevent any access from outside by default. I mentioned these before.
However, in addition to setting these settings to allow network access,
there is a security setting for RPC, which I missed. This is the registry
key:
HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Win dows
NT\RPC\RestrictRemoteClients
This needs to exist and have a value of 0 (DWORD) if the subscriber is
Windows 2000.
For more info read:
http://blogs.msdn.com/florinlazar/archive/2004/06/18/159127.aspx
http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/sp2netwk.mspx#XSLTsection128121120120
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23tmOUdqAHHA.3536@.TK2MSFTNGP03.phx.gbl...
> Is this an upgrade from SQL 2000? There are some issues with a remote
> distributor in an upgrade. Basically the repl_distributor remote server is
> modified to a linked server and there have been cases where you have to
> update the passwords to get this to work. AFAIK - MSDTC is not involved in
> queued replication.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>

New partition (SSAS 2005) - named query not in "available tables"

I'm trying to create another partition in an existing measure group.
I start the new partition wizard and choose my data source view in the
"Look in", and then click "Find tables".
My named query does not appear in the list. What could be wrong?

I have saved and refreshed the data source view. The named query has the
same column names and data types that the original table has (that the
measure group is using now).

I also tried to make the named query a ordinary view in the source
database but that does not appear in the list either (I choose the
data source then instead of the data source view).

Hi,

I wouldn't suspect that you'd see a named query in the list of tables when you go to create a new partition. I'm not sure if that list of tables shows views from the underlying DB, either..But, you can make your partition query binding, instead of table binding, and you can make the partition source the same query you're using in your named query, or a subset of it with a where clause, or whatever you choose.

HTH,
C
|||

I don't understand...

In the "New partition wizard" there isn't a whole lot to choose from... Pick either a data source or a data source view, and then pick the "table" - which I suppose could be a view, table, named query... When I choose data source view I can only see the table that is already being used as a fact table in the measure group (and this is actually a named query and not a table). When I choose the data soure, eg. the underlying database, I get nothing.

|||

I found the answer myself.

I thought I had the same datatypes because I created my new named query from the original table that the first named query (the one being used in the measure group already) was based on. But it turned out that the first named query did not get the same datatypes i all columns as the underlying table.

In BOL it states that the tables (or named queries) used in the same measure group must be sufficiently similar - I would call it exactly the same :-)

New Import/Export Wizard error

I am making the adjustment from SQL Server 2000 to SQL Server 2005. In the
past I have created named ranges in Excel that look like data tables (header
row followed by multiple data rows) and used DTS to create database tables i
n
SQL Server 2000. It has worked for several years. Now, using the SQL
Server 2005 import/export wizard, the same named ranges cause the following
error.
- Validating (Error)
Messages
Error 0xc00470fe: Data Flow Task: The product level is insufficient for
component "Source - B11Tbl" (1).
(SQL Server Import and Export Wizard)
Can anyone explain why it 'blows-up' and what I might do to fix it?
Thank you!Hi Mitch
Which version are you running?
John
"Mitch" wrote:

> I am making the adjustment from SQL Server 2000 to SQL Server 2005. In th
e
> past I have created named ranges in Excel that look like data tables (head
er
> row followed by multiple data rows) and used DTS to create database tables
in
> SQL Server 2000. It has worked for several years. Now, using the SQL
> Server 2005 import/export wizard, the same named ranges cause the followin
g
> error.
> - Validating (Error)
> Messages
> Error 0xc00470fe: Data Flow Task: The product level is insufficient f
or
> component "Source - B11Tbl" (1).
> (SQL Server Import and Export Wizard)
> Can anyone explain why it 'blows-up' and what I might do to fix it?
> Thank you!
>|||Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
I hope this gives the version info needed.
Mitch
"John Bell" wrote:
> Hi Mitch
> Which version are you running?
> John
> "Mitch" wrote:
>|||Hi
That does not tell me the edition!
You may want to test if it works with SP1.
John
"Mitch" wrote:
> Microsoft SQL Server Management Studio 9.00.1399.00
> Microsoft Analysis Services Client Tools 2005.090.1399.00
> Microsoft Data Access Components (MDAC) 2000.085.1117.00
> (xpsp_sp2_rtm.040803-2158)
> Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
> Microsoft Internet Explorer 6.0.2900.2180
> Microsoft .NET Framework 2.0.50727.42
> Operating System 5.1.2600
> I hope this gives the version info needed.
> Mitch
> "John Bell" wrote:
>|||Microsoft SQL Server Standard Edition
"John Bell" wrote:
> Hi
> That does not tell me the edition!
> You may want to test if it works with SP1.
> John
> "Mitch" wrote:
>|||Hi Mitch
Then I can't see why you are getting this message! Have you tried this
without the named ranges or a different version of Excel?
John
"Mitch" wrote:
> Microsoft SQL Server Standard Edition
> "John Bell" wrote:
>|||I'll have the IT people check the installation settings for SQL Server 2005.
The 2000 version of DTS still works just fine... The problem is only with
the 2005 version. Thank you for your help!
"John Bell" wrote:
> Hi Mitch
> Then I can't see why you are getting this message! Have you tried this
> without the named ranges or a different version of Excel?
> John
> "Mitch" wrote:
>|||Hi Mitch
SQL 2005 has most thing off by default, but I can't remember having to
change anything special for excel files, although I haven't tried named
ranges.
John
"Mitch" wrote:
> I'll have the IT people check the installation settings for SQL Server 200
5.
> The 2000 version of DTS still works just fine... The problem is only with
> the 2005 version. Thank you for your help!
> "John Bell" wrote:
>|||I get the error message several seconds after identifying the source and it
does not progress (show any more windows) from that point. The message
suggests that it is a Microsoft Jet Database Engine error message. We
watched the server during the attempt (several times) and absolutely nothing
is happening on the server end (SQL Server). My workstation has 2.0 GB RAM
so I doubt that RAM is a problem. The funny part is that the data transfer
works using SQL Server 2000 (which I no longer have installed). Any
suggestions?
"John Bell" wrote:
> Hi Mitch
> SQL 2005 has most thing off by default, but I can't remember having to
> change anything special for excel files, although I haven't tried named
> ranges.
> John
>
> "Mitch" wrote:
>|||Hi
It does sound like a driver issue, I have tested importing named ranges
without any issues.
John
"Mitch" wrote:
> I get the error message several seconds after identifying the source and i
t
> does not progress (show any more windows) from that point. The message
> suggests that it is a Microsoft Jet Database Engine error message. We
> watched the server during the attempt (several times) and absolutely nothi
ng
> is happening on the server end (SQL Server). My workstation has 2.0 GB RA
M
> so I doubt that RAM is a problem. The funny part is that the data transfe
r
> works using SQL Server 2000 (which I no longer have installed). Any
> suggestions?
> "John Bell" wrote:
>

New Database Error Message

I am relatively new to SQL Server. I created a new
database with (and without) the new database wizard.
When I get to the end of the wizard, I get an error
message [error 1038] indicating that I cannot use empty
object or column and should use a space instead. The new
database gets created and is listed in the tree diagram.
I get a similar error message when I open the new
database and try and add a new table to it. I am running
on the local server. Anyone have any ideas?
Thanks.
Joe
I have never run across this error but do you have spaces in any of your
object names by any chance?
Andrew J. Kelly SQL MVP
"Joe" <josephmicheli@.sbcglobal.net> wrote in message
news:740101c494d6$7bc4be20$a601280a@.phx.gbl...
> I am relatively new to SQL Server. I created a new
> database with (and without) the new database wizard.
> When I get to the end of the wizard, I get an error
> message [error 1038] indicating that I cannot use empty
> object or column and should use a space instead. The new
> database gets created and is listed in the tree diagram.
> I get a similar error message when I open the new
> database and try and add a new table to it. I am running
> on the local server. Anyone have any ideas?
> Thanks.
> Joe
|||The the new database I created was a default created by
the wizard. The wizard did not allow me to add/modify any
of the objects in the database. I believe it uses
the "model" database that comes with SQL Server 2000. When
I compared the new one to the model, the number of tables
were the same (19) and the table names looked to be the
same.
>--Original Message--
>I have never run across this error but do you have spaces
in any of your[vbcol=seagreen]
>object names by any chance?
>--
>Andrew J. Kelly SQL MVP
>
>"Joe" <josephmicheli@.sbcglobal.net> wrote in message
>news:740101c494d6$7bc4be20$a601280a@.phx.gbl...
new[vbcol=seagreen]
running
>
>.
>
|||I never use the wizard so I wasn't sure what was going on and if maybe you
tried to use spaces in the DB name. You might want to get in the habit of
using commands such as CREATE DATABASE instead. This allows for much more
control and better information if an error occurs. To create a database is
pretty easy. Check out CREATE DATABASE in BooksOnLine for samples and
details.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:089101c494df$69d374a0$a401280a@.phx.gbl...[vbcol=seagreen]
> The the new database I created was a default created by
> the wizard. The wizard did not allow me to add/modify any
> of the objects in the database. I believe it uses
> the "model" database that comes with SQL Server 2000. When
> I compared the new one to the model, the number of tables
> were the same (19) and the table names looked to be the
> same.
> in any of your
> new
> running