Reformatting ResultSet

I'm using ADO.Net to return a set of records from a SQL Server, which I
then process to create a text stream ultimately used as the InnerHTML
property of a browser control in a C# project. Please don't ask why I'm
doing it that way. I know it's stupid, but customer requirements dictate
that it be done that way. Here's my question. Assume I get the following
return from the SQL query:
KeyType ILS KeyVal 1 <ILS F1>Somevalue</ILS F2><ILS F2>Somevalue</ILS
F2>...
KeyType PARTS KeyVal 2 <PARTS F1>Somevalue</PARTS F1><PARTS
F2>Somevalue</PARTS F2>...
KeyType ILS KeyVal 3 <ILS F1>Somevalue</ILS F2><ILS
F2>SomeSpecificvalue</ILS F2>...
After processing, this yields:
<ILS Records>
<ILS>KeyVal 1
<ILS F1>Somevalue</ILS F1>
<ILS F2>Somevalue</ILS F2>
</ILS>
<ILS match='true'>KeyVal 3
<ILS F1>Somevalue</ILS F1>
<ILS F2><b>SomeSpecificvalue</b></ILS F2>
</ILS>
</ILS Records>
<PARTS Records>
<PARTS>KeyVal 2
<PARTS F1>Somevalue</PARTS F1>
<PARTS F2>Somevalue</PARTS F2>
</PARTS>
</PARTS Records>
The result above is funneled through an XML stylesheet, which renders
exactly the way the customer wants it to. There are a whole lot of ifs,
ands, and buts associated with how each record is formatted, its weight, its
type, its priority, query parameters, etc.. The SQL Server returns the
result set into the datareader object very quickly. Does it make sense to
put the application specific formatting logic into stored procedures, or
would it make more sense to leave the record level formatting in the C#
code?> Does it make sense to
> put the application specific formatting logic into stored procedures, or
> would it make more sense to leave the record level formatting in the C#
> code?
I'd leave it in c# code. TSQL isn't the best language in the world for these
types of things. I
think that your code will be more readable, manageable and efficient as c# c
ode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"bigbob" <big@.bob.com> wrote in message news:eAn$3a9AFHA.1400@.TK2MSFTNGP11.phx.gbl...[color
=darkred]
> I'm using ADO.Net to return a set of records from a SQL Server, which I
> then process to create a text stream ultimately used as the InnerHTML
> property of a browser control in a C# project. Please don't ask why I'm
> doing it that way. I know it's stupid, but customer requirements dictate
> that it be done that way. Here's my question. Assume I get the following
> return from the SQL query:
> KeyType ILS KeyVal 1 <ILS F1>Somevalue</ILS F2><ILS F2>Somevalue</ILS
> F2>...
> KeyType PARTS KeyVal 2 <PARTS F1>Somevalue</PARTS F1><PARTS
> F2>Somevalue</PARTS F2>...
> KeyType ILS KeyVal 3 <ILS F1>Somevalue</ILS F2><ILS
> F2>SomeSpecificvalue</ILS F2>...
> After processing, this yields:
> <ILS Records>
> <ILS>KeyVal 1
> <ILS F1>Somevalue</ILS F1>
> <ILS F2>Somevalue</ILS F2>
> </ILS>
> <ILS match='true'>KeyVal 3
> <ILS F1>Somevalue</ILS F1>
> <ILS F2><b>SomeSpecificvalue</b></ILS F2>
> </ILS>
> </ILS Records>
> <PARTS Records>
> <PARTS>KeyVal 2
> <PARTS F1>Somevalue</PARTS F1>
> <PARTS F2>Somevalue</PARTS F2>
> </PARTS>
> </PARTS Records>
> The result above is funneled through an XML stylesheet, which renders
> exactly the way the customer wants it to. There are a whole lot of ifs,
> ands, and buts associated with how each record is formatted, its weight, i
ts
> type, its priority, query parameters, etc.. The SQL Server returns the
> result set into the datareader object very quickly. Does it make sense to
> put the application specific formatting logic into stored procedures, or
> would it make more sense to leave the record level formatting in the C#
> code?
>[/color]|||Thanks, Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%232FLMK%23AFHA.1188@.tk2msftngp13.phx.gbl...
> I'd leave it in c# code. TSQL isn't the best language in the world for
these types of things. I
> think that your code will be more readable, manageable and efficient as c#
code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "bigbob" <big@.bob.com> wrote in message
news:eAn$3a9AFHA.1400@.TK2MSFTNGP11.phx.gbl...
its
to
>

Reference Dimension causing long processing times

I have a reference dimension that is linked to a regular dimension that is linked to the fact table. For some reason, when I process the database with some small sample data, it take about 5 minutes to process, where it should take 10 seconds. I am using a reference dimension because it is a child-parent hierarchy, and I don't think I can merge it into the regular dimension. My work-around is to just include the link to this reference dimension in the fact table, so I can create the child-parent dimension as regular. Now is processes in 10 seconds. It doesn't seem ideal since I have what seems to me an unessary dimension now.

