sql dwh problem: rows

Showing posts with label rows. Show all posts

Sunday, March 11, 2012

Dynamic formatting of table columns

I have a table with a few columns in my report and I would like to make some
of the rows show up in bold in the report if the value in one of the column
is within a particular range. Can I do this using Expressions. If so, can
someone post a sample expression or let me know if there is another way to do
this. Thanks in advance.
PremYes,
You can do it using expressions.
Goto row property ->font->Fontweight
Then there you can write expression
like
=iif( Fields!XYZ.Value>0,"Bold","Normal")
Malkesh
"Prem" wrote:
> I have a table with a few columns in my report and I would like to make some
> of the rows show up in bold in the report if the value in one of the column
> is within a particular range. Can I do this using Expressions. If so, can
> someone post a sample expression or let me know if there is another way to do
> this. Thanks in advance.
> Prem
>

Friday, March 9, 2012

Dynamic Dynamic Filters

Hello,
I am attempting to use dynamic filters on merge replication to filter rows. In my front end app I am using the ActiveX Merge object to set the Host_Name property. My question is: Is it possible to set the host name to a more complex string?
example:
.HostName = "Col = 'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
The problem I am running into is that the sql wizard for creating the dynamic filters runs a check on the sql statement and won't allow something like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
Technically if I could somehow bypass the wizard to enter the filter rules this should work. Anyone know if this is possible? Or possibly another way to work around this so that you can pass more than 1 value to the where clause? I suppose you could do so
mething like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = Host_Name()
HostName = "'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
Would this be the only way? Any feedback would be greatly appreciated. Thanks in advance.
I'm unsure on what your filtering condition is. For instance your filter
should evaluate to a boolean true or false. Yours evaluates to hostname.
You can use a UDF to extend the functionalty of your filter or you can do
stuff like this
SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() like 'p%'
or use a subquery
SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() in (select
Servername from ServerList where state='ca')
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:D7626D05-A0A6-4F19-963D-8B588A20AC6E@.microsoft.com...
> Hello,
> I am attempting to use dynamic filters on merge replication to filter
rows. In my front end app I am using the ActiveX Merge object to set the
Host_Name property. My question is: Is it possible to set the host name to a
more complex string?
> example:
> .HostName = "Col = 'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
> The problem I am running into is that the sql wizard for creating the
dynamic filters runs a check on the sql statement and won't allow something
like:
> SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
> Technically if I could somehow bypass the wizard to enter the filter rules
this should work. Anyone know if this is possible? Or possibly another way
to work around this so that you can pass more than 1 value to the where
clause? I suppose you could do something like:
> SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = Host_Name()
> HostName = "'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
> Would this be the only way? Any feedback would be greatly appreciated.
Thanks in advance.
>
>
|||Yes mine purposely evaluates to hostname. What I am trying to do is set the filter to:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
and then set hostname to something like:
Col = 'Val1' OR Col = 'Val2'
So that the resulting select will look like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = 'Val1' OR Col = 'Val2'
Or whatever other complex WHERE clause that I choose. The problem is that it won't except the above filter. My work around listed above should work (I have yet to test it). Although I am interested in other possibilites to achieve the same results. This s
eems fairly basic unless I am missing something?
"Hilary Cotter" wrote:

