Advice on database volume

Hi all,
I'm new to datawarehousing. I've built a DW with two fact tables, each one
will be added with more than 5 to 10 millions rows each day. Do you think
that this volume is compatible with Analysis Services 2005 or do I have to
change the model I use, by creating a fact table for each customer I have
(for instance).
Second question: if the awnser of the previous question is that I can keep
the model I've built, will I have to investigate cube partition ?
thank's.
Jerome
You can work with this volume of data in Analysis Services 2005 without
changing your schema (and I strongly suggest you to not change your
schema!).
You can use partitions to handle this volume of data without having a
single big partition that is hard to process if for whatever reasons
you need to full process your cube.
Be careful with aggregation design - you really need preaggregations on
this volume of data.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Jerome wrote:
> Hi all,
> I'm new to datawarehousing. I've built a DW with two fact tables, each one
> will be added with more than 5 to 10 millions rows each day. Do you think
> that this volume is compatible with Analysis Services 2005 or do I have to
> change the model I use, by creating a fact table for each customer I have
> (for instance).
> Second question: if the awnser of the previous question is that I can keep
> the model I've built, will I have to investigate cube partition ?
> thank's.
> Jerome
|||Hi Marco,
What do you mean exactly by "aggregation design"?
What is exactly preaggregation? Do you know some references on the web?
Thanks
Jerome
"Marco Russo" wrote:

> You can work with this volume of data in Analysis Services 2005 without
> changing your schema (and I strongly suggest you to not change your
> schema!).
> You can use partitions to handle this volume of data without having a
> single big partition that is hard to process if for whatever reasons
> you need to full process your cube.
> Be careful with aggregation design - you really need preaggregations on
> this volume of data.
> Marco Russo
> http://www.sqlbi.eu
> http://www.sqljunkies.com/weblog/sqlbi
>
> Jerome wrote:
>
|||yeah aggregation % in Analysis Services
-Susie
Jerome wrote:[vbcol=seagreen]
> Hi Marco,
> What do you mean exactly by "aggregation design"?
> What is exactly preaggregation? Do you know some references on the web?
> Thanks
> Jerome
>
> "Marco Russo" wrote:
|||yeah % aggregation in analysis services
Jerome wrote:[vbcol=seagreen]
> Hi Marco,
> What do you mean exactly by "aggregation design"?
> What is exactly preaggregation? Do you know some references on the web?
> Thanks
> Jerome
>
> "Marco Russo" wrote:

ADSI hetrogenous queries

Hi guys
I think I'm going insane, as I'm having a problem with something I've never
had issues with before: I'm moving a database to a new server, but the
destination server also requires the ADSI distributed server to be configured.
So far as I remember it, this is what I thought I needed to do:
1. Create the linked server definition and password:
EXEC sp_addlinkedserver 'ADSI', 'Directory Services', 'ADSDSOOBJECT',
'dsadatasource'
go
EXEC sp_addlinkedsrvlogin ADSI, 'false', NULL, 'domain\account', 'password'
go
2. Execute a query such as:
SELECT *
FROM OPENQUERY(ADSI,
'<LDAP://DC=SOME,DC=DOMAIN,DC=COM>;(&(objectClass=user)(sAM AccountName=jbloggs));ADsPath;subTree')
When I run this on my original sever (I even ran the link creation steps
creating an ADSI2 connection for test purposes) the query returns the
expected row. When I run it on the new machine, I get the following error:
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'ADSDSOOBJECT'.
OLE DB error trace [OLE/DB Provider 'ADSDSOOBJECT' ICommandText::Execute
returned 0x80040e22].
The above two steps are how I successfully configured the original server.
I thought this may have been some form of COM issue, but when I compared the
configuration of both machine (assuming I haven't missed anything obvious)
they looked the same.
The domain account I'm using for the directory authentication is the same
one as the MSSSQLSERVICE service runs under, which in addition is a local
administrator.
I'm not really expecting an answer on this as it seems terribly cryptic, but
if you have any suggestions they'd be most welcome
Cheers
Lain
> Hi guys
> I think I'm going insane, as I'm having a problem with something I've
never
> had issues with before: I'm moving a database to a new server, but the
> destination server also requires the ADSI distributed server to be
configured.
> So far as I remember it, this is what I thought I needed to do:
> 1. Create the linked server definition and password:
> EXEC sp_addlinkedserver 'ADSI', 'Directory Services', 'ADSDSOOBJECT',
> 'dsadatasource'
> go
> EXEC sp_addlinkedsrvlogin ADSI, 'false', NULL, 'domain\account',
'password'
> go
> 2. Execute a query such as:
> SELECT *
> FROM OPENQUERY(ADSI,
>
'<LDAP://DC=SOME,DC=DOMAIN,DC=COM>;(&(objectClass=user)(sAM AccountName=jblog
gs));ADsPath;subTree')
> When I run this on my original sever (I even ran the link creation steps
> creating an ADSI2 connection for test purposes) the query returns the
> expected row. When I run it on the new machine, I get the following error:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'ADSDSOOBJECT'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOOBJECT' ICommandText::Execute
> returned 0x80040e22].
> The above two steps are how I successfully configured the original server.
> I thought this may have been some form of COM issue, but when I compared
the
> configuration of both machine (assuming I haven't missed anything
obvious)
> they looked the same.
> The domain account I'm using for the directory authentication is the same
> one as the MSSSQLSERVICE service runs under, which in addition is a local
> administrator.
> I'm not really expecting an answer on this as it seems terribly cryptic,
but
> if you have any suggestions they'd be most welcome
> Cheers
> Lain
Check your OLE DB provider options and make sure "AllowInProcess" is turned
on:
HKLM Software\Microsoft\MSSQLServer\Providers\ADSDSOObj ect -
AllowInProcess = 1
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thank you Eric. Perfecto!

