I can manipulate this in a pivot table for reporting however I need this to be a table for reporting by filterable searchable information. What can I do short of defining each field with case logic, or union queries.
I want to create a table that has a datetime column, data type=datetime and I want the date to start form 01/01/2004 00:00:00 to 01/01/2008 00:00:00 the increment will be by minute like
01/01/2004 00:01:00
01/01/2004 00:02:00
01/01/2004 00:03:00 and so on up to 01/01/2008 00:00:00
I will use this time dimension in bussiness objects.
In my data modell I have defined the 2 tables "Person" and "Category":
Table "Person" ---------------- [PersonID] [int] IDENTITY(1,1) NOT NULL [CategoryID] [int] NOT NULL [FirstName] [nvarchar](50) [LastName] [nvarchar](50)
Table "Category" ---------------- [CategoryID] [int] IDENTITY(1,1) NOT NULL [CategoryName] [nvarchar](50)
Now I like to read my first row from the source and lookup a value for the CategoryID "sailing". As my data tables are empty right now, the lookup is not able to read a value for "sailing". Now I like to insert a new row in the table "Category" for the value "sailing" and receive the new "CategoryID" to insert my values in the table "Person" INCLUDING the new "CategoryID".
I think this is a normal way of reading data from a source and performing some lookups. In my "real world" scenario I have to lookup about 20 foreign keys before I'm able to insert the row read from the flat file source.
I really can't belief that this is a "special" case and I also can't belief that there is no easy and simple way to solve this with SSIS. Ok, the solution from Thomas is working but it is a very complex solution for this small problem. So, any help would be appreciated...
We are trying to do some utilization calculations that need to factor in a given number of holiday hours per month.
I have a date dimension table (dimdate). Has a row for every day of every year (2006-2015)
I have a work entry fact table (timedetail). Has a row for every work entry. Each row has a worked date, and this column has a relationship to dimdate.
Our holidays fluctuate, and we offer floating holidays that our staff get to pick. So we cannot hard code which individual dates in dimdate as holidays. So what we have done is added a column to our dimdate table called HolidayHoursPerMonth.Â
This column will list the number of holiday hours available in the given month that the individual date happens to fall within, thus there are a lot of duplicates. Below is a brief example of dimdate. In the example below, there are 0 holiday hours for the month of June, and their are 8 holiday hours for the month of July.
I have a pivot table create based of the fact table. I then have various date slicers from the dimension table (i.e. year, month). If I simply drag this column into the pivot table and summarize by MAX it works when you are sliced on a single month, but breaks if anything but a single month is sliced on. Â
I am trying to create a measure that calculates the amount of holiday hours based on the what's sliced, but only using a single value for each month. For example July should just be 8, not 8 x #of days in the month.Â
Listed below is how many hours per month. So if you were to slice on an entire year, the measure should equal 64. If you sliced on Jan, Feb and March, the measure should equal 12. If you were to slice nothing, thus including all 15 years in our dimdate table, the measure should equal 640 (10 years x 64 hours per year).
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
Why is taking so long to open/create/render the reports for the first time? Is there any configuration to change this? I don€™t think this behavior is related to Report Execution or cache! I think there is something else going on! Thanks.
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.
Analysis manager won’t let me create dimension unless fact table filed has value init. Other words, how do I create MT dimension(dimension without member)
I have a fact table which contains the transaction date, ProductID, QTySold, TotalSaleAmount, etc...
Since I am new to OLAP therefore I need help to now create the table for TIME on which I will be basing my time dimension... I have read a few articles and have gathered that at the end of the exercise my fact table should have a 'timeid' column which will be linked to the same column in the table being used for the time dimension...
I have gone through the tutorial of MS-Analysis Service and FoodMart example have some idea about what he structure of this table will be.
My questions are:
1. I need guidance on how to create the table for time. One option is to just copy the table used in the Foodmart example but thought that might work but my concept will not be clear
2. The structure of the table to be used for time dimension is quite clear (i think this part is easy). What I want to understand is that how do i POPULATE this table which data? Can some one provide me with scripts, SPs, or whatever to do this.... This is the area where I am lost...
3. How will I enter the "TheDate" column in my fact table and link it with my table for time dimension...
Looking forward to someone's help.
BTW, I would like to share a very good article which i recently found in one of the newsgroups. Some of you might like appreciate it too: http://www.sqljunkies.com/Article/D1E44392-592C-40DB-B80D-F20D60951395.scuk
some of our Drill down has many values and we would like to display only top 10 ... I know how to create CALCULATEDMEMBER for the cube ..but that becomes a measure and will NOT be in the drill down so,
how to create Top 10 for Raw Url
Drill Down Groups Top 10 Urls ( this has 10000 values€¦€¦.. can we just display top 10 here? Right now the report never comes back because of the count€¦ or if there is a better way let me know
I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.
E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name] Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].
Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"
Please look into this issue, advice me what do I do ASAP.
I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.
E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name] Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].
Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"
Please look into this issue, advice me what do I do ASAP.
Just found that I am not able to ignore the name column property for role-palying dimension even though I only select one of the key columns for this dimension in the mining structure where the role-playing dimension is used? E.g I have a fact table (which is the case table for the mining structure) which is related to the role-playing date dimension. The schema is as following:
Dim_event_day_time_key
Dim_carrier_day_time_key
Dim_domain_day_time_key
.....................................
and other attibutes in the fact table.
When I dragged the 'hours' attribute from the dim_day_time table to the mining structure, if I left the key columns of 'Hours' as the above 3 columns, then I have to select a name column, by then I dont have a name column in the dimension table yet. Therefore I want to jsut select one key column as the key of that role-playing dimension in the mining structure, but then I am not able to ignore the 'name column' property as it still always asks me to select the 'name column' for that.
Therefore my question is: we are not allowed to select only one key columns in the mining structure for the role-playing dimension? And we will have to always go to the data source view to create a named calculation as the new column for the role-playing dimension?
Hope my question is clear for your advices and I am looking forward to hearing from you shortly.
The above increments by 1 day which is defined in the 1st argument of the #duration. My question is how can I dynamically change the value of this 1st argument such that its the number of days in the current month hence it will increment to only return the 1st date in the Month e.g
1/1/2000 1/2/2000 1/3/2000 etc..
I prefer to use an elegant approach if possible, the alternative would be return all dates, create a custom column from these dates which returns the month date - delete the dates column - get a distinct list of the month dates.
Dear Experts, i've one table named table11. in this perticular table, i've 30 columns and 40,000 rows of data. this table is taking 35 sec for select * from table11.
defnetly it will take more time if i used this in some places like procedures and functions or views like that.
where is the problem? generally it takes that much of time or is there any problem?
guidence please.....
Vinod Even you learn 1%, Learn it with 100% confidence.
I have one query with 3 statments in it, which then creates a table called HCSReturnFile. My problem is that I have an order by in my query, but when I go to create a table that puts all 2 statements into 1, it does not do the order by. What am I doing wrong? Below is my Query.
USE [Impact_PROD] GO /****** Object: StoredProcedure [dbo].[p_GenerateHCSReturnFileUPDATE] Script Date: 01/18/2008 14:20:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:Wendy & Mike -- Create date: 11/08/2007 ---Create Date to Production - 12-06-2007 -- Description:This was a touch one. ---This report drop both Queries into a tabled called HCSRetrunFile -- ============================================= ALTER PROCEDURE [dbo].[p_GenerateHCSReturnFileUPDATE]
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF OBJECT_ID('IMPACT_PROD..HCSReturnFile') IS NOT NULL DROP TABLE HCSReturnFile
INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile] ([EventNumber] ,[ClaimNumber1] ,[Resolution] ,[Resolution2] ,[ProviderType] ,[Negotiation] ,[NEGOYESORNO] ,[ProviderID] ,[HCSAuthorizationID] ,[PROLASTNAME] ,[proFirstName] ,[ProOffice] ,[TOTALBILLEDAMT] ,[SAVINGS] ,[OONNEGO] ,[CLM_ATT1] ,[CLM_ATT2] ,[CLM_ATT3] ,[CLM_ATT4] ,[CLM_ATT5] ,[NoteData]) SELECT Distinct --n.note_sys, --c.CLM_id1 AS 'ClaimNumber', e.EVE_id1 AS 'EventNumber', ClaimNumber1 = '', --e.EVE_clm As 'EventEventTable', --c.CLM_elrc AS 'EventClaimTable', e.eve_resl1 as 'Resolution', p.EVEP_RESL1 as 'Resolution2', p.evep_reas as 'ProviderType', p.evep_nego as 'Negotiation', --e.EVE_STAT AS 'STATUS', e.EVE_SW01 AS 'NEGOYESORNO', ProviderID = '', e.EVE_EXNO AS 'HCSAuthorizationID',
--p.EVEP_LNAME AS 'PROVIDERLASTNAME', --p.EVEP_FNAME AS 'PROVIDERFIRSTNAME', p.EVEP_LNAME AS PROLASTNAME, p.evep_fname AS proFirstName, p.evep_offic AS ProOffice, --c.CLM_EDID AS 'LOCKDATE', TOTALBILLEDAMT = '', SAVINGS = '', OONNEGO = '', c.CLM_ATT1, c.CLM_ATT2, c.CLM_ATT3, c.CLM_ATT4, c.CLM_ATT5, n.NoteData
FROM dbo.EVE e
RIGHT JOIN dbo.clm c ON e.EVE_id1 = c.clm_id1 RIGHT JOIN dbo.EVEP p ON e.eve_id1 = p.EVEP_id1 JOIN dbo.notes2 n ON n.eve_id1 = e.EVE_id1 --JOIN evep p2 --ON p.evep_id1 = p2.evep_id1
Where e.eve_resl1 = 'CL'and p.EVEP_RESL1 <> 'NG' and p.evep_reas <>'FA'--and ---means closes Events --p2.evep_nego = 'Y' and --((LTRIM(p.evep_id2) <> '0002' AND p.evep_id2 <> '0001' AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1) --e.EVE_STAT = 'CL'and --e.EVE_id1 IN ('00101965','00102080','00102084','00101962','00101963') --and
SELECT Distinct --n.note_sys, --c.CLM_id1 AS 'ClaimNumber', e.EVE_id1 AS 'EventNumber', ClaimNumber1 = '', --e.EVE_clm As 'EventEventTable', --c.CLM_elrc AS 'EventClaimTable', e.eve_resl1 as 'Resolution', p.EVEP_RESL1 as 'Resolution2', p.evep_reas as 'ProviderType', p.evep_nego as 'Negotiation', --e.EVE_STAT AS 'STATUS', e.EVE_SW01 AS 'NEGOYESORNO', ProviderID = '', e.EVE_EXNO AS 'HCSAuthorizationID',
--p.EVEP_LNAME AS 'PROVIDERLASTNAME', --p.EVEP_FNAME AS 'PROVIDERFIRSTNAME', p.EVEP_LNAME AS PROLASTNAME, p.evep_fname AS proFirstName, p.evep_offic AS ProOffice, --c.CLM_EDID AS 'LOCKDATE', TOTALBILLEDAMT = '', SAVINGS = '', OONNEGO = '', c.CLM_ATT1, c.CLM_ATT2, c.CLM_ATT3, c.CLM_ATT4, c.CLM_ATT5, n.NoteData
FROM dbo.EVE e
RIGHT JOIN dbo.clm c ON e.EVE_id1 = c.clm_id1 RIGHT JOIN dbo.EVEP p ON e.eve_id1 = p.EVEP_id1 JOIN dbo.notes2 n ON n.eve_id1 = e.EVE_id1 --JOIN evep p2 --ON p.evep_id1 = p2.evep_id1
Where ((e.eve_resl1 = '' and p.evep_reas = 'HO')or (e.eve_resl1 = '' and p.evep_reas = 'PH') or (e.eve_resl1 = '' and p.evep_reas = 'AN'))
Order by e.eve_resl1,p.EVEP_RESL1
--and ---means Open Events will not have claims attached to them. we just need the fields in this report. --p2.evep_nego = 'Y' and --((LTRIM(p.evep_id2) <> '0002' AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1)
INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile] ([EventNumber] ,[ClaimNumber1] ,[Resolution] ,[Resolution2] ,[ProviderType] ,[Negotiation] ,[NEGOYESORNO] ,[ProviderID] ,[HCSAuthorizationID] ,[PROLASTNAME] ,[proFirstName] ,[ProOffice] ,[TOTALBILLEDAMT] ,[SAVINGS] ,[OONNEGO] ,[CLM_ATT1] ,[CLM_ATT2] ,[CLM_ATT3] ,[CLM_ATT4] ,[CLM_ATT5] ,[NoteData]) SELECT Distinct c.CLM_elrc AS 'EventNumber', CASE c.clm_ips WHEN 'C' THEN c.clm_pclm ELSE c.clm_id1 END as "claimnumber1", --ClmClaims."CLAIMNUMBER", --c.CLM_PCLM as "CLAIMNUMBER", --n.note_sys, --c.clm_id1, --e.EVE_clm As 'EventEventTable', e.eve_resl1 as 'Resolution', p.EVEP_RESL1 as 'Resolution2', p.evep_reas as 'ProviderType', p.evep_nego as 'Negotiation', --e.EVE_STAT AS 'STATUS', e.EVE_SW01 AS 'NEGOYESORNO', c.CLM_5 AS 'ProviderID', e.EVE_EXNO AS 'HCSAuthorizationID', --p.EVEP_LNAME AS 'PROVIDERLASTNAME', --p.EVEP_FNAME AS 'PROVIDERFIRSTNAME', --p.EVEP_OFFIC AS 'PROVIDEROFFICE', p.evep_lname AS ProLastName, p.evep_fname AS proFirstName, p.evep_offic AS ProOffice, c.CLM_MCHG AS 'TOTALBILLEDAMT', c.CLM_SPPO AS 'SAVINGS', c.CLM_55d AS 'OONNEGO', c.CLM_ATT1, c.CLM_ATT2, c.CLM_ATT3, c.CLM_ATT4, c.CLM_ATT5, n.NoteData
FROM dbo.EVE e Right Join dbo.clm c ON e.EVE_id1 = c.clm_elrc Right join dbo.EVEP p ON e.eve_id1 = p.EVEP_id1 --JOIN evep p2 --ON p.evep_id1 = p2.evep_id1 JOIN dbo.notes2 n ON n.eve_id1 = e.EVE_id1
Where p.EVEP_RESL1 = 'NG'and clm_adjto = '' ---"NG"This report is going to be changed to the negoitated report the field is going to be "NG" --e.eve_resl1 = 'NG'and --p2.evep_nego = 'Y' and --((LTRIM(p.evep_id2) <> '0002' AND p.evep_id2 <> '0001'AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1)
I'm using a OLE DB COMMAND component to perform an update (SQL statement) but the procces takes about 9 hours, so I changed it to a stored procedure but it was the same, I need to update about a a million of rows and the package is very simple.
How can I improve the time, Can I use another component or startegy?
I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.Â
Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.Â
I have a table that I need to do some computations on all the data but first I need to remove the duplicate records and insert the results into a destination table. Here's the example below. My table has 3.1 million rows. I have tried using the DISTINCT and the GROUP BY but both ways to select the data takes about half a minute to run. I'm wondering if there is a way to increase performance. Users are ok with this time since the process runs overnight but improving it won't hurt. I do have a clustered index on these fields but that doesn't seem to improve any.
Due to localization I have the need to make child tables, where there is a composite Primary Key, between the Id column and the LanguageSign column. On the parent table the Id column is Identity column with auto increment.
The problem is that during the select into query to copy columns from parent to child, this auto increment behaviour of the parent-Id is copied to the child-Id. However I do not want that, because the same Id will be used by different LanguageSign entries
Is there a way to use 'select into' without copying the auto increment, or is my only option to make a whole new column without auto increment on the child and copy the records? Â btw I have used this statement
SET IDENTITY_INSERT MyTable
ON , so that inserting into the Id column is possible. I can see however that this does not take away the auto increment...
Disk space is increasing at an alarming rate (about 500MB a day). Shrink of database seems to have no effect. It is a queueing table so data is inserted then a few minutes later it gets processed & deleted.
we have a problem with "one-to-many relations between fact table and dimension table". Take the example of table "LOGGEDFLAW" which is related one-to-many to the table "LOGGEDREASON. "LOGGEDFLAW" includes the column "FLAWKEY" and "LOGGEDREASON" includes the column "REASONKEY" and essentiallay the column "FLAWKEY" as foreign key. Now assume that we have the following records in there:
Now assume, that "LOGGEDFLAW" is the facttable and "FLAWCOUNT" is the measure with the source column "FLAWKEY" in which we want to count the number of FLAWs. As you see in the example the number of FLAWs is 1 for "FLAW1" and "FLAW2". Microsoft Analysis Server generates the value of 2 for the number of FLAWs "FLAW1" because of the one-to-many relationship to the table "LOGGEDREASON". In the attached ZIP File you find :
- a MDB File with the described example - a screenshot from the cube constructed in AS - a screenshot from the result table generated with AS.
The question: How is it possible to calculate the measure "FLAWCOUNT" correctly, ignoring the records generated by the one-to-many relationship?
I have a large flat file that comes to me. I first import the flat data in to a SQL table for ease of use. Then i put it into a more permanent table with the proper references to dimension tables. I want to build a dimension table out of information from my flat file. I have a dimension table with columns, [Org Client], and [Client#] where [org client] is the name of the client. Both of these columns appear in my flat file but i want to use only the client# in my permanent table. How extract distinct values of client # and [org client] into a dimension table?
My idea was to select distinct values of client# and use some type of foreach loop to go through each client# and use a query to select the TOP(1) values of [org client] where client# = x. Would this work and if so how do I go about setting this up?
I'm really hoping there is a simpler way than this. Thank you all for your time.
Hi I am working as datawarehous architect with a large concern and i designed a datamart witha fact table surrounded by 5 dimension tables. My PM who do not know datwarehousing has abruptly changed my design and instead of 5 dimension tables has increased to 17 tables which just 1 column each in them. This is hell of a design because if it is a single column dimension then there can not be any hierarchy in the dimension table , better will be to push this column as a fact. What should I do now. Moreover he has asked me to not to use SSIS and code everything in stored procedure. Please guide. Jigjan
While populating dimsnion table the requirement is that the 1st record in all dimension should be " Not Available"
and if the fact table has a null value for that dimension it shouldbe tied to " Not Available" record in dimension Please help hwo to do both the things. JJ
Now I create datawarehouse for my client, I have SSIS a lot for ETL process, I a problem that some fact table need to be updatetable and there is a lot of data of this, I need some efficent way to load this data to data warehouse. I have read your article about SCD in SSIS (Slowly Changing Dimensions in SQL Server 2005). I think the purpose of SCD for Dimension table. If I have some fact table that need rows to be updatetable can you give me an example, best practice, the efficient way or fastet way to load fact table that can be updatetable? If you have link or link about this problem please reply my email. Thanks My datasource from ORACLE and my datawarehouse in mssql2005
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?
I have date and float in attribute in a dimension table. If deploy my cube and I try to create a report with RS, I can't format this data. The value seems to be a String so I have to do a CDate or a CDbl before formating it. I have no problem when I try to format my measures.
I have a situation with a table that was created for a transactional
system with a 3 columns key. The table is similar to the following:
countrystatecitydescription 11221City A from country 1 and state 12 11321City A from country 1 and state 13 21422City B from country 2 and state 14 21522City B from country 2 and state 15
Now I'm trying to create a dts package that would allow me to build a
city dimension table with unique codes (keys) for each city. What kind of
transformation should I use to translate the old codes (based on the
country-state-city key) into the new ones and preserving the data