If I turn "materialize" off, it processes in 10 seconds, but I don't know the implications of this.

Does anyone have any idea of what is going on or have any advice?

Thanks,

Ernie

Are you using a referenced dimension here because the parent column is in another table from the child column? If so, you could create a view on top of them that encapsulates the relationship. What are the other issues you see regarding not being able to merge these columns into a regular dimension?|||

Thanks for the reply Scott.

The child and parent are in the same view. I've created another dimiension because the child attribute has to be a key. The child attribute, or key of this dimension, is not what joins directly to the fact table. There is an intermediate "field" that has to create this link, which is a foriegn key in the regular dimension, which has a key to link to the fact table. Hmmm, did I say that right? I may not be explaining things well, as I'm a bit new at this. Anyway, this is why I "think" I can't merge them.

Maybe this is a valid example:

Fact table has column SubAccount

SubAccount table has Account column (multiple sub-accounts per account)

Account table has AccountParent column which created the child parent relationship.

In this example, I would create a regular SubAccount dimension and a reference dimension called Account. My "workaround" is to put both the Account and SubAccount columns in the Fact table (or view).

Ernie

|||

It sounds like you should merge the sub account and account into a single dimension (make it a parent-child). You'll use a snowflake schema which will describe the join between the account and sub account tables as a source for the dimension, yet only have a single dimension.

Without doing this, if you tried going down the road of having two account dimensions (subaccount and account), then you'd end up with cases where you would get confusing results (like subaccounts that are not really related to an account showing up together, etc.)

Hope that helps

|||

Scott (or anyone else):

I am running into a problem that seems to be related to the one described by Ernie above. Here is the situation:

1) I have a referenced dimension (dimA) that joins to the fact table (factB) through an intermediate dimension table (dimC).

2) The referenced dimension (dimA) has a parent-child heirarchy. The primary key of this table (keySelf) and the parent key (keyParent) are in the same table (dimA) i.e. the parent-child hierarchy is stored in the same table and not in a separate table.

3) I am able to deploy the cube and it gives correct results.

4) I am currently in the design stage so I am dealing with fairly small data sets. Therefore, I donot know if it is performing poorly during deployment.

5) My problem occurs when I try to design aggregations. I get the error at the end of the wizard when I am deploying the aggregations. The error is on the Measure group that is based on factB. The error states:

"Errors in the high-level relational engine. The binding for the 'standardId' column is not a ColumnBinding type."

Note that 'standardId' is the primary key (keySelf) in the dimA table. i.e. it is the key that maps factB to dimA (through dimC).

6) The error goes away if I uncheck the "Materialize" option in the Define Relationship window for the relationship between factB and dimA.

7) I have other referenced dimensions in the cube that relate to factB through dimC that do not have a parent-child hierarchy and they do not give this problem.

So it seems that if a referenced dimension has a parent-child hierarchy and is materialized then the aggregations run into problem. I realize that I can eliminate the issue by making dimA a regular dimension of factB but I am curious to understand why this is happening.

Is this expected behaviour or am I doing something wrong?

Thanks beforhand.

Regards,

Amardeep

|||

Amardeep, this sounds like it could be a bug that you're hitting. I'll forward this on to our test team to verify.

One thing you could do to work around the issue that you're seeing though (since not materializing the dimension will be slower at query time) would be to not use a referenced dimension and instead use a regular dimension. To do this you'll need to modify your data source view (DSV) to include additional relationships in order to allow the join to work properly.

Hope this helps.

reduce trans log size; help needed

Hello,
I am hoping you can help me with the following problem; I need to process
the following steps every couple of hours in order to keep our Sql 2000
database a small as possible (the transaction log is 5x bigger than the db).
1.back-up the entire database
2.truncate the log
3.shrink the log
4.back-up once again.
As you may have determined, I am relatively new to managing a sql server
database and while I have found multiple articles online about the topics I
need to accomplish, I cannot find any actual examples that explain where I
input the coded used to accomplish the above-mentioned steps. I do
understand the theory behind the steps I just do not know how to accomplish
them!
If you know of a well-documented tutorial (Aside from Books Online (F1)),
please point me in the right direction.
Regards.| Hello,
|
| I am hoping you can help me with the following problem; I need to process
| the following steps every couple of hours in order to keep our Sql 2000
| database a small as possible (the transaction log is 5x bigger than the
db).
|
| 1.back-up the entire database
| 2.truncate the log
| 3.shrink the log
| 4.back-up once again.
|
| As you may have determined, I am relatively new to managing a sql server
| database and while I have found multiple articles online about the topics
I
| need to accomplish, I cannot find any actual examples that explain where I
| input the coded used to accomplish the above-mentioned steps. I do
| understand the theory behind the steps I just do not know how to
accomplish
| them!
--
You can create a job that executes the above steps every couple of hours.
You can define each of the above steps as actual job steps with the job.
You can use Enterprise Manager to create this job.
Hope this helps,
--
Eric Cárdenas
SQL Server support