ADOMD

Hello:
I've installed ADOMD.NET and I've tried to connect Analysis Service with
ASP.NET (using VB.NET). I receive errors but I don't know how I must use
ADOMD.NET. Can somebody help me and show me how I begin?
Thanks.
Regards.
look at this:
http://www.sqlserveranalysisservices...tGridIntro.htm
"Gema Snchez" <gema.sanchez@.grupo-episteme.com> wrote in message
news:OaL%23V8qYFHA.980@.TK2MSFTNGP12.phx.gbl...
> Hello:
> I've installed ADOMD.NET and I've tried to connect Analysis Service with
> ASP.NET (using VB.NET). I receive errors but I don't know how I must use
> ADOMD.NET. Can somebody help me and show me how I begin?
> Thanks.
> Regards.
>

ADO/VB6: Creating a primary key

Hi all,
I've narrowed down my problem to a key issue. If I don't create a key, then
ADO is happy. If I do, then I know get a complaint. I've whipped up the
code fragment below to illustrate my problem. I'm using ADO v2.8, although
v2.7 has the same problem.
Dim dbtblSQL as New ADOX.Table
Dim dbkeySQL as New ADOX.Key
With dbtblSQL
.Name = "New Table"
.Columns.Append "Col1", adInteger, 0
.Columns.Append "Col2", adInteger, 0
.Columns.Append "Col3", adInteger, 0
With dbkeySQL
.Name = "MyKey"
.Type = adKeyPrimary
.Columns.Append dbtblSQL.Columns("Col1") 'Problem line 1
.Columns("Col1").RelatedColumn = "Col1"
End With
.Keys.Append dbkeySQL
End With
Call dbcatSQL.Tables.Append(dbtblSQL)
VB6 now complains on problem line 1 stating "Object already in collection.
Cannot append", but If I don't "append", then the item really isn't in the
collection.
The question boils down to how to create a primary key related to a column
in a table, where the column already exists. Thanks in advance.
Martin A. Weinberger
ButterflyVista
http://www.butterflyvista.com/
(when responding to me by email, remove the "X_" from the beginning)
Hi Again,
I also tried the single line method. The code below still produces a "The
parameter is incorrect."
objTable.Columns("PrimaryKey_Field").Properties("A utoIncrement").Value =
True
objTable.Keys.Append "PrimaryKey", adKeyPrimary, "PrimaryKey_Field"
Martin A Weinberger
ButterflyVista
Please remove the x_ to send me an email directly.
|||I found the problem thanks to some outside help. :-) The problem turned out
to be that when you use the AutoIncrement attribute, you must append the
table before you can create keys on it. This little MS ommission cost me a
few days, but at least I learnt something, so I thought that I pass it on.
Martin A Weinberger
ButterflyVista
Please remove the x_ to send me an email directly.

ADO.NET connection to SQL fails

