Dear Friends,
I created my BI Blog and I need your feedback to my post SSIS-Populating Dimension. Do you think is a good approach? In my project works perfectly!
http://pedrocgd.blogspot.com/
Thanks!
Hi. I am currently building a blog application, and I need some advice... I have the post title, post date, post by, etc stored in a database table, however I was wondering whether I should store the actual post content in the database as well or in a text file. For example, if the posts get really long would it slow down database performance or would there not be much of a difference? Furthermore, if I wanted to keep the posts private, a text file would not be ideal as it can be accessed easily by surfers... What do you recommend?Thanks a lot
View 1 Replies View RelatedHi,
I am new at SSIS and I am trying to create a Datawarehouse using SSIS. I have the data files as flat files I have the Dimensional Model ready on Paper and Now I need to use the SSIS for the ETL process.
I am trying to figure out how to make dimension tables in SSIS? I mean I want to create the 5 Dimension tables and then create a Fact table out of it but I cant understand where to start? Can any one tell me how we create Dimesion tables in SSIS. Like one of the dimesion tables I need to create uses 2 flat files and is like a flattened dimension, How would I create this in SSIS?
Even if there is any tutorial which shows this step by step do let me know. I would really appreciate any guidance on this.
Thanks,
Sarang
Hi Folks,
Can anyone recommend a script to populate a date dimension such as that described in Kimball's "The Data Warehouse Toolkit, 2nd Edition" pp.38-41???
Thanks - wg
Hi,
I am currently trying to develop an application which would help in retrieving data from cubes (Microsoft Analysis Services)! The user would not be accessing the Business Intelligence Studio, etc. but would be viewing the data from a custom made application developed in VB.Net2005.
While implementing this, I want to populate the drop-down-list in the VB(.Net) Form, by retrieving the various hierarchies in the dimensions of the cubes (along with dimensions if possible). This should be done dynamically and in real-time!
Can you please help in implementing this? Any code/method, etc would be highly appreciated!
Thanks in advance.
Best wishes!
(Software : SQL Server 2005 Enterprise Edition (with Analysis Services and BI Studio), VS.Net 2005 Enterprise Edition, ADOMD.Net)
can someone help me with th best way to look up a date in date dimension and populate the date id in fact.
in the source date is dd/mm/yyyy
and in date dimension columns are date id , year , quarter , month, day
When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.
View 4 Replies View RelatedI 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
Hi,I have a 2 tables called 1.tblRisk which consists of Ref(pk), subject, status, staff & Dept(fk)2.tblDept which has Ref(Pk) & DepartmentHow do i get it to populate Department, when tblRisk Ref's Dept matches the Ref in tblDept i am using SQL Server 2000best regards
View 1 Replies View RelatedHi,
Is it possible to populate a dataset with tables returned by a stored proc?Consider this:
BEGINSELECT * FROM Table1SELECT * FROM Table2SELECT * FROM Table3END
If that is my stored proc, could I call it from a page and automatically populate a dataset with all 3 tables (if yes, then how?), or would I have to make 3 seperate calls to the db for each table?
Thanks
I would like to find a utility that can scan through an existing SqlServer 2000 database and create scripts to re-insert/re-populate thedata into another DB with the same table structures. Like a back-uputility.Can anyone recommend one (or tell me which ones to avoid)? Iwould like one that takes dependencies between tables intoconsideration.Thanks
View 1 Replies View RelatedHi,
I wonder if anyone could tell me whether it is acceptable to post a SSIS contract vacancy on this forum?
If not, where would be the best forum/site to find UK-based SSIS developer contractors?
Many thanks,
Gavin.
I have a table is SQL server database A that is my source.
I have another database B which is accessed via webservice call.(its a CRM server basically).
My intention is to transfer data from A to B while B is accessible only via web service. I need to Trasnfer the data from the source database A to the destination Database B by calling the webservice.
Is there a way where I can retrieve whole set of rows in source table in preexecute(), And transfer the data to Database destination B by calling the webservice?
When creating a new row or record in the Master table (by an Adapter update), how do I then populate new records in 15 different Child tables?
These Child tables are attributes of the Master table. I am sure there is more than one way to do this...
Thanks
I'm coding in a black hole that has a moving target in it.
Ok, I now need to call an stored procedure and with the results from the stored procedure I need to populate my tables on my handheld.
Any ideas on how I can do this? I was using inline sql "select col1, col2, from table1" and doing my rda pull from that, but now I need to call stored procedures to do this and one sp takes a parameter. How can I accomplish this on handheld ?
Hi,
I need to write a reporting app that process daily IIS logs that are averaging about 299MB a day!
I am considering using SSIS to put the log data into a sql server 2005 database.
Has anyone done this? Or does anyone know if it is possible? And without using the MS LogParser?
Thanks in advance and any help greatly appreciated!
Regards,
kitquo2.
---------------------------------------------------------------
My Original Post
I have to query n table(NLRImports) using the Distinct keyword, to retrieve a set of ID numbers. ( "Select DISTINCT id_nbr from NLRImport" ).
Now i want to use those values i retrieved, to process the records in the table(NLRImports) 1 by 1. How do i use those ID no's i retrieved as Variables or parameters for my next query?? If this makes sense?
----------------------------------------------------------------
First, thanks for the response.... now here is what im trying to do.
I created a simple application in delphi to import information to a table in MSSql2005. This is some of the resulting columns...
date | id_nbr | account_nbr | sub_account_nbr | ... etc
-------------------------------------------------------------
Now there will be several entries with the same id no but on different dates, so i take it dates would rather be my pkey.
Then i need to take one person's entries(i work on id_nbr) and go thru all the entries taking the earliest date and comparing all the other entries for that person to the first date and select all the dates more than 19 days after the first date and less than 91 days from first date and place it in a new table.
I used cursor s and while loops to kind of get it going but i know that cursors are not really recommended use but the performance implications dont bother with this particular job.
What other ways should i be using to accomplish this?
thanks, i hope this is clear...
I have a temporary table (@tblResults) that has 4 columns that need to be populated with a calculation made from columns held within 2 other tables.
Joins
@tblResults tr JOIN dbo.MarketPrice mp
ON tr.Item = mp.Item
AND tr.[Month] = mp.[Month]
AND tr.[Year] = mp.[Year]
AND mp.[Date] BETWEEN tr.LatestStartDate AND tr.PriorEndDate
[code]....
Where the 2 dbo.MarketPrice and dbo.MillDifferentials date fields are NOT equal, the last (chronologically) dbo.MillDifferentials.Diff value should be used (or '0' if no previous value found).
so expected results where @tblResults.Id = 1:
The dbo.MarketPrice.Price value of '2014-10-29' should be combined with the dbo.MillDifferentials.Diff value of '2014-10-06' - this produces the combined Max value of 184.50
The dbo.MarketPrice.Price value of '2014-04-28' should be combined with the dbo.MillDifferentials.Diff value of '2014-04-28'
The dbo.MarketPrice.Price value of '2014-01-22' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20'
The dbo.MarketPrice.Price value of '2014-01-21' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20' - this produces the combined Min value of 111.50
The dbo.MarketPrice.Price value of '2014-01-04' should be combined with '0.00' if there is no matching or previous dbo.MillDifferentials.Diff value OR the top 1/max (most recent) dbo.MillDifferentials.Diff value if a record is found before the specified @tblResults.LatestStartDate
Hi,
I accidentally populate tables into System databases/master database. What should I do? Should I delete all the tables I populate in mster database?
Thank you very much!
We'll be using 2014 enterprise to populate some excel spreadsheets. We may not have the option of using ssrs so here is the question.
The excel spreadsheets have some pretty fancy heading structures, multiple tabs and in a few cases graphs that i'm sure are dependent on data sitting somewhere in the spreadsheet. The heading have variable info in them (eg FYxx where xx is a parameterized fiscal year, month names etc). Sometimes those headings have sub headings (with variable info) and so on.
Generally speaking how (if at all) do folks deal with this kind of excel challenge when limited to using ssis? I don't know if templates are going to be a good idea. I don't know if i'll be looking at a fair amount of c# etc code behind the scenes. I can be more specific depending on the feedback.
This is part of a conversion from 2 of the larger BI and statistical tools out there to the MS sql stack. Obviously with some shortcuts that we may wish we didn't take.
I'm still fairly new to cubes, so bear with me. I'm trying to figure out if I should include aggregate data (e.g. total employees per facility) in a dimension table or if I should use the finished cube to get the counts (MDX?). Thanks for any help that you can provide.
influent
Hi All,
What is the best way to move data from Online system tro data warehouse?
I have created 3 dimension tables(product,date and customer tables) and
I wanna create fact table and get foreign keys from dimension tables.
What is the best method to do that in SSIS?
thanks
I am currently looking at the capabilities in SSIS from the point of view of an ETL developer who has worked with other products eg. Informatica, Cognos DecisionStream and one of things I note is a lack of support for dimensional hierarchies.
It appears that MS have assumed that SSIS users will automatically use SSAS.
We use Hyperion Essbase. Other sites have Cognos or Business Objects for their OLAP/BI.
I would like to be able build multi-level dimension hierarchies directly from within SSIS.
Has MS considered this for future versions?
How to populate an excel cell from a sql table using ssis.
I need to populate a specified cell lets say H7, in an excel sheet,
from a sql table in a ssis package.
Suppose we have a column in sql table called total, which contains some value,
we need to take this value and populate the cell H7 in an excel sheet.
The problem is that I have (for example) following data
AAAA AA ALH
B BBB MIL
CCCCCC CAC Q
D D P
in space delimited file and i want to populate a database using SSIS and it is not working. Any suggestions will be appreciated.
P.S. the only way to recognize colums is through spaces b/w the data.
Hey Guys,
I have a cube with a Period Dimension, in which the hierarchy is as follows:
Year
Quarter
Month
Day
Users access the data via a pivot table.
The problem is that whenever a new month comes in, the pivot table doesn't automatically display data regarding the new month. Can someone please help me here.
Thanks,
Surbha
Hi there, my question is really simple. I want to setup an automatic task in SSIS that drops the tables in the target database and substitutes them with tables from the source database. We are talking about two or three dimension tables and one fact table. The dimension tables are pretty small. The fact table will contain, at maximum, 300,000 rows and 12 columns. I do not use delta or flag historisation btw. What tasks in SSIS would you suggest to use?
BTW I'm new to SSIS... ;-) Thanks in advance!
Hi ,
I have situation where I get data from SRC Flat file and have to load Dimensional table and also fact table, using same data flow(have no other choice since I have to unpivot some src data). Since I have to load both tables in same data flow, I have to have a way to put load ordering constraint (I know informatica allows that). Does any one have any idea on how this can be done in SSIS?
I would be really grateful.
Thanks
Hi,
Can anyone provide me with some sample examples on how to load dimension tables in SSIS. Any help is highly appreciated!
Thanks in advance!
Regards,
Vivek.
I've broken down and started one of these things: http://blogs.msdn.com/ryan.stonecipher/default.aspx . Watch that space for "good to know about DBCC" stuff.
Thanks,
----------------------
Ryan Stonecipher
Developer, Microsoft SQL Server Storage Engine, DBCC
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Hi all,
Apologies if this has been raised in the past, but 6 hours of web searching today hasn't turned up anything!
I'd like to use the Slowly Changing Dimension (SCD) Wizard to keep track of tables in my relational database. This means 200+ tables. I don't want to step through the UI Wizard for each table. Ideally I'd like to be able to create the SCD transformation in code, but I can find no good examples for doing this. The MSDN examples here are too brief and don't allow me to expand out to the level I need.
As in any database, columns come and (very rarely, go), and having a programmatic solution to this would mean that I could be flexible and cope with these situations.
So, my question is: Has anyone implemented SCD functionality in code, or have any code examples that do this, that I might learn from. Or, any tips/pointers if I'm barking up totally the wrong tree.
Thanks in advance,
-tom
The problem is that I have (for example) following data
AA AA LH
BB BB ML
CC CC QA
DD DD PS
in space delimited file and i want to populate a database using SSIS and it is not working. Any suggestions will be appreciated.
Hello,I am creating a simple blog system using SQL 2005.I have a Blog table:[BlogId] > PostId (PK), BlogTitle, ...And a Posts table[Posts] > PostId (PK), BlogId (FK), PostContent, PostLabels, ...PostLabels would have the following format:Label1,Label2,Label3, etc ...I will need to perform 3 actions:1. Get all posts in blog2. Get all labels in a post3. Get all unique existing labels in all posts in a blog and make a list.I am not sure if my approach of using a simple labels column in my Posts table is a good idea.So my other idea would be to add two more tables:[BlogLabels] > BlogLabelId (PK), BlogId (FK), LabelName ...[LabelsInPosts] > BlogLabelId (PK), PostId (PK)So my idea is:1. When creating a post one of the parameters would be a comma separating string with all labels for the post. Inside SQL Procedure I will need to loop through each label and check if it exists in BlogLabels. If not then I added it. For each label I add a records in LabelsInPosts. How to create this loop? Am I thinking this right?2. To get a list of all labels in a blog I would need to go to BlogLabels and get all labels which are related with posts in LabelsInPosts. Those posts must be only the ones that are related with my given BlogId. Grrr, this is getting really confusing for me. Is this possible to to? How?Please, give me some advice about all this.Thanks,Miguel
View 1 Replies View Related