> I'm unsure on what your filtering condition is. For instance your filter
> should evaluate to a boolean true or false. Yours evaluates to hostname.
> You can use a UDF to extend the functionalty of your filter or you can do
> stuff like this
> SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() like 'p%'
> or use a subquery
> SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() in (select
> Servername from ServerList where state='ca')
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:D7626D05-A0A6-4F19-963D-8B588A20AC6E@.microsoft.com...
> rows. In my front end app I am using the ActiveX Merge object to set the
> Host_Name property. My question is: Is it possible to set the host name to a
> more complex string?
> dynamic filters runs a check on the sql statement and won't allow something
> like:
> this should work. Anyone know if this is possible? Or possibly another way
> to work around this so that you can pass more than 1 value to the where
> clause? I suppose you could do something like:
> Thanks in advance.
>
>
|||Steve,
you could have a mapping table which relates values (Val1 and Val2) to
hostnames. In your example there would be 2 rows:
-- MAPPINGTABLE --
hostname1 val1
hostname1 val2
If you include this table in the filter, you can achieve the clause you want
to create.
HTH,
Paul Ibison
|||Paul,
I am not familiar with mapping tables and I have been unable to find any information relating to them. Could you provide an example of what you mean or possibly a link to some information? Thanks.
|||Steve,
it's easiest if I explain how I set up a test: I had 2 tables - region and
HostnameLookup - shown below. The HostnameLookup table defines the multiple
values I'm interested in. I use dynamic filters to filter the HostnameLookup
table using HOST_NAME() and a join filter to join to the region table.
Editing the 2nd step on the merge agent's job to include: -Hostname
PaulsComputer means that only 2 regions get replicated. I've listed the
exact text below in case you want to recreate it to test.
HTH,
Paul Ibison
Region
RegionID RegionDescription rowguid
-- -- --
1 Eastern 9A6377F0-70DF-4A5C-962D-B41A41EFA82B
2 Western C0CAEFAD-7B8C-43D6-87ED-920494A5C60C
3 Northern 6CD9AA33-BE2D-4DCF-B447-745A3B86818E
4 Southern B4826E41-96D6-457C-8645-DD4984AE3BF5
HostnameLookup
RegionDescription Hostname rowguid
-- -- --
Northern PaulsComputer 2367C78A-1001-417B-A3B1-1C74B23F8131
Southern PaulsComputer 4F563C4D-8479-4A7D-A938-490DD514F12A
Filter Clause for HostnameLookup:
SELECT <published_columns> FROM [dbo].[HostnameLookup]
WHERE HostnameLookup.Hostname = HOST_NAME()
Filter Clause for Region:
< All rows published >
Join Filter:
Filtered table is HostnameLookup
Table to FIlter is Region
SELECT <published_columns> FROM [dbo].[HostnameLookup]
INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =
region.regiondescription
Edit the 2nd step on the merge agent's job to include: -Hostname
PaulsComputer
Run the snapshot then merge agents and only 2 regions should be replicated.
|||Hi Paul,
I saw your suggested solution in another web page
http://www.replicationanswers.com/Merge.asp
I was wondering if this Host_Name() is a function that will return the
computer name of the device/desktop that the subscriber database is residing
on?
If I am using mobile devices as subscribers and they have a web service
method called Reader_Id() which returns the unique id of the mobile device,
can I modify your solution accordingly?
Thank you.
"Paul Ibison" wrote:

> Steve,
> it's easiest if I explain how I set up a test: I had 2 tables - region and
> HostnameLookup - shown below. The HostnameLookup table defines the multiple
> values I'm interested in. I use dynamic filters to filter the HostnameLookup
> table using HOST_NAME() and a join filter to join to the region table.
> Editing the 2nd step on the merge agent's job to include: -Hostname
> PaulsComputer means that only 2 regions get replicated. I've listed the
> exact text below in case you want to recreate it to test.
> HTH,
> Paul Ibison
> Region
> --
> RegionID RegionDescription rowguid
> -- -- --
> 1 Eastern 9A6377F0-70DF-4A5C-962D-B41A41EFA82B
> 2 Western C0CAEFAD-7B8C-43D6-87ED-920494A5C60C
> 3 Northern 6CD9AA33-BE2D-4DCF-B447-745A3B86818E
> 4 Southern B4826E41-96D6-457C-8645-DD4984AE3BF5
> HostnameLookup
> --
> RegionDescription Hostname rowguid
> -- -- --
> Northern PaulsComputer 2367C78A-1001-417B-A3B1-1C74B23F8131
> Southern PaulsComputer 4F563C4D-8479-4A7D-A938-490DD514F12A
>
> Filter Clause for HostnameLookup:
> SELECT <published_columns> FROM [dbo].[HostnameLookup]
> WHERE HostnameLookup.Hostname = HOST_NAME()
> Filter Clause for Region:
> < All rows published >
> Join Filter:
> Filtered table is HostnameLookup
> Table to FIlter is Region
> SELECT <published_columns> FROM [dbo].[HostnameLookup]
> INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =
> region.regiondescription
> Edit the 2nd step on the merge agent's job to include: -Hostname
> PaulsComputer
> Run the snapshot then merge agents and only 2 regions should be replicated.
>
>