Hello,
I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do an insert to can't validate my login. I've verified that the account does have permissions to access the SQL DB and that everything is granted as far as the INSERT, UPDATE
, etc...
I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and Windows).
I've tried all the suggestions from the "connectionstrings.com" website and I'm still getting errors stating that the login failed.
Here is a copy of the error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
The above error is happening when I use the connection string of:
"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
I get the same thing when I use "Integrated Security=SSPI" as well.
Here is the error I get when I try to use a user id:
Login failed for user '{user name}'.
The funny thing about it is that the user name specified above is the DBO and it is still rejected.
One last question; have you ever wanted to give up with computers and go back to pen and paper?
Any futher suggestions would be really great! I really appreciate it...
Dale
DBO is not a login, it is a user.
Try connecting with sa (and the sa password). I am guessing that you =
will have better results.
NOTE: You should probably create a login that you will use to connect to =
the database from your web app. If you are planning on using stored =
procedures (a good idea, by the way) they will not need any permissions =
in the database (other than "execute" on the stored procedures used by =
your app).
--=20
Keith
"Dale" <anonymous@.discussions.microsoft.com> wrote in message =
news:1025EFC0-546F-4F76-96CD-75B12AA30E03@.microsoft.com...
> Hello,=20
>=20
> I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm =
trying to do an insert to can't validate my login. I've verified that =
the account does have permissions to access the SQL DB and that =
everything is granted as far as the INSERT, UPDATE, etc...=20
>=20
> I have my SQL Server (SQL2K) set up for mixed authentication (both SQL =
and Windows).=20
>=20
> I've tried all the suggestions from the "connectionstrings.com" =
website and I'm still getting errors stating that the login failed.=20
>=20
> Here is a copy of the error:=20
> Login failed for user '(null)'. Reason: Not associated with a trusted =
SQL Server connection.=20
>=20
> The above error is happening when I use the connection string of:=20
> "server=3DMyServer;Initial Catalog=3DMyDatabase;Integrated =
Security=3DTRUE"=20
>=20
> I get the same thing when I use "Integrated Security=3DSSPI" as well.=20
>=20
> Here is the error I get when I try to use a user id:=20
> Login failed for user '{user name}'.=20
>=20
> The funny thing about it is that the user name specified above is the =
DBO and it is still rejected.=20
>=20
> One last question; have you ever wanted to give up with computers and =
go back to pen and paper? =20
>=20
> Any futher suggestions would be really great! I really appreciate =
it...=20
>=20
> Dale
|||The login failed for user NULL indicates that the account that is
attempting to make the connection is unknown to SQL Server. Look at the IIS
configuration to verify that the user that IIS is using has a login at the
SQL Server. If you are using anonymous access and the IIS machine is on a
separate machine the NT authentication will generate the Login failed for
user NULL.
I would look at articles:
PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'"
http://support.microsoft.com/?id=307002
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/?id=247931
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||The problem you are having may be related to how you've configured
your asp.net application. The app may in fact be attempting to connect
using the aspnet process model account unless you've configured IIS to
use impersonation. There's a pretty good "asp.net security best
practices" whitepaper that bears reading:
http://www.microsoft.com/downloads/r...eleaseID=44047
Your machine.config file has the settings you are using in the
<processModel> section -- the default is to have userName="machine",
which means that the aspnet windows account is being used.
In the meantime, try enabling the aspnet windows login in SQLS and
give it the necessary database access. This should work with the
integrated security=sspi setting in your connection string.
--Mary
On Fri, 16 Apr 2004 10:06:03 -0700, "Dale"
<anonymous@.discussions.microsoft.com> wrote:

>Hello,
>I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do an insert to can't validate my login. I've verified that the account does have permissions to access the SQL DB and that everything is granted as far as the INSERT, UPDAT
E, etc...
>I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and Windows).
>I've tried all the suggestions from the "connectionstrings.com" website and I'm still getting errors stating that the login failed.
>Here is a copy of the error:
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
>The above error is happening when I use the connection string of:
>"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
>I get the same thing when I use "Integrated Security=SSPI" as well.
>Here is the error I get when I try to use a user id:
>Login failed for user '{user name}'.
>The funny thing about it is that the user name specified above is the DBO and it is still rejected.
>One last question; have you ever wanted to give up with computers and go back to pen and paper?
>Any futher suggestions would be really great! I really appreciate it...
>Dale

ADO.NET 2.0 Slows SP Execution By Large Factor

I'm hoping someone will be able to point me in the right direction for solving this problem as i've come a bit stuck.

The Sql Server 2005 Stored Procedure runs in about 3 secs for a small table when run from SQL Management Studio (starting withdbcc freeproccache before execution) but times out when run through ADO.NET on .NET (45 sec timeout).

I've made sure the connection was closed prior to opening and executing the adapter. I'm a bit stuck as where to check next though.

Any ideas greatfully received, Thanks

Can you give us something more to go on? If the stored procedure text is relatively short, can you post it with related tables/indexes and such?

