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
We are using MS Excel 2007 Pivot tables to access en SSAS 2005 Cube. Farly often when we reopen an excel spreadsheet with one or more pivot tables we get an error like this:
Excel found unreadable Content. Do you wish to repair
When we click yes the following log is shown:
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition2.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable2.xml part (PivotTable view)
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)
And all the pivot tables are converted to plain text.
I have read about this in KB 929766 but this do not apply since KPIs are not used.
The KB 943088 is more interesting but after upgrading all user with SP 1 the problem is still there, mostly when we open old excel-files that has been created without SP 1 but opened and saved once with SP 1. After that we are not able to open them at all, either with or without SP 1 installed.
Is there some way to €œsave€? pivot tables from destruction? I can€™t ask the users to rebuild all there spreadsheets that have been created prior SP1. What will happen when there is a new SP for Excel? Rebuild all spreadsheets and pivot tables again?
I have a date dimension with below Hieararchy:
Year-->Quarter-->Month-->Week
Week is the lowest granular attribute.
Also i have a measure 'Holdings'
I would like to create calculated measure which should give me closing holdings at all levels(week ,Month,Quarter and year levels.) whichever i pull in the browse pane.
Below calculated measure would show the holdings for whatever the level you have specified.
([Measures].[Holdings],
ClosingPeriod( [DIM BI DATE].[Calendar].[WEEK], [DIM BI DATE].[Calendar].CurrentMember
))
But this measure shows the value for only week attribute.
for month,([Measures].[Holdings],ClosingPeriod( [DIM BI DATE].[Calendar].[Month],
[DIM BI DATE].[Calendar].CurrentMember ))
But my aim is to create a calculated measure to give closing value for any level.
I have a monthly time period dimension representing average number of students for each month. At the yearly aggregate level I don't want it to sum up the avg number of students from every month because that number is incorrect. I would like it to use the number of students from the most recent month as a roll up. Is that possible to configure in SSAS?
View 2 Replies View RelatedI have a query which returns the movements to and from our warehouse stock, as well as the current stock for each depot and how much is on order. What I need is a kind of pivot so that each item is shown just once, and then summarises the movements in 4 extra columns: Last 30 days, 30-60 days, 60-90 days and 90-120 days. How can I achieve this with my query below? A sample of some of the results is also shown.
select
iv.item,
iv.descr,
ts.loc_total_on_hand [Stock],
ts.loc_code [Depot],
po.qty [On Order],
po.office [Order Depot],
[Code] .....
I have a [Fin Period] column which outputs a number between 1 and 12 depending on the financial period. For numbers 0-9 this is output as a single digit. How can I format this so it places a 0 in front of these numbers but not in front of periods 10-12?
View 2 Replies View RelatedImagine a 5 column dataset with the following fields - Organiser, Date of Invoice, Total Invoice Value, Reimbursements and Service tax charged. Using the PowerPivot, I want to determine the "Growth in Professional fee billed over the previous period" - please note that previous period need not be previous year because a client may be billed after a gap of 1-2 years as well.To compute growth, I first need to determine the absolute value of Professional fee billed over the previous period. This is where I am getting stuck. Since the billing periods for different clients need not be consecutive, I cannot use the SAMEPERIODLASTYEAR function.
In trying to solve the problem, I tried to frame a calculated field formula but could not do so. Therefore, I tried solving it via a calculated column formula in the PowerPivot window. My idea here was to determine the client wise previous financial year for each row and then use this column in a calculated field formula to get my desired result. I am getting an error when I write this formula (see Billing data tab of PowerPivot window).
I would like to provide users a way to dynamically select the dimension by which they analyse data in a pivot table. In a simple example, my fact table has 3 columns: product type, colour, volume. The pivot table consolidates the volume, and a slicer allows the user to display the sum of volume either by product type or by colour.
The solution I have come to so far involves creating a table with a single column that contains all the single values of product type and colour. There are 2 relationships between the fact table and this lookup table: one active on the product type column, one inactive on the colour column.
A 3rd disconnected table provides a way to select which of the 2 relationships is the active one for the consolidation metric. The example is available here.
I have two data tables:
1) Production data with column headers: Key, Facility, Line, Time, Output
2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key
Facility
Line
Time
Output
Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
i have a model contains fact of account revenue , a time dimension and scd of account.
the scd (type 2) is changing when an account get a new color.
when i queries the model by PowerPivot i sometimes get wrong color for an account in a date priod , for example , I would expect to see color 3 for account 1 at 04-06-2010 , But instead I see  color 1 - as you can see in the picture bellow.
FACT , SCD :
Result
i process all , deploy , mark dim_time as date dim , and i still cant find the error.
I need to create a date dimension where the lowest level is month. I've seen examples which use the list function such as
Source = List.Dates(#date(2000, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2000,1,1)), #duration(1,0,0,0)),
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.
Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)
View 2 Replies View RelatedWe 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.
DateKey MonthNumber HolidayHoursPerMonth
6/29/2015 6 0
6/30/2015 6 0
7/1/2015 7 8
7/2/2015 7 8
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).
MonthNumberOfYear HolidayHoursPerMonth
1 8
2 4
3 0
4 0
5 8
6 0
7 8
8 0
9 8
10 4
11 16
12 8
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 Related
Hi,
I have a problem with a reporting services 2005 report.
The data source is OLEDB for OLAP 9.
The report contains all the members of a parent child dimension. An example of the implementation is defined in the post following the forum msdn:
Http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=138549&SiteID=1.
The report works fine in web. The problem is when the report is exported as Excel, the groups disappeared and the entire dimension is ragged down.
Normally toggled groups in reporting services are exported to Excel with the appearance '+' or '-' on the left side of the sheet. This feature is very useful to hide / show lines.
It seems that with the implementation specified in the msdn post above,
It is not possible to export correctly the report in Excel.
So my questions are:
Has someone already encountered this problem?
Are there solutions with a different implementation?
Thanks,
Gurvan
Hi,
I have several pivot tables in Excel that access data to a SQL 2000. We install SQL 2005, we change the ODBC from the 2000 server to the 2005 server. Now when we try to run the pivot tables I've got the following message:
"User 'public' does not have permission to run DBCC TRACEON"
Any idea on how to fix this problem?
Thanks,
Arty
Hi,
Im using Sql server 2000&MS Excel 2003. i've recently moved to Sql server,i was using MS Access.
i've created an odbc connectivity and a View . i've created pivot tables in excel. my intension was once i upload the data into sql server and when i refresh the view/excel pivot, the pivot table should updated automatically with the newly entered data( i used to do the same process in Access).
pls advise me how to update the Excel pivot table.
Rgds
RPU
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
Hi,
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
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?
I am facing problem like;We generate reports thru Excel Pivot table. The data is coming from salescube. Let?s assume that there are 2 products A and B. The user wants tofilter for each product and view ?stock in hand? and ?total sales? forparticular month (as two columns).From the Pivot table filtering, when we choose product A and month ?Jan?,it is showing values for ?stock in hand? and ?total sales? columns for thatmonth.But when we choose product B and month ?Jan?, it is only showing ?stock inhand? value and the ?total sales? column was suppressed/unseen. We knowthat there is no sales transaction for ?Jan? month. But we still want toshow the column title even if there are no records in the database / cube.I am not using any scripting, this pivot table was generated just thruPivot table wizard.Any way to achieve this, please advise me.Advanced thanks for u r help.--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedHello,
I'm wondering if anyone could shed some light on the following error when selecting multiple items in a pivot table.
€œThe database driver does not include necessary capabilities and cannot be used with Excel. Contact your database administrator or driver vendor.€?
Your help is appreciated!
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
The excel pivot table is very powerful for the user, with dragging and dropping fields at will on the web page, but I can only get it work when run from the web server directly. The data is in SS2000 300,000 rows, get external data in Excel, then publish pivot table as HTM with interactivity.
It seems to look for c:myodbc.dsn in the C: drive of the pc the user is on rather than the server, and I cannot use IP Addressing to relate to the dsn file.
any ideas ?
TIA
Neil.
I know that this is an Excel question, but I guess it is much more likely that an SQL person using dynamic pivot tables had stepped on this, rather than any advanced Excel user.
I am exporting a dynamic pivot table to Excel through a Stored Procedure. If the Stored Procedure that executes the dynamic pivot table returns 7 columns in one run, and 4 columns in the following update, then I have 3 orphaned columns that are still displayed in the spreadsheet. There isn't any content related to them, but the empty columns with their headers are bothering enough.
I've been trying to play with the data connection properties, but nothing deletes unused columns from former data executions.
Hi,
We have a excel 2007 file with a Pivot Table in it. We would like to use the same as data source (But all the source fields as it comes for Pivot). The key issue is that the data to the pivot is from a connection and points to DB - but there is no access to DB. So the Excel is static to us with a PIVOT in it.
Is there a way to get all the data that is avaialble as a source for pivot.
Ex. The select query has co1,co2....co 11 from xxxxx
whereas the PIVot HAS ONLY cO1, CO2 AS ROW, CO3,CO4 AS COL, CO5 AS VALUE AND CO6,CO7 AS FILTER AND OTHERS MISSED OUT IN PIVOT.
THe SSIS as such provide Excel 2007 access to Access OLE DB driver with Excel 12 setting in advanced tab. But not sure whether we can give the range (or) a way around ot get all the data as available in excel regardless of what is used only in the pivot.
Regards,
kart
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 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
Wonder if you could help me ?
I have set up a cube running under MSSQL Server 2000 Analysis services and have created reports in Excel 2002 using pivot tables and linking into the cube as an external data source.
The pivottable works fine and I can slice / dice as usual, the only thing that doesnt work is the drill through into AS. I receive and error message saying "Cannot show detail data for that selection...".
Now I know that the drill through works, as when I create a report using the Analsyis Services Excel Add Inthe drill through works fine, its just how do I get it working using the pivot tables ?
fyi - the reason I dont want to use the Analsyis Services Excel Add In for all the reports is because I have to deploy this to XX number of users, who wont have admin rights on their machines etc etc....
Is there any VB that I could use to perform this drill through and return the results.....or easier ??
Thanks very much
Mark
We have build a powerpivotmodel that uses a couple of datasources. The user extracts on monthly base an excel file with 20 samples (randomly) from the model to check some aspects of the input. He can put Yes or No into a column if he agrees or not and fill in a remark field. Next this excel file is loaded back into the powerpivot model (in a nightly batch) and the Yes/No field and the remark field are added to the original data. If the user refreshes his excel sample file from the pivot, the remark and YN field will be filled with the values he added. So far so good. One moth later when he extracts the file for a new month a problem arises. Since the excel file only has data for the new month, when the PP-model is reloaded the data for the previous month will be lost. So we need some sort of incremental load for the excel file. Is that possible and is this the best way to handle this situation?
View 4 Replies View RelatedIn an Excel workbook I'm building a report using a PowerPivot data model.
I've a Calendar filter. If I select an year from this filter I need to show in a cell the total number of working days, present in the Calendar table as a column.