Sunday, February 26, 2012

Dynamic Cursor versus Forward Only Cursor gives Poor Performance

Hello,

I have a test database with table A containing 10,000 rows and a table
B containing 100,000 rows. Rows in B are "children" of rows in A -
each row in A has 10 related rows in B (ie. B has a foreign key to A).

Using ODBC I am executing the following loop 10,000 times, expressed
below in pseudo-code:

"select * from A order by a_pk option (fast 1)"
"fetch from A result set"
"select * from B where where fk_to_a = 'xxx' order by b_pk option
(fast 1)"
"fetch from B result set" repeated 10 times

In the above psueod-code 'xxx' is the primary key of the current A
row. NOTE: it is not a mistake that we are repeatedly doing the A
query and retrieving only the first row.

When the queries use fast-forward-only cursors this takes about 2.5
minutes. When the queries use dynamic cursors this takes about 1 hour.

Does anyone know why the dynamic cursor is killing performance?
Because of the SQL Server ODBC driver it is not possible to have
nested/multiple fast-forward-only cursors, hence I need to explore
other alternatives.

I can only assume that a different query plan is getting constructed
for the dynamic cursor case versus the fast forward only cursor, but I
have no way of finding out what that query plan is.

All help appreciated.

KevinPlease explain what you are trying to do here. Cursors are usually best
avoided and typically perform much less efficiently than set-based
solutions. If you describe the problem in more detail someone should be able
to suggest an alternative that doesn't use a cursor. Post DDL (CREATE TABLE
statements), some sample data (INSERT statements) and show your required
result.

--
David Portas
SQL Server MVP
--

Friday, February 24, 2012

Dynamic connection string

Our Reporting Services environment uses Oracle as the data source. Based upon the user connecting to the database determines what rows they will see for various tables. How can we dynamically pass the username/password to the connection string? Background: Our users log into Active Directory and are assigned to a group. The AD group name is used to access a control table in Oracle that contains the database username/password for that group’s connection to Oracle. All subsequent connections to Oracle will use the group’s username/password from the control table. We have an ASP.NET application that works like this and stores the connection information in the session state. How can we do something similar with our connection in Reporting Services? Note: Our Oracle Database does not use Windows Integration.

You can create a private (report-level) data source that uses an expression-based connection string as the ExpressionBasedConnection report in this download demonstrates.|||Thank you Teo for your prompt reply. I will take a look at this.|||

Teo,

I looked over the files, but I can't say that the answer stands out at me. Can you tell me specifically which part that I should look at?

|||The report needs to be set up for a private data source and the data source connection string is expression-based. Look at the way the dataset data source is configured.|||

Thanks Teo. I looked into this but it does not solve our unique situation. In our reports, we logged onto our database as one user. We have other companies that need to access our reports now, but do not want them to see the same information. We are attempting to get the user information and log into the database to retrieve the password for the group that that person belongs to. Then we would like to pass back the group user name and password in our connection string so that the person running the report only sees the information that they are allowed to. Another developer wrote a DLL to attempt to do this. It passes back in the dll but does not work when we try to display the connection string on the report. Do you have any samples to make this work or any suggestions on how we might do this?

|||

Why wouldn't this approach work in your situation? Can't you call the DLL from the connection string expression in the same way and get the user-specific connection string?

As a side note, the right way probably to address a similar requirement is to pass User!UserID to your database and let the database implememt row-level security based on the user identity. With this approach you don't have to deal with connection strings at all.

|||Our biggest problem is that we need to take the ID from Active Directory and find the groups that they are assigned to. So far we have been unsuccessful in making this work.

Dynamic connection string

Teo,

I looked over the files, but I can't say that the answer stands out at me. Can you tell me specifically which part that I should look at?

|||The report needs to be set up for a private data source and the data source connection string is expression-based. Look at the way the dataset data source is configured.|||

|||

Why wouldn't this approach work in your situation? Can't you call the DLL from the connection string expression in the same way and get the user-specific connection string?

|||Our biggest problem is that we need to take the ID from Active Directory and find the groups that they are assigned to. So far we have been unsuccessful in making this work.

Sunday, February 19, 2012

Dynamic Columns

Hi everybody.
I've to make somthing like a table in RS, but instead to habe dynamic rows,
i would like to have dynamic columns.
I see that it may be possible with matrix, but RS told me that i can only
have grouping expression in my cells, and i don't want it.
Thanks four your help.
--
Nicolas HOOGE
IngÃ©nieur Conception DÃ©veloppement
OMNILOGYes, you can do that. Open the sample project that is installed when you
install Reporting Services. You should find this under your SQL Server
installation folder, for example: c:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\Samples\Reports. open the solution in Visual
Studio then open the report called Company Sales.rdl. You will see that it
has both dynamic rows and dynamic columns. And you will be able to see how
they did it.
HTH
Charles Kangai, MCT, MCDBA
"Nicolas HOOGE" wrote:
> Hi everybody.
> I've to make somthing like a table in RS, but instead to habe dynamic rows,
> i would like to have dynamic columns.
> I see that it may be possible with matrix, but RS told me that i can only
> have grouping expression in my cells, and i don't want it.
> Thanks four your help.
> --
> Nicolas HOOGE
> IngÃ©nieur Conception DÃ©veloppement
> OMNILOG|||thanks
but in this example, you've got a dynamic columns with 2 static rows without
label.
I would like to have to same thing that a table but with 90Â° of rotation !!!
"Nicolas HOOGE" wrote:
> Hi everybody.
> I've to make somthing like a table in RS, but instead to habe dynamic rows,
> i would like to have dynamic columns.
> I see that it may be possible with matrix, but RS told me that i can only
> have grouping expression in my cells, and i don't want it.
> Thanks four your help.
> --
> Nicolas HOOGE
> IngÃ©nieur Conception DÃ©veloppement
> OMNILOG|||If the only grouping you want is sum/totals, in a matrix you can right-click
on the data and select Subtotal.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Nicolas HOOGE" <nicolas.hooge(sansspam)@.omnilog.fr> wrote in message
news:BDE3FADD-C883-4A88-AB30-B85C3D3A16F3@.microsoft.com...
> Hi everybody.
> I've to make somthing like a table in RS, but instead to habe dynamic
> rows,
> i would like to have dynamic columns.
> I see that it may be possible with matrix, but RS told me that i can only
> have grouping expression in my cells, and i don't want it.
> Thanks four your help.
> --
> Nicolas HOOGE
> Ingénieur Conception Développement
> OMNILOG

Dynamic columns

My report has a large number of rows in the details section, This wastes a lot of space. I'd like to spread these rows across multiple columns in the details section of my table.

What i'm currently getting...

ROW1-

ROW2-

ROW3-

ROW4-

ROW5-

ROW6-

ROW7-

ROW*-

ROW9-

What I'd like to see...

ROW1- ROW2- ROW3-

ROW4- ROW5- ROW6-

ROW7- ROW8- ROW9-

Is this possible, if so, how?

SSRS supports multi-column reports.

|||Thanks for your reply, but the example in that link doesnt really suit me. My report has various groups and footer totals that are independent of the "details" columns. I need to, somehow, break the 'details' into 3 columns, while still maintaining my header,subreport, groups, and footer|||

Perhaps a multi-column subreport for the details only then.

|||

Using a Matrix:

Save this as an RDL file and figure out whats going on.

Code Snippet

<?xml version="1.0" encoding="utf-8"?>

<DataSourceReference>AdventureWorks</DataSourceReference>

<rd:DataSourceID>05fe442d-a63b-47f0-9339-790445d10db9</rd:DataSourceID>

</DataSource>

</DataSources>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

</MatrixColumn>

</MatrixColumns>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

<GroupExpression>=Fields!ROWGROUPS.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicRows>

</RowGrouping>

</RowGroupings>

<rd:DefaultName>COLUMNGROUPS</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!COLUMNGROUPS.Value</Value>

</Textbox>

</ReportItems>

<GroupExpression>=Fields!COLUMNGROUPS.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</DynamicColumns>

</ColumnGrouping>

</ColumnGroupings>

<DataSetName>DataSet1</DataSetName>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</Corner>

<rd:DefaultName>ROWDATA</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=First(Fields!ROWDATA.Value)</Value>

</Textbox>

</ReportItems>

</MatrixCell>

</MatrixCells>

</MatrixRow>

</MatrixRows>

</Matrix>

</ReportItems>

</Body>

<rd:ReportID>327962be-8654-4648-a50f-65e063317681</rd:ReportID>

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>SELECT 'ROW1' AS ROWDATA, 1 AS ROWGROUPS, 1 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW2' AS ROWDATA, 1 AS ROWGROUPS, 2 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW3' AS ROWDATA, 1 AS ROWGROUPS, 3 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW4' AS ROWDATA, 2 AS ROWGROUPS, 1 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW5' AS ROWDATA, 2 AS ROWGROUPS, 2 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW6' AS ROWDATA, 2 AS ROWGROUPS, 3 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW7' AS ROWDATA, 3 AS ROWGROUPS, 1 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW8' AS ROWDATA, 3 AS ROWGROUPS, 2 AS COLUMNGROUPS

UNION ALL

SELECT 'ROW9' AS ROWDATA, 3 AS ROWGROUPS, 3 AS COLUMNGROUPS</CommandText>

<DataSourceName>AdventureWorks</DataSourceName>

</Query>

<rd:TypeName>System.String</rd:TypeName>

<DataField>ROWDATA</DataField>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>ROWGROUPS</DataField>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>COLUMNGROUPS</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

Friday, February 17, 2012

dynamic column headings?

Is it possible for Report Services 2000 to accomplish the following. Are there any examples available that i can take a look?

How can I turn rows in a table into column headings in a report. By the way, the user can chose one questions to see the answers for all the Test Takers.

The questions and answers are rows in two seperate tables.

For example, a certain test may have N questions and N answers. I would like to make the questions the column headings and the answers the rows. Into something like this.

Test Taker Questions 1 Questions2 Questions3 QuestionsN+1

Mike Jay Answer1 Answer2 Answer3 AnswerN+1

Jon Doe Answer1 Answer2 Answer3 AnswerN+1

etc....

Can anyone suggest a solution or documentation?

Thank you,

yadgor2000

Try using a matrix. You can easily do this in VS.Net.

Wednesday, February 15, 2012

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..
Hi
http://www.microsoft.com/sql/techinf...calability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..Hi
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..Hi
http://www.microsoft.com/sql/techin...scalability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

Sunday, March 11, 2012

Dynamic formatting of table columns

Friday, March 9, 2012

Dynamic Dynamic Filters

Sunday, February 26, 2012

Dynamic Cursor versus Forward Only Cursor gives Poor Performance

Friday, February 24, 2012

Dynamic connection string

Dynamic connection string

Sunday, February 19, 2012

Dynamic Columns

Dynamic columns

Friday, February 17, 2012

dynamic column headings?

Wednesday, February 15, 2012

DW/DSS Schema design

DW/DSS Schema design

DW/DSS Schema design

DW/DSS Schema design

sql dwh problem

Blog Archive

About Me