|||

Could you please make sure that you are connected to the Sql Server before making call to that stored procedure... Just try to execute a small query with a top clause.

The problem seems with Connection

|||

Unfortunately the SP is fairly complicated at about 200 lines in all. In summary it comprises of two joins. One is a highly indexed Account table and the other is a YearToDate table.

The output is basically a list of regions and all the Revenue that they've generated over period X to Y.

Here's my data tier code. The bottleneck seems to be the last line as that is the part that takes ages when i'm stepping through in debug mode

ssCommand.CommandType =CommandType.StoredProcedure;
ssCommand.CommandText = "my_stored_proc";
ssCommand.Connection =newSqlConnection("my_con_str");
ssCommand.CommandTimeout = 45;
foreach (SqlParameter ssParamin mySqlParmCollection){
ssCommand.Parameters.Add(ssParam);
}
ssCommand.Connection.Open();
ssDataAdapter =newSqlDataAdapter(ssCommand);
ssDataAdapter.Fill(ssDataSet, sDataTableName);

Many thanks,

|||

The nice thing would be to check the Sql Profiler... By this way you will be able to know whether its hitting the Sql Server or not.. and it will narrow down the problem. Then you can see why its taking so long

|||

I've just done a trace and the execution took almost 45 secs to complete using ADO.NET

I then ran it through query analyser and it took 3 secs.

I'm very puzzled by all of this, I could understand the execution plans not being used though .NET but they were cleared whilst executing them using sql management..

Your help is much appreciated,

Matt

|||

This is a excerpt from one of the msdn forum ..

I had the same problem. Seems like the SQL execution plan was corrupted for the stored procedure. I created a new SP and and copied the code to the new SP from the old and ran the application and the data was returned in no time. This proves that this was a SQL server issue and probably the execution plan for the SP was not efficient when it is run from ADO.NET. The reason that the SP worked from the query analyser and not the application using ADO.NET is QA directly executes the SP on the server but ADO.NET internally executes the sp by calling sp_executesql. The dropping and recreating the SP from the database should be a quick fix for the future. It may be worth while to find out WHY this condition happens after a while on the database.

