Populate Star Schema By DTS
Dec 18, 2002does any one know how to built a star schema by DTS:confused:
View 4 Replies
ADVERTISEMENT
Using Star Schema
Dec 27, 2004Hi,
I have tried the following out and would appreciate feedback from experienced users regarding if the following is a good/bad approach:
After bring all the data in my Data Mart, I have created a view which has all the data in a big flat table (totally unnormalized). Then based on this BIG FLAT UNNORMALIZED VIEW :) I have created my various dimensions using the 1st option i.e. Star Schema.
Based on the little testing that I have done, I seem to be getting the correct results across various dimensions... However, can someone kindly comment on this approach and the pros/cons.
Thanks
Analysis :: Cube Extracting Data From Physical Or Logical Star Schema?
Oct 22, 2015We have an OLTP database and operational reporting is carried out on a replica server / database. We have plans to build a new data warehouse and an analysis services cube.
Question 1:Should a cube be designed to extract data from a physical star schema rather than a logical one (3NF relational (ODS?) using a data source view to derive the star)? I'm guessing for performance it's better to pull data from similar structures (physical facts and dimensions as required by analysis services) but is the difference significant?
Question 2:Depending on the answer to q1, is it bad practice to ETL data from a staging database (replica > staging) directly to a star schema (multiple data sources and cleansing / business rules required)? Or should it be processed from staging to an ods and only then to a star schema (physical or logical). I still don't know if an ODS is required but I guess the consideration for this decision is whether the business would require daily operational (or ad hoc) reporting on the consolidated data sources (without needing historical DW functionality).
Star Schema Design For Company Headcount With Headcount Targets
Nov 16, 2007I am cutting my teeth on star schema design. I have a simple star schema I am building for Headounct analysis at work. I have a factless fact table where a row represents a head in the company. Each head is toed to a particulat week in a Date dimension tabel. There are additional dimensions for things like gender, ethnicity, marital status, age, etc. Now in my department dimension - it's hierarchical. In the DimDepartmnet there is a department which belongs to a company. Comapnies belong to divisions. Now the fun part. Each division has a headcount target for each year. Up to this point I am in a perfect star schema (no snow flaking). How would I integrate in this concept of a headcount target for each division for a given year?
We are using cognos on top of this star schema to provide reporting and analysis services if that is relevant. From the Star Schema design stand point... any thoughts?
Christian Loris
Populate Schema Name Dynamically
Jun 30, 2006We are designing a server where functions and procedures are to be kept in a single database. Our other databases will call these functions. The functions will need to reference tables in the schema being called from. Is it possible to pass a schema name to a function? If so, then how?
I tried passing something like:
CREATE FUNCTION [dbo].[getPaymentReceived2](@inSchema CHAR(30), @inAraccountid CHAR(15), @inInvoicestart SMALLDATETIME)
RETURNS MONEY
AS
BEGIN
DECLARE @outAmt money
DECLARE @arinvoiceid char(15)
SET @arinvoiceid = (SELECT ai.arinvoiceid
FROM @inSchema.dbo.arinvoice ai
WHERE ai.araccountid = @inAraccountid
AND ai.invoicestart = DATEADD(month, - 1, @inInvoicestart))
I get an incorrrect syntax error.
Star Model Question
Oct 13, 2000My clients are not intersted in using Auto generated keys. They are
also get data from many sources they would like to use something like
customerID. Dose anybody know of any reason why we should not do that?
Also they are concern about sql server not being able to handle the datawarehouse in future because they are expecting it to grow in terabites.
Dose anyone have advice on that?
I was thinking of putting the fact table on a different file group don't if it will help.
Datawarehouse Star Model Question
Oct 11, 2000I am re-engineering the data warehouse and my client is currently using autogenerate keys, their concern is that after a certain amount of keys (can't remember the figure) sql server starts having problems, dose anyone know how i should handle it when i am doing the designing?
thanks any input will be appreciated
Star Schemma - Distributed Transaction
Nov 23, 2007I'm designing a DW, and i have some doubts relative to the Distributed Transaction when modeling a star schemma.
My problem is: I have a main dtsx package in wich i call all the child packages in order to create the (Fact and Dimension Tables).
(1) First i have several child packages that create and populate all the Dimension Tables (with the latest values from the relational DB).
(2)Then i have several child packages that create all the fact tables, in this process i use the surrogate keys from the dimension tables (obtained in step 1).
The problem here is , " How do i use the multiple transaction ?" , if i put a "required" Transaction Option on the parent package, then after calling the child packages that creates the dimension tables. The values are not commited, so they are not available when i later execute the childs packages related with the fact tables.
How can i use transaction when modelling a star schemma, in order to have a full roll back or a full commit in all tables (Dimensions and Fact Tables).
Thanks
Select Specific Column In Front Of Star
Jul 8, 2014In SQL Server I can select a specific column in from of * like so:
Code:
select test_column_1,* from testtable1
I've been googling around and cannot seem to be able to find a definitive answer.
Pointers To The Best Documentation On Star Joins And The Optimiser
May 16, 2007Hi All,
we are just starting to do some testing on sql server EE with dimensional models.....we have had one or two problems we have been able to solve using the new peformance dashboards etc.
However, as is inevitable, we are seeing strange behaviour of a query....in a star join it seems to be doing an eager spool and trying to spool the entire fact table to tempdb....hhmmm....
Rather than ask one question at a time.....we have DBAs who went to classes etc at MSFT and the client is some level of MSFT partner.
Could anyone point me to the best documentation for understanding the optimiser and how to influence it to get it to do the right thing in optimising plans for star joins?
Thanks
Peter
SSRS And Oracle's Star Transformation Query
Apr 30, 2008So this has got to be considered a major, major flaw in how SSRS interacts with Oracle. I'm using the "Oracle" data provider, but I've also tried using Microsoft's OLE DB data source, and some others, and in no case does SSRS hand off to Oracle a query that does NOT have bind variables. In other words, typically query parameters get passed off to Oracle as bind variables.
The incredibly major problem that this causes is that it disallows Oracle's use of star transformation queries which is the primary method by which to get fast responses to a data warehouse/star schema, in fact a prime authority on this subject (Bert Scalzo, Oracle DBA Guide to Data Warehouse and Star Schemas, p.86 -- obvioulsy not using Oracle 7x was the first) lists it as in effect the #1 consideration.
So what gives? In effect SSRS cannot be used against large scale Oracle data warehouses? I've had success with Business Objects being able to access Oracle star transformations.
So a guess my question is how the heck can use SSRS in a big, Oracle-based data warehouse?
http://www.dba-oracle.com/oracle10g_tuning/t_star_transformations_sql.htm
For star_transformation join plans, the following parameters must also be considered: ... No BIND VARIABLE in SELECT statement
http://www.orafaq.com/usenet/comp.databases.oracle.server/2003/09/28/2305.htm
Star transformation is not supported for tables with any of the following characteristics:
* Queries that contain bind variables
Indexing In SQL Server Star Scheme Data Warehouse
Jan 19, 2007Hi all,
Our star schema design has one fact table and 3 dimensions.
The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:
Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3
Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.
Is the above design good to start with?
Thanks,
V
The 'System.Web.Security.SqlMembershipProvider' Requires A Database Schema Compatible With Schema Version '1'.
Sep 27, 2007Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
Transferring Objects Form Schema A To Schema B In One Shot....!
May 27, 2008I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
Database Schema Compatible With Schema Version '1'
Apr 12, 2008Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies View RelatedMoving Data From One DB Schema To Another DB Schema Using SSIS
May 8, 2007Hello,
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Please give me some advices here.
Thanks.
Adding A XML Schema To XML Schema Collection
Apr 19, 2006I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
View 3 Replies View RelatedCopy Objects From One Schema To Another Schema?
Nov 21, 2011I am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.
In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.
Ddl Doesn't Populate
Feb 6, 2008Anybody see a reason why this list won't populate?
<asp:DropDownList ID="ddlState" DataSourceID="srcStates" DataTextField="StateName" DataValueField="StateName" runat="server"> <asp:ListItem>Select State</asp:ListItem></asp:DropDownList><asp:SqlDataSource ID="srcStates" runat="server" ConnectionString="<%$ ConnectionStrings:webConn %>" SelectCommand="sp_States" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="ddlState" DefaultValue="Select State" Name="State" PropertyName="SelectedValue" Type="String" /> </SelectParameters></asp:SqlDataSource>
How Do I Populate A Listbox From A SQL DB?
Feb 10, 2004Sorry if this is to basic but I am just starting out. Any help is appreciated.
Basically I am attempting to populate a listbox with items from a MSSQL DB so the user can select either one or multiple items in that listbox to search on.
Need To Populate Columns For Whole ID
Feb 2, 2015I have data like below, I need to populate the ID_INDICATOR columns with the below condition
ID TASK_ID TASK_COMPLETEDTSTASK_DUETSTASK_INDICATORID_INDICATOR
112014-06-09 00:00:002014-06-11 00:00:00GREEN
122014-06-13 00:00:002014-06-14 00:00:00AMBER
132014-06-17 00:00:002014-06-16 00:00:00RED
142014-06-17 00:00:002014-06-18 00:00:00AMBER
Condition:
##########
Red = If ID due date was overdue, i.e. if last task completed after the ID end date(2014-06-18 00:00:00).
AMBER= If any task in the ID is overdue but completed before the ID end date(2014-06-18 00:00:00)
Green = If all tasks were completed on time.
I am looking for the logic to implement the AMBER for the whole ID, because the TASK_ID 3 is overdue, but completed before the ID end date (2014-06-18 00:00:00).
Trying To Populate A Name Column
Sep 18, 2006I am trying to update a name column in the following way: (I wrote a description, but I think this visual is easier to understand).
This is what I have:
name1 name2 address etc
Bob null 123 street
Sue null 123 street
Jack null ABC circle
This is what I want:
name1 name2 address etc
Bob Sue 123 street
Jack null ABC circle
I'm just trying to get 2 names on the same row if they have the same address and get rid of the spare row. Name2 is currently null. Seems simple enough but I don't know how to do it in SQL. I can do it in FoxPro, but that doesn't help me here.
Thanks for any ideas.
How Do You Populate A New Index?
May 5, 2008I added an index to a SQL Server 2005 table. How do I populate that index? I thought it might be automatically populated but the operation to add the index happened so quickly that I don't think it could have done it that quickly. Also, I expected faster performace when selecting rows based on the indexed column, but performance remains the same.
View 1 Replies View RelatedPopulate A Variable
Oct 24, 2006During a dataflow - I like to populate a variable with True or False based on a value in one of the data records.
How do I do this excercise ?
if I use a script - can someone provide me with simple script on how to populate one variable ?
Thanks heaps
How Can I Populate Data In A One-to-one Relationship
Jul 20, 2006Here is what I have and (somewhat understand). I’m using Visual Web
Developer 2005 Express Edition and have setup my application to use form authentication
(which automatically creates the ASPNETDB.MDF file with several default tables
and views). I’m using the CreateUserWizard
which is fine…but I need to collect additional information like (firstname,
lastname, address…and on..). What I’ve
done. I’ve created a tabled named
UserProfile and set UserId as the primary key (uniqueidentifier).
I then setup a 1-to-1 relationship
between aspnet_Users and UserProfile (which I think is correct). On my UpdateContactInfo.aspx page (where
users go to update their personal information) I use a hidden label control
(UserValue) to receive the UserId during the page_load event as below:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load UserValue.Text = Membership.GetUser().ProviderUserKey().ToString() End Sub Now with the UserID available I need to populate the
UserProfile table with the UserId, firstname, lastname, address of the
currently logged in user. How can I do this and am I on the
right track..?
Populate Arraylist From A Database
Sep 15, 2006Hi i'm pretty new to this, how do i connect to my database a put all the values from one column into the arraylist. Any help and a easy example would be nice Thanks in advance Richard
View 4 Replies View RelatedPopulate Binay Data
Oct 16, 2006We have stored large text data in "Image" data type in SQL Server. Can someone tell me how to get that vlaue from image datafield. Thanks
View 3 Replies View RelatedPopulate The Array From Database
Oct 18, 2006Hi, I want to populate the array with a single column values from database(sqlserver 2000)Ex. name ageaaaa 23bbbb 43cccc 18 Now i want to populate the array with name field values. Please anyone guide me how to do this.Thanks in advanceBala
Populate DB Records In Array
Nov 9, 2006Using the SqlDataReader, I am retrieveing records from a SQL Server 2005 DB table. Since I am using the SqlDataReader to retrieve the records from the DB, I have to use the Read method of the SqlDataReader like this:Dim sqlReader As SqlDataReaderWhile(sqlReader.Read) Response.Write(sqlReader.GetValue(0) & "<br>") Response.Write(sqlReader.GetValue(1) & "<br>") Response.Write(sqlReader.GetValue(2) & "<br>")End WhileThe records retrieved can only be accessed inside the While loop. I want to access the records outside the While loop as well. Is there anyway by which I can do this, maybe by populating the recordset in an array variable & then using it outside the While loop?
View 3 Replies View RelatedHow To Populate A ArrayList With A IDataReader
Jan 18, 2007In my data access layer class I have Populate methods on the bottom.One of the the objects, color, is an ArrayList, how do I write that? private Product PopulateProduct(IDataReader r) { Product product = new Product(); product.BrandId = Convert.ToInt32(r["brandId"]); product.BrandName = Convert.ToString(r["brandName"]); product.Color = whatGoesHere? (r["color"]); return product; } thanks
View 4 Replies View RelatedPopulate Automactly UpdateParameters
Jan 29, 2008 Hi there!I'm trying to populate automactly a record in a table. I've tried this: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ProducaoLigacao %>" SelectCommand="UPDATE [Producao] SET [Prodas] = @Prodas WHERE ([ProdUserID] = @ProdUserID)">
<UpdateParameters>
<asp:Parameter DefaultValue="1" Name="ProdUserID" Type="Int32" />
<asp:Parameter DefaultValue="999" Name="Prodas" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource> Is this possible? Thanks in advance!
Populate Dropdown Box From SQL In Web Matrix?
Jan 22, 2004I have been attempting to populate a dropdown (combo) box from a SQL query (SQLConnector) in Web Matrix and I have so far been unsuccessful. I have been able to populate a DataGrid just fine from the query, so then I limited it down to one colum (so itd be in the dropdown box) and set all the properties I could find (DataSource, DataMember, DataTextField, DataValueField) (and in all possible combinations) for that dropdown box to the SQL stuff (SQLConnector for the DataSource and then used the dropdowns for the others), and nothing shows up in my dropdown box. How can I make this populate off of my SQL query properly?
Additionally I will need to do the same thing for a Checkbox List so if it is the same and you know how to do that and could let me know for that too that'd be great.
Thanks a million in advance!
Two DB Tables To Populate One Control
Jun 20, 2004I have a dropdownlist that is populated from a DB table. I would like to also include any other values that might be in another table. How do I combine these two queries, so I can get distinct values from both tables combined?SELECT Category
FROM dbo.TABLE1
GROUP BY Category
SELECT Category
FROM dbo.TABLE2
GROUP BY Category