Here are some information from the research I did on this for your reference:
1) "SqlCommand.ExecuteReader executes all commands in the context of the sp_executesql stored procedure." (http://msdn2.microsoft.com/en-us/library/aa720629(VS.71).aspx)
2) "Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table. You will notice this problem quite often when you are working with temporary tables in SQL Server 7.0 as SQL Server will determine that after 6 modifications to a temporary table any stored procedure referencing that table will need to be recompiled."
(http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp)
3) "You may have heard about a system stored procedure called sp_executesql. It lets you evaluate dynamic SQL, but it happens to also cache its execution plan."
(http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/controlling-stored-procedure-caching-with-dyanmic-sql.aspx)
4) "The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is always recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure, because in this case the wrong execution plan will not be used. "
(http://www.databasejournal.com/features/mssql/article.php/1565961)

Check the link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1102019&SiteID=1

and try the solutions mentioned

ADO Stream from Stored Procedure with JScript fails mysteriously

Hi all, I've seen a couple of references to this problem, but only one
solution which had no appreciable effect.
I have a stored procedure which uses FOR XML EXPLICIT. The stored
procedure works quite happily and I can access the DB perfectly well
using VBScript.
Being of a religious bent, I'd rather not use VBScript.
I have the following *working* function, ported directly from the
broken JScript version to VBScript. The Jscript version fails on
Execute telling me that my procedure wasn't expecting any parameters.
Handy that, 'cos it doesn't get any.
I've tried using oCmd.Execute(0,0,1024),
oCmd.Execute(null,null,0x400), oCmd.Execute('', '', 0x404) without a
commandType, oCmd.Execute(N, N, 1024) where N is a null variable and
all points in between.
The error varies amusingly if I give it a nonsensical constant, but
provided I stick to something sensible, I get an 0x80040E21 error.
Finally, there are no differences between the two versions other than
the obvious ones.
Any idea how I can get this working in JScript?
******** begin code ********
function fetchXML()
set oCmd = Server.CreateObject("ADODB.Command")
oCmd.CommandType = 4
oCmd.CommandText = "prcMenu_FetchMenu"
set stmOut = Server.CreateObject("ADODB.Stream")
stmOut.Open()
set cn = getConnection()
oCmd.ActiveConnection = cn
oCmd.Properties("Output Stream") = stmOut
oCmd.Execute , , 1024
set cn = nothing
set oCmd = nothing
sOut = "<menu>"+stmOut.ReadText()+"</menu>"
set stmOut = nothing
fetchXML = sOut
End Function
******** end code *******
Cheers,
-- Bob
0x80040E21 is DB_E_ERRORSOCCURRED. Its description is: "Multiple-step
operation generated errors. Check each status value. No work was done."
Check your stored procedure text.
This procedure works (MS SQL Server 2000, local, database pubs, Windows
security):
//----
var conn = new ActiveXObject("ADODB.Connection");
conn.Open("Provider=SQLOLEDB;Integrated Security=SSPI;Initial
catalog=pubs");
var cmd = new ActiveXObject("ADODB.Command");
cmd.ActiveConnection = conn;
cmd.CommandText = "SELECT * FROM authors FOR XML AUTO";
var stream = new ActiveXObject("ADODB.Stream");
stream.Open();
cmd.Properties("Output Stream") = stream;
cmd.Execute(null, null, 0x400);
WScript.Echo(stream.ReadText());
//----
//--
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE
"Bob Gregory" <bobgregory@.ppsltd.net> ??/?? ? ?? ??:
news:aba9c4c4.0407200859.7a64e94b@.posting.google.c om...
> Hi all, I've seen a couple of references to this problem, but only one
> solution which had no appreciable effect.
> I have a stored procedure which uses FOR XML EXPLICIT. The stored
> procedure works quite happily and I can access the DB perfectly well
> using VBScript.
> Being of a religious bent, I'd rather not use VBScript.
> I have the following *working* function, ported directly from the
> broken JScript version to VBScript. The Jscript version fails on
> Execute telling me that my procedure wasn't expecting any parameters.
> Handy that, 'cos it doesn't get any.
> I've tried using oCmd.Execute(0,0,1024),
> oCmd.Execute(null,null,0x400), oCmd.Execute('', '', 0x404) without a
> commandType, oCmd.Execute(N, N, 1024) where N is a null variable and
> all points in between.
> The error varies amusingly if I give it a nonsensical constant, but
> provided I stick to something sensible, I get an 0x80040E21 error.
> Finally, there are no differences between the two versions other than
> the obvious ones.
> Any idea how I can get this working in JScript?
> ******** begin code ********
> function fetchXML()
> set oCmd = Server.CreateObject("ADODB.Command")
> oCmd.CommandType = 4
> oCmd.CommandText = "prcMenu_FetchMenu"
> set stmOut = Server.CreateObject("ADODB.Stream")
> stmOut.Open()
> set cn = getConnection()
> oCmd.ActiveConnection = cn
> oCmd.Properties("Output Stream") = stmOut
> oCmd.Execute , , 1024
> set cn = nothing
> set oCmd = nothing
> sOut = "<menu>"+stmOut.ReadText()+"</menu>"
> set stmOut = nothing
> fetchXML = sOut
> End Function
> ******** end code *******
> Cheers,
> -- Bob
|||"Viatcheslav V. Vassiliev" <msnewsgroup@.www-sharp.com> wrote in message news:<O63yoqobEHA.4092@.TK2MSFTNGP10.phx.gbl>...
> 0x80040E21 is DB_E_ERRORSOCCURRED. Its description is: "Multiple-step
> operation generated errors. Check each status value. No work was done."
> Check your stored procedure text.
> This procedure works (MS SQL Server 2000, local, database pubs, Windows
> security):
<snip />
I submitted the system in VBScript thanks to deadline constraints so
this is now a purely academic issue. I know the stored proc is
absolutely fine, because I return useful results from VBScript with
the same procedure. The only difference is the language, and
presumably the underlying mechanics of speaking to COM therewith.
-- Bob

Administrators

I would like that local administrators or domain administrators dont have
administrative rights on some databases running with MSDE.
I've learned about to enable the user "sa" and to give a personal password
to this user, but apparently administrators continue having full access to
all databases.
Thanks.
Regards.
Jose Nuez
Montevideo - Uruguay
hi Jose,
"Jose Nuez" <josenunez70@.hotmail.com> ha scritto nel messaggio
news:OOeUxFovEHA.164@.TK2MSFTNGP10.phx.gbl
> I would like that local administrators or domain administrators dont
> have administrative rights on some databases running with MSDE.
> I've learned about to enable the user "sa" and to give a personal
> password to this user, but apparently administrators continue having
> full access to all databases.
> Thanks.
> Regards.
> Jose Nuez
> Montevideo - Uruguay
Hari already answered you in june.. http://tinyurl.com/4638l and gave you
additional pointers to troubles you could experience..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply